Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File Using A Wildcard
I am trying to write a macro that will be run weekly, and will open, one by
one, a series of files, extract a certain range of data, paste to a master file, and close each source file. I cannot convince the requestor to leave the date out of the file name, so the names of the files changes each week. Here is a piece of the code: Workbooks.Open Filename:= _ "C:\EnterpriseServices\ES Dashboard Laurie B 08-23-2007.xls" Rows("4:40").Select Selection.Copy I tried placing an asterisk right after the initial of the person's last name, (the "B" in this example), but of course this did not work. Is what I am trying to do possible? If so, how? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File Using A Wildcard
How about something like thie:
Sub GetFiles() With Application.FileSearch .NewSearch .FileType = msoFileTypeExcelWorkbooks .Execute For i = 1 To .FoundFiles.Count If InStr(.FoundFiles(i), "C:\EnterpriseServices\ES Dashboard Laurie B") 0 Then Workbooks.Open .FoundFiles(i) 'do your thing here Workbooks(Dir(.FoundFiles(i))).Close True End If Next End With End Sub Bob Umlas Excel MVP "jmdaniel" wrote in message ... I am trying to write a macro that will be run weekly, and will open, one by one, a series of files, extract a certain range of data, paste to a master file, and close each source file. I cannot convince the requestor to leave the date out of the file name, so the names of the files changes each week. Here is a piece of the code: Workbooks.Open Filename:= _ "C:\EnterpriseServices\ES Dashboard Laurie B 08-23-2007.xls" Rows("4:40").Select Selection.Copy I tried placing an asterisk right after the initial of the person's last name, (the "B" in this example), but of course this did not work. Is what I am trying to do possible? If so, how? Thanks in advance! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Open File Using A Wildcard
Assuming if there is a file with a name of this type, it is the correct file
then: Dim sPath as String, sn1 as String, sn as String spath = "C:\EnterpriseServices\" sn1 = "ES Dashboard Laurie B " sn = dir(spath & sn1 & "*.xls") if sn = "" then exit sub Workbooks.Open Filename:= _ spath & sn Rows("4:40").Select -- Regards, Tom Ogilvy "jmdaniel" wrote: I am trying to write a macro that will be run weekly, and will open, one by one, a series of files, extract a certain range of data, paste to a master file, and close each source file. I cannot convince the requestor to leave the date out of the file name, so the names of the files changes each week. Here is a piece of the code: Workbooks.Open Filename:= _ "C:\EnterpriseServices\ES Dashboard Laurie B 08-23-2007.xls" Rows("4:40").Select Selection.Copy I tried placing an asterisk right after the initial of the person's last name, (the "B" in this example), but of course this did not work. Is what I am trying to do possible? If so, how? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening a file with wildcard | Excel Discussion (Misc queries) | |||
In Excel - Use Windows Explorer instead of File Open to open file | Excel Discussion (Misc queries) | |||
Using Wildcard on Workbooks.Open | Excel Programming | |||
open workbook using wildcard | Excel Programming | |||
Using wildcard for checking whether files are open | Excel Programming |