Code for finding Workbooks

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
Has anyone got any code for using the find files option (under the start menu search)to find excel files by a certain name say workbook.xls ?

_________________<MARQUEE scrollamount=8 behavior=alternate>
galaxyicon.gif
</MARQUEE>

I basically want this so if I send a file to someone it can find where their workbook is on their comp and perfom a few things is there another way to do this?<MARQUEE scrollamount=8 behavior=alternate>
galaxyicon.gif
</MARQUEE>
This message was edited by brettvba on 2002-05-05 19:47
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Oh well i found some code which opens opens a file name found in any sub directory of the drives specified

does anyone have any tidyer code than this??

Function FilesFindMatching(sSearchPath As String, Optional sFileFilter As String = ".xls", Optional bSearchSubFolders As Boolean = False) As Variant
Dim lThisFile As Long, lFound As Long, asFiles() As String

With Application.FileSearch
'Initialise variables
.LookIn = sSearchPath
.Filename = sFileFilter
.SearchSubFolders = bSearchSubFolders
'Perform search
lFound = .Execute
If lFound Then
'Populate result array
ReDim asFiles(1 To lFound)
For lThisFile = 1 To lFound
asFiles(lThisFile) = .FoundFiles(lThisFile)
Next
'Return results
FilesFindMatching = asFiles
Else
'No matching files
FilesFindMatching = Empty
End If
End With
End Function




Sub FindBook()
Dim book
Dim avFiles As Variant, lThisFile As Long
avFiles = FilesFindMatching("c:", "Book1.xls", True)
If IsEmpty(avFiles) = True Then avFiles = FilesFindMatching("p:", "Book1.xls", True)
If IsEmpty(avFiles) = False Then
With ActiveSheet
.Range(.Cells(1), .Cells(UBound(avFiles), 1)).Value = Application.WorksheetFunction.Transpose(avFiles)
End With
book = Range("a1").Value
Workbooks.Open Filename:=(book)
Else
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top