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

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
78
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows
If "cstSUM-1210912.xlsx" (note the file extension) is the only file with 1210912 in it, then:
Code:
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.
 
Last edited:

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
78
If "cstSUM-1210912.xlsx" (note the file extension) is the only file with 1210912 in it, then:
Code:
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.
 
Last edited:

instanceoftime

Board Regular
Joined
Mar 23, 2011
Messages
78
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)
Code:
Cells.Select    Range("K11").Activate
    Selection.Copy
    Windows("Stickerprinter.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("H1").Select

anything prettier?
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,446
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

this might be a little neater

Code:
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,227
Messages
5,527,532
Members
409,769
Latest member
Hudz

This Week's Hot Topics

Top