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

Quote:

Originally Posted by 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!

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:

Open the Standard Office File Dialog Window.
This Dialog will only display Excel (*.xls) Files and no other Files.
Once you Navigate to, then select a File, it will then Open it in Excel.
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.
If you decide to use this approach, and need further help, we re here.

Expand|Select|Wrap|Line Numbers

 '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.

Expand|Select|Wrap|Line Numbers

 
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!

Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

re: Open Excel File from Access Form using Wildcards

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about