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