VBA help please

  tasslehoff burrfoot 16:18 14 Feb 2005

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
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?



  rogertjj 16:49 14 Feb 2005

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 2005

That's great.

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

Many thanks

  rogertjj 10:59 15 Feb 2005

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


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


  rogertjj 15:12 02 Mar 2005

Did you have any luck with this ?

  tasslehoff burrfoot 20:39 06 Mar 2005

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



  VoG II 20:46 06 Mar 2005

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 2005

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


  VoG II 21:05 06 Mar 2005

Yes. Good luck!

  tasslehoff burrfoot 21:09 06 Mar 2005

I'll let you know how it goes tomorrow!

  tasslehoff burrfoot 19:26 10 Mar 2005

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.


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

Elsewhere on IDG sites

Samsung Galaxy S9 review

Artist Helen Friel on designing paper models for everyone to make

What to expect at Apple's 27 March education event

Idées cadeaux pour geeks et tech addicts