CreatePivotTable: Invalid procedure call or argument

G

Guest

The following code is returning an "Invalid procedure call or argument" error
on the ".CreatePivotTable" line. It works on one machine but not on another!
Both machines have the WhoDidWhatWhen workbook, and the workbook contains a
sheet called "Pivot" (on both machines). I'm stumped. Thank you for your help.

====

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\cpsa-fs\fa$\Financial
Analysis\ProjectTime.mdb;DefaultDir=\\cpsa-fs\fa$\Financial
Analysis;DriverId=25;FIL=MS Access;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblTimeReported.computername, tblTimeReported.project,
tblTimeReported.startday, tblTimeReported.timespent" & Chr(13) & "" & Chr(10)
& "FROM `\\cpsa-fs\fa$\Financial Analysis\ProjectTime`.tblTimeReported
tblTimeReported" & Chr(13) & "" & Chr(10) & "WHERE (tblT" _
, _
"imeReported" & strWorker & ") AND (tblTimeReported.startday>={ts '"
& strStart & " 00:00:00'}) AND (tblTimeReported.startday < {ts '" & strEnd &
" 00:00:00'})AND (tblTimeReported.timespent>0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With
 
G

Guest

Different versions of XL??? "xlPivotTableVersion10" The Pivot table engine
changed in 2002 and pre/post versions are not compatble.
 
G

Guest

Thanks for the reply, Jim. Unfortunately, both versions are the same (2003,
SP2).

Jim Thomlinson said:
Different versions of XL??? "xlPivotTableVersion10" The Pivot table engine
changed in 2002 and pre/post versions are not compatble.
--
HTH...

Jim Thomlinson


DesertCyclist said:
The following code is returning an "Invalid procedure call or argument" error
on the ".CreatePivotTable" line. It works on one machine but not on another!
Both machines have the WhoDidWhatWhen workbook, and the workbook contains a
sheet called "Pivot" (on both machines). I'm stumped. Thank you for your help.

====

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\cpsa-fs\fa$\Financial
Analysis\ProjectTime.mdb;DefaultDir=\\cpsa-fs\fa$\Financial
Analysis;DriverId=25;FIL=MS Access;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblTimeReported.computername, tblTimeReported.project,
tblTimeReported.startday, tblTimeReported.timespent" & Chr(13) & "" & Chr(10)
& "FROM `\\cpsa-fs\fa$\Financial Analysis\ProjectTime`.tblTimeReported
tblTimeReported" & Chr(13) & "" & Chr(10) & "WHERE (tblT" _
, _
"imeReported" & strWorker & ") AND (tblTimeReported.startday>={ts '"
& strStart & " 00:00:00'}) AND (tblTimeReported.startday < {ts '" & strEnd &
" 00:00:00'})AND (tblTimeReported.timespent>0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With
Click to expand...
Click to expand...
 
G

Guest

Any problem with data access to \\cpsa-fs\fa$\Financial Analysis\ProjectTime???
--
HTH...

Jim Thomlinson


DesertCyclist said:
Thanks for the reply, Jim. Unfortunately, both versions are the same (2003,
SP2).

Jim Thomlinson said:
Different versions of XL??? "xlPivotTableVersion10" The Pivot table engine
changed in 2002 and pre/post versions are not compatble.
--
HTH...

Jim Thomlinson


DesertCyclist said:
The following code is returning an "Invalid procedure call or argument" error
on the ".CreatePivotTable" line. It works on one machine but not on another!
Both machines have the WhoDidWhatWhen workbook, and the workbook contains a
sheet called "Pivot" (on both machines). I'm stumped. Thank you for your help.

====

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\cpsa-fs\fa$\Financial
Analysis\ProjectTime.mdb;DefaultDir=\\cpsa-fs\fa$\Financial
Analysis;DriverId=25;FIL=MS Access;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblTimeReported.computername, tblTimeReported.project,
tblTimeReported.startday, tblTimeReported.timespent" & Chr(13) & "" & Chr(10)
& "FROM `\\cpsa-fs\fa$\Financial Analysis\ProjectTime`.tblTimeReported
tblTimeReported" & Chr(13) & "" & Chr(10) & "WHERE (tblT" _
, _
"imeReported" & strWorker & ") AND (tblTimeReported.startday>={ts '"
& strStart & " 00:00:00'}) AND (tblTimeReported.startday < {ts '" & strEnd &
" 00:00:00'})AND (tblTimeReported.timespent>0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With
Click to expand...
Click to expand...
Click to expand...
 
S

Scott Gall

I just ran accross a similar issue myslef...

try:

..CreatePivotTable TableDestination:="[whodidwhatwhen.xls]pivot!R8C1",
TableName:= _

insdead of

..CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _

The following code is returning an "Invalid procedure call or argument" error
on the ".CreatePivotTable" line. It works on one machine but not on another!
Both machines have the WhoDidWhatWhen workbook, and the workbook contains a
sheet called "Pivot" (on both machines). I'm stumped. Thank you for your help.

====

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\cpsa-fs\fa$\Financial
Analysis\ProjectTime.mdb;DefaultDir=\\cpsa-fs\fa$\Financial
Analysis;DriverId=25;FIL=MS Access;MaxBuff" _
), Array("erSize=2048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT tblTimeReported.computername, tblTimeReported.project,
tblTimeReported.startday, tblTimeReported.timespent" & Chr(13) & "" & Chr(10)
& "FROM `\\cpsa-fs\fa$\Financial Analysis\ProjectTime`.tblTimeReported
tblTimeReported" & Chr(13) & "" & Chr(10) & "WHERE (tblT" _
, _
"imeReported" & strWorker & ") AND (tblTimeReported.startday>={ts '"
& strStart & " 00:00:00'}) AND (tblTimeReported.startday < {ts '" & strEnd &
" 00:00:00'})AND (tblTimeReported.timespent>0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With
On Monday, October 29, 2007 2:18 PM James_Thomlinso wrote:
Different versions of XL??? "xlPivotTableVersion10" The Pivot table engine
changed in 2002 and pre/post versions are not compatble.
--
HTH...

Jim Thomlinson


"DesertCyclist" wrote:
Click to expand...
Click to expand...
 
Top