Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a workbook
I have a code that opens a workbook with the name "CashVariance"
concatenated with the effective audit date, formatted as "mmddyy". Therefore, if the audit date is 7/18/06, the file named CashVariance071806.xls will open. An upgrade to Crystal Reports saves the file as CashVariance & "yyyy-mm-dd-hh-mm-ss".xls. The time is variable. Is there a way to modify my code to open the file by looking only at the "yyyy-mm-dd" and ignoring the "-hh-mm-ss"? I don't know how to utilize the left function in a file name or use wildcards in the Workbooks.Open procedure. Any help would be greatly appreciated. Sam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a workbook
One way you could approach it is to use filesearch. I just used today's date
in the filename, change to whatever date you need/want. Also, change .Lookin to the proper folder. If there is more than one match, it will prompt the user to select the proper file. Change to whatever you want it to do in that case. Sub Test() Dim wkbTemp As Workbook Dim FName As String With Application.FileSearch .NewSearch .LookIn = "I:\Excel" .SearchSubFolders = False .Filename = "CashVariance" & _ Format(Date, "yyyy-mm-dd") & "*" .MatchTextExactly = True .FileType = msoFileTypeExcelWorkbooks .Execute Select Case .FoundFiles.Count Case 0: MsgBox "File Not Found" Case 1: Set wkbTemp = Workbooks.Open(.FoundFiles(1)) Case Else FName = CStr(Application.GetOpenFilename) If FName < "" And UCase(FName) < "FALSE" Then _ Set wkbTemp = Workbooks.Open(FName) End Select End With End Sub "Sam Ricca" wrote: I have a code that opens a workbook with the name "CashVariance" concatenated with the effective audit date, formatted as "mmddyy". Therefore, if the audit date is 7/18/06, the file named CashVariance071806.xls will open. An upgrade to Crystal Reports saves the file as CashVariance & "yyyy-mm-dd-hh-mm-ss".xls. The time is variable. Is there a way to modify my code to open the file by looking only at the "yyyy-mm-dd" and ignoring the "-hh-mm-ss"? I don't know how to utilize the left function in a file name or use wildcards in the Workbooks.Open procedure. Any help would be greatly appreciated. Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
when opening an Excel Workbook, another blank workbook also opens | Excel Discussion (Misc queries) | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming |