export to and open excel without specifying directory

[Mike Brearley]

Currently I am specifying the exact path to the excel file in both vb code
for buttons and in the 'Run App'

The macro for run app:
excel.exe "c:\folder\file.xls"

In the VB code, I'm exporting data out to the excel file:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
"c:\folder\file.xls", True

I'd like to be able to specify the filename and have the directory use the
current directory that the database is running in. Please help.

Thanks...

 

[Douglas J Steele]

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery",
CurrentProject.Path & "\" & "file.xls", True

 

[Mike Brearley]

Thank you Doug!! You pointed me in the right direction and I now have that
part working.

A couple of things to note:

If there are spaces in the folder structure, be sure to put in quotes like
this:
, """" & CurrentProject.Path & "\" & "file.xls" & """", True

I found to open the excel file, I can use this:
Shell "excel.exe " & """" & CurrentProject.Path & "\Machine_OEE_Chart.XLS" &
"""", vbNormalFocus

Thanks,
Mike