Finding a worksheet in a specific location

stevehorton09 · Jul 27th 2004

Hi Guy/gals,

Is it possible to find a worksheet in a specific location?

I.e I have a location on the c drive where all my files are kept and are saved as numbers i.e 1111, 2222 etc etc

Is it possible for a macro/ vb statement to go and find that file and then open it (making it the active worksheet).

I expect this is very simple but i have never had to use the find function (*) before.

Dave Hawley · Jul 27th 2004

Worksheet or Workbook?

The code below will search a folder for Workbook.

Code

Sub RunCodeOnAllXLSFiles()
Dim i As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False


On Error Resume Next


Set wbCodeBook = ThisWorkbook


    With Application.FileSearch
        .NewSearch
         'Change path to suit
        .LookIn = "C:\MyDocuments\TestResults"
        .FileType = msoFileTypeExcelWorkbooks
        .Filename = "Book*.xls"
        
            If .Execute > 0 Then 'Workbooks in folder
                For i = 1 To .FoundFiles.Count 'Loop through all
                 'Open Workbook x and Set a Workbook variable to it
                 Set wbResults = Workbooks.Open(.FoundFiles(i))
                 
                 'Do Your Code Here
             
                 Next i
            End If
    End With
    
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
End Sub

Display More

**royUK** · Jul 27th 2004

This macro opens a Workbook specified in the activecell, you should be able to adapt this

stevehorton09 · Jul 27th 2004

Hi Roy,

Is it possible to use the code to look in a specific cell for part of the saved worksheet name when i have defined what directory to look in?

Im not sure if im understanding the code properly but this is what im trying to get to.

Example:- file im looking for in directory C:\sch is called 5555.

On the find spreadsheet i want it to look in cell a1 (for example), in which i have typed that file name 5555 and then by a click of a button it opens that spreadsheet?

**royUK** · Jul 27th 2004

Try opening a test file using the Macro Recorder to find the exact wording that you need. Then adapt the code. You should end up with something like:

Code

Option Explicit
Sub Comment_text_2_Cell()
' Written by royUK
'   [url]www.excel-it.com[/url]




Workbooks.Open Filename:= _
        "C:\Documents and Settings\My Documents\test.xls"

change this to:

Code

Option Explicit
Sub Comment_text_2_Cell()
' Written by royUK
'   [url]www.excel-it.com[/url]




Dim nameC As Range
 Set nameC = Range("A1").Value


'
    Workbooks.Open Filename:="C:" & "\Documents and Settings\My Documents" & "\" & nameC & ".xls"

Display More

Type test(or the name that you tried previously in A1 and it should open .

stevehorton09 · Jul 27th 2004

OK i have got this working but i have to enter the full path, is it not possible to put part of the file name in?

i.e file location is c:\sch\test1_tue.xls

If i was to make this look in this folder c:\sch\

is it possible if i just typed in test1 that it would return the excel sheet test1_tue??

**royUK** · Jul 27th 2004

Quote from stevehorton09

OK i have got this working but i have to enter the full path, is it not possible to put part of the file name in?

i.e file location is c:\sch\test1_tue.xls

If i was to make this look in this folder c:\sch\

is it possible if i just typed in test1 that it would return the excel sheet test1_tue??

Display More

Dim nameC As Range
Set nameC = Range("A1").Value

'
Workbooks.Open Filename:="C:" & "\"& "sch" & "\" & nameC & ".xls"

I think that should do it if you type in test_tue

stevehorton09 · Jul 27th 2004

yep done it!

dont spend anymore of your time on this but if there is a way so you would have to only type in a small part of the file name i.e test rather than test_true that would be greater beneficial

**royUK** · Jul 27th 2004

Quote from stevehorton09

yep done it!

dont spend anymore of your time on this but if there is a way so you would have to only type in a small part of the file name i.e test rather than test_true that would be greater beneficial

Try this

Workbooks.Open Filename:="C:" & "\"& "sch" & "\" & nameC" & "_true" & ".xls"

Finding a worksheet in a specific location

Participate now!

Share