VBA help please

  tasslehoff burrfoot 16:18 14 Feb 05
Locked

Hi all long time no post!

I am, trying to set up an excel doc to perform a search on a shared folder on a network, the code I have is:

Private Sub Workbook_Open()
Set fs = Application.FileSearch
file = InputBox("Name", Search)
With fs
.LookIn = "\\******\*****\*****\*****"
.Filename = "*" & file & "*"
.SearchSubFolders = True
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
If MsgBox(.FoundFiles(i), vbOKCancel) = vbOK Then
vba to open found file goes here>
End If
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

I need the found file to open when yes is clicked on the message box, any idea how I would do this?

TIA

Tas

  rogertjj 16:49 14 Feb 05

Workbooks.Open .FoundFiles(i)


That should do it. You may also want to add


.Filename = ".xls"


so that the seearch only looks for Excel files. Otherwise, it will return errors if it finds and tries to open non Excel files.

  tasslehoff burrfoot 08:29 15 Feb 05

That's great.

Except some of the foundfiles will be word docs, is there anyway to do this?

Many thanks
Tas

  rogertjj 10:59 15 Feb 05

Yes, you can open word documents. Firstly, you would need to determine if the FoundFile(i) is a word doc. To do this, type the following :


If Right(.FoundFiles(i), 3) = "doc" Then


Code to open the word doc goes here


end if


There are a couple of ways that you can then open the word document.


1 - You need to add the Microsoft Word Object Library to your Excel VBA project.


To do this, press ALT + F11 to access the VBA editor. Then, select References from the Tools menu, and tick the Microsoft Word 10.0 Object Library. (number may be different depending on version of Word)


Then, to open the word doc, type :


Dim myApp As New Word.Application


myApp.Documents.Open .FoundFiles(i)


myApp.Visible = True


myApp.Activate


That will open the word document, and activate Microsoft Word.


2 - You could type :


myApp = "C:\Program Files\Microsoft Office\Office10\Winword.exe"


myFile = .foundfiles(i)


myShell = shell(myapp & " " & myfile)


Both of those should work, and you could adapt the second one to open any other type of file, as long as you know the application path.


Hope this helps


Roj

  rogertjj 15:12 02 Mar 05

Did you have any luck with this ?

  tasslehoff burrfoot 20:39 06 Mar 05

Sorry for the delay in posting. It works with excel files but not with word docs. Everythings fine until I tell it to open the doc and then it says it could not find the file (even though it has just searched and found it).

Could it be that the path & filename are too long? It looks like it could be this as when it says it can't find it, it displays only part of the path/filename

Cheers

Tas

  VoG II 20:46 06 Mar 05

Is the path+filename > 255 characters?

If it is, you should be able to get around this by explicitly declaring a string variable:

Dim MyFullPath as String

  tasslehoff burrfoot 21:04 06 Mar 05

I'll check tomorrow (it's a work thing). If so, will I just put

with fs

.lookin = MyFullPath

and that's it? (obviously after declaring the variable)

Many thanks

Tas

  VoG II 21:05 06 Mar 05

Yes. Good luck!

  tasslehoff burrfoot 21:09 06 Mar 05

I'll let you know how it goes tomorrow!

  tasslehoff burrfoot 19:26 10 Mar 05

The path + file name is 136 characters. I tried your suggestion anyway, VoG but still no luck.

I'll click resolves and tell my boss this is beyond me.

Cheers for the help.

Tas

This thread is now locked and can not be replied to.

New Google phones UK release date | Pixel XL price, new features, specifications: Pixel X and…

1995-2015: How technology has changed the world in 20 years

This is the future of TV, VR and holograms

iOS 10 troubleshooting tips: Simple fixes for the most common iOS 10 problems, from network…