Making pivots in xlsx files in Access VBA

Question

There are some xlsx files in "c:", exported from Microsoft Access table. There are about 4 files with equal number and names of columns but different data.

When the code starts running, it makes the first xlsx pivot correctly with no problems, but the second iteration causes errors here:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    fileName, Version:=6).CreatePivotTable TableDestination:= _
    "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6

Error: Run- time error '91' : Object Variable or with block variable not set

I've already tried to use "myWorkbook" before Sheets, ActiveWorkbook etc., but it didn't work or I didn't do it properly.

All Excel files should have pivot tables.

Sub test()
    Dim strF As String, strP As String
    Dim wb As Workbook
    Dim ws As Worksheet


    'Edit this declaration to your folder name
    strP = "c:\" 'change for the path of your folder


    strF = Dir(strP & "\*.xls*") 'Change as required



    Do While strF <> vbNullString
        'MsgBox strP & "\" & strF
        createPivot strP & "\" & strF, strF
        strF = Dir()
    Loop    
End Sub


Sub createPivot(path As String, fileName As String)

    fileName = Replace(fileName, ".xlsx", "")
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook

    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(path)
    appExcel.Visible = True

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        fileName, Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field1")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field2")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field3")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("FieldN"), "Sum of FieldN", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field+1")
        .Orientation = xlRowField
        .Position = 1
    End With

    myWorkbook.Save
    myWorkbook.Close

    appExcel.Quit

    Set myWorkbook = Nothing
    Set appExcel = Nothing
Exit Sub
End Sub

*"causes errors"* wich error and in which line? • It is very likely that your issue is because of using `ActiveSheet` and `Select`: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Jun 19 '19 at 08:10
Please [edit] your question and put all the additional information there. Code in comments is not readable. Also try to accomplish what is suggested in the link I posted. There is a high proability that this fixes your issues alread. — Pᴇʜ, Jun 19 '19 at 08:17

Asger · Accepted Answer · 2019-06-19T13:57:09.283

The source range for your pivotcache might be wrong. You use "filename" for that (is it a named range corresponding to the file's name, which is valid within each workbook?).

I suggest following:

build your code object by object: workbook, pivotcache, worksheet, pivottable, pivotfields, ...
as you use two applications: declare almost every variable very clear, like Excel.Workbook
avoid selecting or activating anything

Sub test()
    Dim strF As String, strP As String
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet

    'Edit this declaration to your folder name
    strP = "c:\users\..." 'change for the path of your folder

    strF = Dir(strP & "\*.xls*") 'Change as required

    Do While strF <> vbNullString
        'MsgBox strP & "\" & strF
        createPivot strP & "\" & strF, strF
        strF = Dir()
    Loop
End Sub


Sub createPivot(path As String, fileName As String)
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook
    Dim myWorksheet As Excel.Worksheet
    Dim pc As Excel.PivotCache
    Dim pt As Excel.PivotTable

    fileName = Replace(fileName, ".xlsx", "")

    On Error Resume Next
    Set appExcel = GetObject(, "Excel.Application")
    On Error GoTo 0
    If appExcel Is Nothing Then Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True

    Set myWorkbook = appExcel.Workbooks.Open(path)

    Set pc = myWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=myWorkbook.Sheets(1).UsedRange) ' this might be adapted

    Set myWorksheet = myWorkbook.Sheets.Add
    Set pt = pc.CreatePivotTable( _
        TableDestination:=myWorksheet.Range("A3"), _
        TableName:="PivotTable1")

    With pt.PivotFields("Field1")
        .Orientation = xlPageField
        .Position = 1
    End With

    With pt.PivotFields("Field2")
        .Orientation = xlPageField
        .Position = 1
    End With

    With pt.PivotFields("Field3")
        .Orientation = xlColumnField
    End With

    With pt.PivotFields("FieldN")
        .Orientation = xlDataField
        .Function = xlSum
        .Name = "Sum of FieldN"
    End With

    With pt.PivotFields("Field+1")
        .Orientation = xlRowField
        .Position = 1
    End With

    myWorkbook.Save
    myWorkbook.Close
    Set myWorkbook = Nothing

    appExcel.Quit
    Set appExcel = Nothing
End Sub

score 0 · Answer 2 · answered Jun 26 '19 at 12:41

You can control Excel from Access, using either Early Binding or Late Binding.

' EARLY BINDING
Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to Declare them
' Here and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile As String

strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

'Opens up the Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
'Set xlWS = xlWB("Sheet2")

With xlWS ' You are now working with the Named file and the named worksheet


End With

'Do Close and Cleanup
End Sub


 
' LATE BINDING
Sub ControlExcelFromAccess()

' No reference to a type library is needed to use late binding.
' As long as the object supports IDispatch, the method can
' be dynamically located and invoked at run-time.

' Declare the object as a late-bound object
  Dim oExcel As Object
  Dim strFile As String

  strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

  Set oExcel = CreateObject("Excel.Application")

' The Visible property is called via IDispatch
  oExcel.Visible = True

  Set xlWB = oExcel.Workbooks.Open(strFile)

'Call code here . . .

Set oExcel = Nothing

End Sub

Making pivots in xlsx files in Access VBA

2 Answers2