Opening Excel Workbook from Access after TransferSpreadsheet

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

Does anybody know why Access gives me the error message that it cannot find
the file I'm specifiying in the xl.workbooks.open function? If I just
created it, why can't it find it? And I have looked and it is there with the
filename I sprecified.

Dim strFilename As String

'Export TSC Open Order Reports
strFilename = "" & Format(Date, "mm-dd-yyyy") & ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOpenOrdersNonStorage", _
"G:\Sync\Public\DBArchives\TSCOpenOrderReports\" & strFilename

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOpenOrdersStorage", _
"G:\Sync\Public\DBArchives\TSCOpenOrderReports\" & strFilename

'Open Open Order Reports and Format
Dim xl As Object 'Excel application
Dim wr As Object 'Excel workbook
Dim sh As Object 'Excel worksheet

Set xl = CreateObject("Excel.Application")
Set wr = xl.Workbooks.Open("G:\Sync\Public\DBArchives\TSCOpenOrderReports\"
& strFilename)

I thoroughly appreciate all the help everyone has provided me on here over
the past several months. Hopefully someday I'll know enough to give back :)
 
You might try putting a couple calls to DoEvents in there so the operating
system can update its information on the files on the disk.

Dim strFilename As String

'Export TSC Open Order Reports
strFilename = "" & Format(Date, "mm-dd-yyyy") & ""
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOpenOrdersNonStorage", _
"G:\Sync\Public\DBArchives\TSCOpenOrderReports\" & strFilename

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryOpenOrdersStorage", _
"G:\Sync\Public\DBArchives\TSCOpenOrderReports\" & strFilename

DoEvents 'One should be sufficient
DoEvents 'But two won't hurt

'Open Open Order Reports and Format
Dim xl As Object 'Excel application
Dim wr As Object 'Excel workbook
Dim sh As Object 'Excel worksheet

Set xl = CreateObject("Excel.Application")
Set wr = xl.Workbooks.Open("G:\Sync\Public\DBArchives\TSCOpenOrderReports\"
& strFilename)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I think I figured it out on my own. I didn't use a file extension in the
file name. The code still isn't working (I have more kinks to work out), but
I'm not hung up on that problem anymore. If it still doesn't work, I'll
try your advice. Thanks for the quick response.
 
Back
Top