L
LTofsrud
Good morning everyone,
I am using Microsoft Access XP.
I am kind of confused as to why I might be getting the following error when
attempting to create a pivot table through automation. The error is a
typical Run-time error 5:
"Invalid procedure call or argument"
While it is usually a simple error, I only get it every once in a while, so
I am wondering if it could be a timing issue when I am creating the Excel
spreadsheet and trying to create the pivot table itself. Here is a code
snippet where it is crashing (I had to remove the Command Text since it had
some company information in it):
With objWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection =
"ODBC;DBQ=P:\SourceData\Extract.mdb;DefaultDir=P:\SourceData;Driver={Microsoft
Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3"
.MaintainConnection = False
.CommandType = xlCmdSql
.CommandText = "SELECT...<removed due to length/confidential>;"
.CreatePivotTable TableDestination:=Range("A3"),
tableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
It is dying on the 'CreatePivotTable TableDestination...' line. The odd
thing is that it doesn't do it all the time; maybe once out out of every 3
runs and I can't seem to determine a trend. Has anyone else ran into this
kind of an issue when creating a pivot table through automation in Excel?
Thanks for your time.
Lance
I am using Microsoft Access XP.
I am kind of confused as to why I might be getting the following error when
attempting to create a pivot table through automation. The error is a
typical Run-time error 5:
"Invalid procedure call or argument"
While it is usually a simple error, I only get it every once in a while, so
I am wondering if it could be a timing issue when I am creating the Excel
spreadsheet and trying to create the pivot table itself. Here is a code
snippet where it is crashing (I had to remove the Command Text since it had
some company information in it):
With objWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection =
"ODBC;DBQ=P:\SourceData\Extract.mdb;DefaultDir=P:\SourceData;Driver={Microsoft
Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3"
.MaintainConnection = False
.CommandType = xlCmdSql
.CommandText = "SELECT...<removed due to length/confidential>;"
.CreatePivotTable TableDestination:=Range("A3"),
tableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
It is dying on the 'CreatePivotTable TableDestination...' line. The odd
thing is that it doesn't do it all the time; maybe once out out of every 3
runs and I can't seem to determine a trend. Has anyone else ran into this
kind of an issue when creating a pivot table through automation in Excel?
Thanks for your time.
Lance