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

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

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

select new wkbk when name varies
 

On Mon, 12 Oct 2009 08:20:02 -0700, Patrick Molloy
wrote:

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

Thanks Patrick, I'll play later tonight.


John Keith

select new wkbk when name varies
 

On Mon, 12 Oct 2009 08:18:01 -0700, Joel
wrote:

I think you would want the latest file with the a specified first 10
characters.


Thanks Joel,

I'll play with this tonight.


John Keith