Open Excel File - Path Syntax Problem?

[MonkeyChico]

I have a command button which when clicked, exports a query to an excel file using the following code:
-------------------------------------------
Private Sub cmd_exportquoterpt_Click()
On Error GoTo Macro_ExportQuoteRpt_Err
DoCmd.RunSavedImportExport "QuoteExport1"

Macro_ExportQuoteRpt_Exit:
Exit Sub
Macro_ExportQuoteRpt_Err:
MsgBox Error$
Resume Macro_ExportQuoteRpt_Exit

End Sub
-----------------------------------

I've been trying to figure out how add code to open the export file (TCS Quoting Test.xls) located at: \\redsv002\PLU\GeneralAccess\Book Transfer\Misc Docs\Test DBs\TCS Quoting Test

I tried using the RunApplication macro builder but the path did not work. I even tried entering -space- for the spaces, but it still couldn't find the file.

Your help would be greatly appreciated.

 

[Trevor G]

I have a command button which when clicked, exports a query to an excel file using the following code:
-------------------------------------------
Private Sub cmd_exportquoterpt_Click()
On Error GoTo Macro_ExportQuoteRpt_Err
DoCmd.RunSavedImportExport "QuoteExport1"

Macro_ExportQuoteRpt_Exit:
Exit Sub
Macro_ExportQuoteRpt_Err:
MsgBox Error$
Resume Macro_ExportQuoteRpt_Exit

End Sub
-----------------------------------

I've been trying to figure out how add code to open the export file (TCS Quoting Test.xls) located at: \\redsv002\PLU\GeneralAccess\Book Transfer\Misc Docs\Test DBs\TCS Quoting Test

I tried using the RunApplication macro builder but the path did not work. I even tried entering -space- for the spaces, but it still couldn't find the file.

Your help would be greatly appreciated.

You need to reference to Excel

In VBA goto the Tools Menu and then select references, look down the list and select Microsoft Excel 11 Object Library (The number will be different)
Then save.

Add the following code to your macro

dim xlApp as Excel.Application
Set xlApp = createobject("Excel.Application")
with xlApp
.workbooks.open " \\redsv002\PLU\GeneralAccess\Book Transfer\Misc Docs\Test DBs\TCS Quoting Test.xls "
.visible=true

end with

 

[MonkeyChico]

Thank you so much Trevor, this has been driving me crazy for two days now.

 

[Trevor G]

Pleased to have helped you resolve this issue.

The forum is a good place to seek help.

Have a good weekend.