macro to search for a file name, open it and copy contents.

[instanceoftime]

I would like to run a macro that would allow me to enter 'text' to search for in the title of the file I would like to open. (this file would be in the following location) : C:\FileServer\Admin1\Glen\101-061-909\invoices

i would like to be able to enter "1210912" and have it open file: "cstSUM-1210912" and copy its contents onto this blank worksheet containing the marco.

I would rather not have to have a window open and have to find the file, click and open.

can someone help me with this please.


Thanks

Tim

 

[JLGWhiz]

If "cstSUM-1210912.xlsx" (note the file extension) is the only file with 1210912 in it, then:

Sub t()
Dim fName As String, fPath As String, srch As String
fPath = "C:\FileServer\Admin1\Glen\101=0610909\invoices\"
srch = InputBox("Enter search string for filename", "SEARCH")
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If InStr(fName, srch) > 0 Then
            Workbooks.Open fPath & fName
            Exit Do
        End If
        fName = Dir
    Loop
End Sub

But if you have more than one with that string in it, then there is no guarantee that it will open cstSUM-1210912.

 

[instanceoftime]

If "cstSUM-1210912.xlsx" (note the file extension) is the only file with 1210912 in it, then:

Sub t()
Dim fName As String, fPath As String, srch As String
fPath = "C:\FileServer\Admin1\Glen\101=0610909\invoices\"
srch = InputBox("Enter search string for filename", "SEARCH")
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If InStr(fName, srch) > 0 Then
            Workbooks.Open fPath & fName
            Exit Do
        End If
        fName = Dir
    Loop
End Sub

But if you have more than one with that string in it, then there is no guarantee that it will open cstSUM-1210912.

It searched and opened perfectly. I also needed to copy the contents of the found sheet to the sheet containing this macro. I would also like to close the found book as well.

 

[instanceoftime]

I added the following to close.

Workbooks(fName).Close SaveChanges:=False

and I recorded a macro to select all and paste the sheet contents (I inserted the recorded macro code it into code but looks a little sloppy)

Cells.Select    Range("K11").Activate
    Selection.Copy
    Windows("Stickerprinter.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("H1").Select

anything prettier?

 

[JLGWhiz]

this might be a little neater

Sub t2()
Dim fName As String, fPath As String, srch As String, sh As Worksheet
Set sh = ActiveSheet
fPath = "C:\FileServer\Admin1\Glen\101=0610909\invoices\"
srch = InputBox("Enter search string for filename", "SEARCH")
fName = Dir(fPath & "*.xl*")
    Do While fName <> ""
        If InStr(fName, srch) > 0 Then
            Workbooks.Open fPath & fName
            ActiveSheet.UsedRange.Copy sh.Range("A1")
            ActiveWorkbook.Close False
            Exit Do
        End If
        fName = Dir
    Loop
End Sub

By pasting to cell A1, it will overwrite the previously copied data on each new use of the macro.

 

[instanceoftime]

Thank you so much, that worked awesome

 

[JLGWhiz]

Thank you so much, that worked awesome

You're welcome,
regards, JLG