Making pivots in xlsx files in Access VBA (1 Viewer)

[ismarti]

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

When the code starts running, it makes the first xlsx pivot correctly with no problems.
But the second iteration...

The code:

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)
        'MsgBox path
    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

Files are the same. The errors start on second iteration. I think, that the problem is in variables, but i;m not sure, I'm begginer in VBA.
BTW the code for making pivots is from Excel Macro Recorder.

The Error:

Sheets.Add:
Run time error 1004:
Method 'sheets' of object 'global' failed

 

[bastanu]

This usually happens when you don't fully qualify your objects, try to replace Sheets.Add with myWorkbookSheets.Add, Sheets("Sheet1")... with myWorkbook.Sheets("Sheet1")...you get the idea.

Cheers,
Vlad

 

[JHB]

You code is missing a lot reference back to the created Excel object.
You need the reference to the Excel object in front of the following words, (because they are unknown for MS-Access)

• Sheets
• ActiveWorkbook
• Cells
• ActiveSheet
• xlPageField
• xlPageField
• xlColumnField
• xlRowField

If you can't get it then post your database with some sample data + the Excel file + description how to run your code. Zip it because you haven't post 10 post yet.