0

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

halfer
  • 19,824
  • 17
  • 99
  • 186
SachinK
  • 29
  • 6
  • You might want to use range.currentregion instead of https://learn.microsoft.com/en-us/office/vba/api/excel.range.currentregion – simple-solution Feb 23 '22 at 18:17
  • Try to avoid select in vba! https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – simple-solution Feb 23 '22 at 18:19
  • The code "ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select" is perfect! (If there is no need to avoid ".select"!) – simple-solution Feb 23 '22 at 18:21
  • You might want to avoid the two ActiveSheet. ... .Select statements and write ----------------------> TableRange = wks.Range("A1").currentregion <-- – simple-solution Feb 23 '22 at 18:23
  • Since you have a query object, could use it to determine how many columns and rows are exported and use those numbers to determine limits of Excel range. Will the number of columns always be the same? There is no need to actually Select cells. – June7 Feb 23 '22 at 19:41
  • Not seeing any code that actually pivots data, at least nothing that looks like my procedure. – June7 Feb 23 '22 at 20:36
  • Why export to Excel? Too many columns in the pivot for Access? – June7 Feb 23 '22 at 21:08
  • I changed the lines and it's working - `Dim MyTableRange As Range, Dim TableRange As String, wks.Cells(1, 1).Select, Set MyTableRange = wks.Range("A1").CurrentRegion, TableRange = wks.Name & "!" & MyTableRange.Address`. But a new error started popping up in the line - `wkb.Worksheets.Add(Before:=Sheets("Flash_Dump")).Name = "Flash_Pivot"` with the message as - "Run-time error '1004': Method 'Sheets' of object '_Global' failed". Note: this error pops up every alternate time when I run the code i.e. 1st time, 3rd time, 5th time etc. On even runs the code works fine. – SachinK Feb 24 '22 at 01:01
  • Somehow the above errors have resolved, but now the new error is coming for my pivot table creation for the TableDestination line which shows the error - "Object variable or With block variable not set" ```wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ TableRange, Version:=xlPivotTableVersion15).CreatePivotTable _ TableDestination:=wks.Name & "!" & ActiveCell.Address(ReferenceStyle:=xlR1C1), _ TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15``` – SachinK Feb 24 '22 at 03:27

0 Answers0