DoCmd.TransferSpreadsheet not Exporting to Excel

[jeversf]

Hi All

VBA newbie here trying to become a little more efficient in my day to day routine.

Goal:
Write VBA code in Access to Export query "qry_3213_ALL_Crosstab" to existing excel file tab (with same name) stored in ""C:\Database Exports\UPM.xlsx". I'd like to code to clear the contents of the sheet before importing query data.

What I have tried so far:

Option Compare Database
Option Explicit


Private Sub ExportCrossTabQuery()


Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
       
Set xlApp = New Excel.Application


With xlApp
    .Visible = True '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''REMINDER: CHANGE THIS TO FALSE WHEN CODING COMPLETE
        Set xlWB = .Workbooks.Open("C:\Database Exports\UPM.xlsx", , False)
            .Sheets("qry_3213_ALL_Crosstab").Cells.ClearContents ''''''''''''''''''''''''''''''''''''FORMATTING MAINTAINED, ONLY CONTENTS ARE CLEARED
End With


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_3213_ALL_Crosstab", "C:\Database Exports\UPM.xlsx" '''''EXPORT TO SPECIFIED EXCEL FILE


End Sub

Microsoft Office / OS:
Office 2013 / Windows 7

Outcome:
Everything works as it should up until the End With statement, then it returns a run-time error 3010 - table 'qry_3213_ALL_Crosstab' already exists. I've double checked and confirmed that file locations, file/query names are correct.

I would appreciate any assistance with this matter and would like to hear peoples tips for best practices when it comes to using VBA and any recommendations to websites/reading material that could aid me in my quest to just become better at VBA.

Thanks in advance.

[jonh]

I imagine sheets is a property of workbook but your with applies it to xlapp.

Best practice? Whatever you can get away with.
Close and destroy all objects when you're done with them. If you can be bothered.

How to get better? Places like this. I don't come here and post answers because I'm a good samaritan. I haven't used Access for anything serious in nearly 10 years. Trying to answer questions and reading other peoples responses gives me practice and keeps me learning new stuff.

[jonh]

Hmm, I tried your code and it seems to work ok for me.

If you split code into distinct parts, it's easier to reuse in other places and makes debugging easier...

eg

Private Sub test_Click()
    ClearSheet "C:\Database Exports\UPM.xlsx", "qry_3213_ALL_Crosstab"
    ExportCrossTabQuery "C:\Database Exports\UPM.xlsx", "qry_3213_ALL_Crosstab"
End Sub

Private Sub ExportCrossTabQuery(wb As String, tbl As String)
On Error Resume Next
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tbl, wb
    If Err.Number Then Debug.Print Err.Number, Err.Description
End Sub

Private Sub ClearSheet(wb As String, tbl As String)
On Error Resume Next
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    
    Set xlWB = xlApp.Workbooks.Open(wb, , False)
    If Err.Number Then Debug.Print Err.Number, Err.Description
    Set xlSht = xlWB.Sheets(tbl)
    If Err.Number Then Debug.Print Err.Number, Err.Description
    xlSht.Cells.ClearContents
    xlWB.Close Err.Number = 0
    xlApp.Quit
End Sub

[jeversf]

Thanks for the info Jon.

With the same code (as above), I am now getting a run-time error 3011 message. Any thoughts on why this could happen, considering I haven't changed a thing?

[jonh]

Sorry, I haven't memorised all of the error numbers. Does it give a description?

[jeversf]

Run-time error '3011:

The Microsoft database engine could not find the object
'qry_3213_ALL_Crosstab'. Make sure the object exists
and that you spell its name and the path correctly.
If 'qry_3213_ALL_Crosstab' is not a local object, check
your network connection or contact the server administrator.

I can confirm that the qry exists and its spelling and path are correct.

[jonh]

Open task manager, kill any open Excel processes and try again.

[jeversf]

No luck. Same problem occurs.

[jeversf]

I got it Jon. Had to add the workbooks.close code prior to the docmd.