Open Excel File from Access Form using Wildcards

[brunpam]

Hello,

I'm somewhat new to Access and I'm trying to open an Excel file from an Acess form command button. The only problem is the Excel filename includes a date and the date changes on a weekly basis. Is there a code that I can use use that supports wildcards?

Thank you in advance!

[ChipR]

As far as I know, you do have to name one specific file to open. You can get the filename easily with the Dir command if you can determine the location of the file.

[brunpam]

Thanks for the quick response. I'm sorry - but I'm not familiar with the DIR command. I'm new to this. Can you give me an example using DIR in a code.

[Delerna]

click on the blue underlined word Dir in chipr's post.
It is a link to all the info you need

[ADezii]

@brunpam
You never stated whether or not the location of your Excel File was static or dynamic. In light of this, I wrote comprehensive code that will:

1. Open the Standard Office File Dialog Window.
2. This Dialog will only display Excel (*.xls) Files and no other Files.
3. Once you Navigate to, then select a File, it will then Open it in Excel.
4. Do not forget to:
   1. Set a Reference to the Microsoft Office XX.X Object Library.
   2. Change the value of the Constant pointing to the Path to Excel.exe, if necessary.
5. If you decide to use this approach, and need further help, we re here.

'First, set a Reference to the Microsoft Office XX.X Object Library
 
Dim strButtonCaption As String
Dim strDialogTitle As String
Dim varFileSelected As Variant
Dim varRetVal As Variant
Const conEXCEL_PATH = "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE"
 
'Define your own Captions if necessary
strButtonCaption = "&Open"
strDialogTitle = "Select Excel File to Open"
 
With Application.FileDialog(msoFileDialogOpen)
  With .Filters
    .Clear
    .Add "Excel Files", "*.xls"                 'Allow Excel Files only
  End With
  'The Show Method returns True if 1 or more files are selected
    .AllowMultiSelect = False                   'Critical Line
    .ButtonName = strButtonCaption
    .InitialFileName = vbNullString
    .InitialView = msoFileDialogViewDetails     'Detailed View
      .Title = strDialogTitle
  If .Show Then     'File selected
     varFileSelected = .SelectedItems(1)        'Can only be 1 File selected
     'Open the Excel File
     varRetVal = Shell(conEXCEL_PATH & " " & Chr(34) & varFileSelected & Chr(34), vbMaximizedFocus)
  End If
End With

[maxamis4]

If you are new to Access you want simple. The best way is to go to the button properties and under On Click paste this code. All you will have to do is change the path, the directory where the excel sheet is located.

Additionally in the code page of Access you will need to go to tools >>Reference
Select the following object library. This step you will have to do for any of the code examples provided. "Microsoft Excel xx.0 Library" xx version of excel, pick the latest.

use xlApp.Workboks.Open
 
Dim xlApp As Excel.Application
Dim xlWB1 As Excel.Workbook
 
   Set xlApp = New Excel.Application
 
   xlApp.Visible = True
 
   Set xlWB1 = xlApp.Workbooks.Open("e:\paige\vb program\delinquent.xls")
 
   xlWB1.Close
 
Set xlWB1 = Nothing
 
   xlApp.Quit
 
Set xlApp = Nothing
 
 

[brunpam]

Thank you all for your quick response!