Advertisement

04.10.2008 at 01:49PM PDT, ID: 23313322
[x]
Attachment Details

DoCmd.Transfer Spreadsheet gives Run-time error 3011

Asked by JazCat in Access Coding/Macros

Tags: Microsoft, Access, 2003

I have a large database running just fine in Access 2003 on my computer. I copied the entire database and sent it to a colleague for him to use.  The import portion of the database works fine for him but, when he attempts to export the first set of Excel spreadsheets, it gives the error:

Run-time error '3011':
The Microsoft Office Access database engine could not find the object 'D:'Mibis web access\Web\Canada.xls'.  Make sure the object exists and that you spell its name and path correctly.

The line that is highlighted in the debug command is:
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyMetricsDetails", CurrentProject.path & "\Web\" & rst!Zone & ".xls", , rst!Zone

First, this file should not exist, it is actually deleted in the code, so the error message is strange. I deleted all my files and it ran fine without them there.  I commented out every step of the Sub and everything works up to the export spreadsheet command.  I had him pull the rst!Zone references out, just send it to a specific Excel file, same error.  I had him manually export the query using right click and it worked fine.   We verified all References are the same between our computers for VBA for both Access and Excel. He is even able to run the same database off a server, using the Access on his computer, without any errors.  But when he tries to run it with the mdb file on his computer he gets the error.

I even had him stand on one foot and cross his fingers but that didn't work either.  I am totally stumped. Any ideas?Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

Private Sub cmdZoneformat_Click()
 
Dim rst As DAO.Recordset
Dim flags As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT Zone FROM [LCT and Zones]")
Set flags = CurrentDb.OpenRecordset("Flags")
 
rst.MoveFirst
flags.MoveFirst
 
Do While Not rst.EOF
 
'First remove existing MIBIS Web Site Details spreadsheet
   Call DeleteFile(CurrentProject.path & "\Web\" & rst!Zone & ".xls")
    'Reset the SQL statement with Zone proceeded
    
    MsgBox rst!Zone & " file has been deleted"
    
    CurrentDb.QueryDefs("MyMetricsDetails").SQL = "SELECT qry_MIBIS_Metrics_Details.*, " & _
    "qryMonthlyCumulativeCost.[Monthly Cumulated Cost], qryQuarterlyCumulativeCost.[Quarterly Cumulated Cost], " & _
    "qryYearlyCumulativeCost.[Yearly Cumulated Cost] FROM ((qry_MIBIS_Metrics_Details LEFT JOIN " & _
    "qryYearlyCumulativeCost ON qry_MIBIS_Metrics_Details.[Cryo Id] = qryYearlyCumulativeCost.[Cryo id]) " & _
    "LEFT JOIN qryQuarterlyCumulativeCost ON qry_MIBIS_Metrics_Details.[Cryo Id] = qryQuarterlyCumulativeCost.[Cryo id]) " & _
    "LEFT JOIN qryMonthlyCumulativeCost ON qry_MIBIS_Metrics_Details.[Cryo Id] = qryMonthlyCumulativeCost.[Cryo id]" & _
    "WHERE ([qry_MIBIS_Metrics_Details]![Zone]= '" & rst!Zone & "')"
 
    MsgBox "Dumped data into MyMetricsDetails"
    
    'Exports pass-through table
    
    MsgBox CurrentProject.path
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyMetricsDetails", CurrentProject.path & "\Web\" & rst!Zone & ".xls", , rst!Zone
     
    MsgBox "Exported file " & rst!Zone
[+][-]04.10.2008 at 02:17PM PDT, ID: 21329483

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.10.2008 at 03:34PM PDT, ID: 21329971

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.10.2008 at 07:35PM PDT, ID: 21331055

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]04.11.2008 at 08:52AM PDT, ID: 21335397

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]04.21.2008 at 08:22AM PDT, ID: 21402804

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: Access Coding/Macros
Tags: Microsoft, Access, 2003
Sign Up Now!
Solution Provided By: JazCat
Participating Experts: 2
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628