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:
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:
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:
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 SubFiles 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:
Code:
Sheets.Add:
Run time error 1004:
Method 'sheets' of object 'global' failed
