Opening Excel File from Access in "My Documents" 2

[zionman4]

I am currently able to transfer a query from access to an Excel file using:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName1,CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Reports.xls", False, "".

How can I open this Excel file after transfering the data? I have several users that have "My Documents" folder on C drive and others on D drive.

Thanks!

 

[PHV]

Something like this ?
strPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Reports.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName1, strPath, False
DoEvents
Set objWB = GetObject(strPath)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

After I set the strPath, I get a "Compile error: Expected: end of statement" and WScript gets highlighted.

Any suggestions?

Thanks PH!

 

[PHV]

Why not posting your actual code ?
Seems like a quotes issue.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

The reason I got the error was because I was enclosing the whole path in quotes. When I removed the quotes the code ran fine; however, Excel did not open.
I am also trying to open it using this:
Call Shell("""C:\Program Files\Microsoft Office\Office10\Excel.exe"" & strPath & ", 1)
In this case, the Excel program opens up but is not finding the file...

 

[PHV]

And this ?
strPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Reports.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName1, strPath, False
DoEvents
Set objWB = GetObject(strPath)
objWB.Application.Visible = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

It opens the Excel program temporarily w/o the file and when the code gets to exit sub, Excel closes as well.

 

[PHV]

And this ?
strPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Reports.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, QryName1, strPath, False
DoEvents
Set objXL = CreateObject("Excel.Application")
DoEvents
objXL.Visible = True
DoEvents
objXL.Workbooks.Open strPath


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[zionman4]

That did it! Thanks PH, I really appreciate your time and effort!!!!

 

[Dor100]

I'm glad I found this thread. I'm having success with this modified version:

SD = Format(SD, "medium date")
ED = Format(ED, "medium Date")

xl = "H:\1\" & SD & " to " & ED & ".xls"

DoCmd.TransferSpreadsheet acExport, 8, "Excel Report Query", xl, True, ""

Dim strPath, objXL

strPath = xl

DoEvents
Set objXL = CreateObject("Excel.Application")
DoEvents
objXL.Visible = True
DoEvents
objXL.Workbooks.Open strPath