D
DesertCyclist
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 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