'Export query from MS Access to Excel file and make pivot tables in excel file

I am trying to export a MS Access query to an Excel file and then create pivot tables in the excel file based on the data dump extracted to the excel file. My issue is that when I try to ascertain the range of the table (extracted to the excel file), I get different types of errors. My code is -

Private Sub btnExcelExport_Click()
    Dim strDesktop As String
    Dim sFileName As String
        
    strDesktop = Environ("USERPROFILE") & "\Desktop"
    sFileName = strDesktop & "\Flash_Pivot.xlsx"
    DoCmd.OutputTo acOutputQuery, "FinalReport", acFormatXLSX, sFileName, Autostart:=False
        
    Dim xls As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    
    Set xls = CreateObject("Excel.Application")
    Set wkb = xls.Workbooks.Open(sFileName)
    Set wks = wkb.Worksheets("FinalReport")
    
    
    wks.Name = "Flash_Dump"
    wks.Cells.WrapText = False
    wks.Cells.Font.Name = "Trebuchet MS"
    wks.Cells.Font.Size = "10"
    Dim TableRange As String
    wks.Cells(1, 1).Select
    ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    TableRange = Selection.Address
    MsgBox (TableRange)
    TableRange = ActiveCell.Parent.Name & "!" & wks.Range(Cells(1, 1), Cells(1, 1).End(xlDown).End(xlToRight)).AddressLocal 
    wks.Cells(1, 1).Select
    
    wkb.Worksheets.Add(Before:=Sheets("Flash_Dump")).Name = "Flash_Pivot"
    Set wks = wkb.Worksheets("Flash_Pivot")
    wks.Cells.Font.Name = "Trebuchet MS"
    wks.Cells.Font.Size = "10"
    wkb.Save
    wkb.Close True
'    Set wks = Nothing
    Set wkb = Nothing
    
    xls.Quit
    
    Set xls = Nothing
    MsgBox "Excel Formatting operation completed"
End Sub

So when running this code I get an error -

"Run-time error '91': Object variable or With block variable not set".

This appears on the line of code - ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source