Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select new wkbk when name varies
I hope I can describe my situation well enough that I can get a
suggestion from the experts on the group. Here goes..... During the execution of a macro a new workbook will be opened, this new workbook will be created by a web based application that has the option to download data to an excel workbook. I would like to have the macro access this new workbook to extract some data. The problem is, the name of the workbook that is generated by the web based application varies, here are some examples of the names: GEALLgz0ebr55kdkez3ukfgcf5e452641732.xls GEALLgz0ebr55kdkez3ukfgcf5e452915659.xls GEALLgz0ebr55kdkez3ukfgcf5e4521132788.xls It appears the start of the name is always the same but the end varies, maybe some time stamp. How do I select this workbook based on for instance, the first 10 characters of the name? Does this make sense? John Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select new wkbk when name varies
I think you would want the latest file with the a specified first 10
characters. Sub OpenLatest() Folder = "C:\Temp\" BaseName = "GEALLgz0eb" Set FSO = CreateObject _ ("Scripting.FileSystemObject") LatestDate = 0 FName = Dir(Folder & BaseName & "*.xls") LatestFName = "" Do While FName < "" Set FSN = FSO.getfile(Folder & FName) If FSN.DateLastModified LatestDate Then LatestFName = FSN.Path End If a = 1 FName = Dir() Loop If LatestDate = 0 Then MsgBox ("Cannot find file - Exiting sub") Exit Sub End If Set bk = Workbooks.Open(Filename:=LatestFName) End Sub "John Keith" wrote: I hope I can describe my situation well enough that I can get a suggestion from the experts on the group. Here goes..... During the execution of a macro a new workbook will be opened, this new workbook will be created by a web based application that has the option to download data to an excel workbook. I would like to have the macro access this new workbook to extract some data. The problem is, the name of the workbook that is generated by the web based application varies, here are some examples of the names: GEALLgz0ebr55kdkez3ukfgcf5e452641732.xls GEALLgz0ebr55kdkez3ukfgcf5e452915659.xls GEALLgz0ebr55kdkez3ukfgcf5e4521132788.xls It appears the start of the name is always the same but the end varies, maybe some time stamp. How do I select this workbook based on for instance, the first 10 characters of the name? Does this make sense? John Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select new wkbk when name varies
dim wb as workbook
for each wb in Workbooks if left(wb.name,10) = "GEALLgz0ebr" then exit for end if next if not wb is nothing then msgbox wb.name,,"workbook selected" end if orif wb.name LIKE "GEALLgz0ebr*" then "John Keith" wrote: I hope I can describe my situation well enough that I can get a suggestion from the experts on the group. Here goes..... During the execution of a macro a new workbook will be opened, this new workbook will be created by a web based application that has the option to download data to an excel workbook. I would like to have the macro access this new workbook to extract some data. The problem is, the name of the workbook that is generated by the web based application varies, here are some examples of the names: GEALLgz0ebr55kdkez3ukfgcf5e452641732.xls GEALLgz0ebr55kdkez3ukfgcf5e452915659.xls GEALLgz0ebr55kdkez3ukfgcf5e4521132788.xls It appears the start of the name is always the same but the end varies, maybe some time stamp. How do I select this workbook based on for instance, the first 10 characters of the name? Does this make sense? John Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select new wkbk when name varies
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select new wkbk when name varies
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheets from source wkbk to another wkbk | Excel Programming | |||
Find WkBk Path, Then use this path to open another WkBk in Subfold | Excel Programming | |||
Sum of a range that varies | Excel Discussion (Misc queries) | |||
Creating a Hyperlink from 1 WKBK to open another WKBK and activate | Excel Programming | |||
run a procedure in 1 wkbk from another wkbk | Excel Programming |