VBAで型が一致しないエラー（バージョンの問題？）

とある理由があり、VBAでピボットテーブルを生成しようとしています。
ピボットキャッシュを生成する為に下記構文を用いていますが特定のパソコンで
Set PCache1のところで型が一致しないと出ます。
Dim lastRow As Long
Dim PCache1 As PivotCache
Dim PivotS1 As Worksheet
Dim DataS1 As Worksheet
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set PivotS1 = Worksheets("ITEM-STOCK")
Set DataS1 = ActiveWorkbook.Worksheets("ITEM-STOCK")
Set PCache1 = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=DataS1.Range("A1", DataS1.Cells(lastRow, 2)))
尚、
SourceData:=DataS1.Range("A1", DataS1.Cells(lastRow, 2))
を
SourceData:=DataS1.Range("A1:B1000000")・・・特定のパソコンで同じエラー
SourceData:=DataS1.Range("A1:B10000")・・・特定のパソコンでもエラー発生せず
になったので、バージョンによって格納できる桁数が違う？のかなと思っていますが
対処方法あるでしょうか？（100万行を超えるデータ数です）

エラー発生しないVBAバージョン7.1
エラー発生するVBAバージョン6.5

No.1 回答者： ngkkkkk 回答日時： 2019/06/21 16:26

別々のPCで開いているのは全て同じExcelファイルですか？

xlsとxlsx等拡張子は違っていたりしますか？

この回答へのお礼

全て同じエクセルファイルです。
拡張子も同じです。（xlsm）

お礼日時：2019/06/21 17:33

No.2 ベストアンサー 回答者： komugi3333 回答日時： 2019/06/21 22:05

当方でためしてみましたら、

　・Excel2000（VBE6.dll）→エラー
　・Excel2010（VBE7.dll）→エラー無し
となりました。

エラーが出るほうの「PivotCaches」コレクションには、「Create」メソッド自体が無く、「Add」メソッドでした。
つまり、Excelのどこかのバージョンで「Add」メソッドが無くなり、「Create」メソッドに変更になったという感じに見えます。

そして、「Add」メソッドと、「Create」メソッドは、構文が異なるようです。

「エラー発生するVBAバージョン6.5」ということでしたら、Excelのバージョンは2007でしょうか？
あるいは、2003などに互換パックなどが適用されている感じでしょうか？

多分、ですが、
　・VBAバージョン6.5→VBE6.dll
　・VBAバージョン7.1→VBE7.dll
が使われている気がするので、それぞれのdllファイルの場所を検索して、パスが異なっていたら、バージョン違いが原因かもしれません。
（通常、VBE6.dllと、VBE7.dll の格納場所は異なります。また、2003までがVBE6.dllで、2010以降は確実にVBE7.dllですが、2007がどうだったか忘れてしまいました。）

なので、エラーが出るExcelのバージョンがもし2007以前なら、
　・Excel自体を2010以降のバージョンに切り替えるか、
　・「エラーがでるパソコンの側」のExcelの「VBEのヘルプ」で、
　　「PivotCaches」コレクションを調べ、
　　もし、そのメンバーに「Create」メソッド自体が無く、
　　「Add」メソッドがあったら、「Add」メソッドの構文に書き換える、
などをしてみてはいかがでしょうか？

逆に、エラーの出るパソコンが2010以降でしたら、イミディエイトウィンドウで「? PCache1.SourceData」とやったりして、その内容に差異が無いか、あるいは、ローカルウィンドウなどで変数やプロパティの値に差異が無いか、などを調べないといけないと思います。

また、2010のExcelのほかに、2003などの古いバージョンが共存しているとどうなるのか、もしそうい状況でしたら、その場合の「PivotCaches」コレクションの「Create」メソッドや「Add」メソッドについても調べないといけなくなると思います。

No.3 回答者： komugi3333 回答日時： 2019/06/21 23:30

なんどもすみません。

古いバージョンのExcelの場合で、AddメソッドでPivotCacheを新規作成する場合、SoueceDataプロパティにRangeオブジェクトを指定すると、なぜかエラーになってしまうので、次のような感じになるっぽいです。

もしかしたら、今回のエラーもそれが原因かも？しれません・・・（検証してません。すみません。）

Sub MkPvt_Ver2000()

　Dim lastRow As Long
　Dim PCache1 As PivotCache
　Dim PivotS1 As Worksheet
　Dim DataS1 As Worksheet
　lastRow = Cells(Rows.Count, "A").End(xlUp).Row
　Set PivotS1 = Worksheets("ITEM-STOCK")
　Set DataS1 = ActiveWorkbook.Worksheets("ITEM-STOCK")

　Set PCache1 = ActiveWorkbook.PivotCaches.Add( _
SourceType:=xlDatabase, _
SourceData:="ITEM-STOCK!A1:B" & lastRow)
'↑「シート名!セル範囲」でソースを指定
'なぜかRangeオブジェクトでの指定が
'エラーになるので


　'同じシートにピボット作成
　PCache1.CreatePivotTable _
TableDestination:=PivotS1.Range("A20"), TableName:="ピボット01"

　ActiveSheet.PivotTables("ピボット01").SmallGrid = False '不要ならコメントアウト

End Sub

この回答へのお礼

ありがとうございます。
ご教授頂きました内容で実行しようとしましたが、
ピボットの行にデータをセットしようとすると
「元のデータのフィールドには、ピボットテーブルレポートで使用できる個数
より多い固有アイテムが・・・」というエラーが出てしまいました。

お礼日時：2019/06/24 13:35