'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 |
---|