CreatePivotTable: Invalid procedure call or argument
 

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:=xlExter nal)
.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.timespent0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With

CreatePivotTable: Invalid procedure call or argument
 

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

Jim Thomlinson


"DesertCyclist" wrote:

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:=xlExter nal)
.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.timespent0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With

CreatePivotTable: Invalid procedure call or argument
 

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

"Jim Thomlinson" 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:

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:=xlExter nal)
.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.timespent0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With

CreatePivotTable: Invalid procedure call or argument
 

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

Jim Thomlinson


"DesertCyclist" wrote:

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

"Jim Thomlinson" 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:

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:=xlExter nal)
.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.timespent0)" _
)
.CreatePivotTable TableDestination:="[whodidwhatwhen]pivot!R8C1",
TableName:= _
"PivotTable2", , DefaultVersion:=xlPivotTableVersion10
End With

CreatePivotTable: Invalid procedure call or argument
 

I just ran accross a similar issue myslef...

try:

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

insdead of

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


On Monday, October 29, 2007 2:14 PM DesertCyclis wrote:

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:=xlExter nal)
.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.timespent0)" _
)
.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:


On Monday, October 29, 2007 2:35 PM DesertCyclis wrote:

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

"Jim Thomlinson" wrote:


On Monday, October 29, 2007 4:01 PM James_Thomlinso wrote:

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

Jim Thomlinson


"DesertCyclist" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
FireAndForget Asynchronous Utility Class for SQL Server Inserts and Updates
http://www.eggheadcafe.com/tutorials...d-updates.aspx