DoCmd.TransferSpreadsheet not Exporting to Excel
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:
Code:
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.