Opening an Excel file with a changing name

[Guest]

Hi there.

I want to open a file that has a chaging name. Another words, the file is
written over on a random basis and the beginning part of the file name is a
date ref that changes.

I want to use an * within the name, but can't get it to work. You can see
what I've tried below.

Can anyone help?

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

TestFile = "C:\VCCT Training DB\SAP Roles\* Role_Catalog_Master.xls"

Set objActiveWkb = objXL.Workbooks.Open(TestFile)

Set objActiveWkb = objXL.Application.ActiveWorkbook

objXL.Visible = True


Thanks in advance,
Clint

 

[Tim Ferguson]

I want to use an * within the name, but can't get it to work. You can
see what I've tried below.

TestFile = "C:\VCCT Training DB\SAP Roles\*" & _
"Role_Catalog_Master.xls"

Set objActiveWkb = objXL.Workbooks.Open(TestFile)

You can't open a file with a star in the name: you need to pass .Open one
single file with an existing file name. You will have to (a) calculate what
the actual file name is or (b) offer the user some kind of choice.

The use of Dir() function may be of some help.

HTH

Tim F

 

[Guest]

You can use the code at this site:
http://www.mvps.org/access/api/api0001.htm
to present the common windows dialog to return a file name. Then you can
open the file using the value returned from the Open Dialog box. Here is an
example:

'Set up the Default path and file
strDefaultDir = "\\xxxxxxxxxxx\xxxxxxx\xxxxxx " & strCurrYear & "\" &
strCurrYear _
& " Actuals\" & strCurrMonth & "\"
strFileName = "Xxxxxxxxxxxxxx " & strCurrMonth & " " & strCurrYear &
".xls"
'Flags Hides the Read Only Check and Only allow existing files
lngFlags = ahtOFN_HIDEREADONLY Or ahtOFN_FILEMUSTEXIST
'Set filter to show only Excel spreadsheets
strfilter = ahtAddFilterItem(strfilter, "Excel Files (*.xls)")
'Call the Open File Dialog
varGetFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=strDefaultDir, _
Filter:=strfilter, _
FileName:=strFileName, _
Flags:=lngFlags, _
DialogTitle:="Import Adjusted Actuals")
Me.Repaint
If varGetFileName = "" Then 'User Clicked CANCEL
GoTo LoadAdjustedActuals_Exit
End If
'Open Excel
On Error Resume Next ' Defer error trapping.
Me.txtStatus = "Opening Spreadsheet"
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
blnExcelWasNotRunning = True
Set xlApp = CreateObject("excel.application")
Else
DetectExcel
End If
Err.Clear ' Clear Err object in case error occurred.
On Error GoTo LoadAdjustedActuals_Err
DoEvents
xlApp.DisplayAlerts = False
xlApp.Interactive = False
xlApp.ScreenUpdating = False
'Open the Workbook
Set xlBook = xlApp.Workbooks.Open(varGetFileName, 0, True)