アクセスVBAからエクセルの操作

質問

おはようございます。

アクセスVBAを使用し作成しているのですが、ここ数
日悩んでしまっている事があります。ご教授頂ける
と幸いです。宜しくお願い致します。

＜悩み・相談＞

アクセス側からエクセルに出力させた際、オートにて、ピボットテーブルを作成しようと考えております。毎月のデータ件数が違う為（月毎にピボットテ
ーブル作成のセル範囲が変わります）、どのように
VBAを組めば良いのか悩んでいます。

今現状のVBは、下記の通りです。

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'受注データ（確定）'!R1C1:R7231C31").CreatePivotTable TableDestination:="", _
TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("ピボットテーブル1").PrintTitles = True
ActiveSheet.PivotTables("ピボットテーブル1").AddFields RowFields:=Array("処理区分名", _
"地区", "集計コード", "病院名", "データ"), ColumnFields:="大分類名", PageFields:=Array("担当グループ" _
, "集計地区", "担当者", "中分類")
With ActiveSheet.PivotTables("ピボットテーブル1").PivotFields("本・個")
.Orientation = xlDataField
.Position = 1
End With

毎月のデータ件数が、7231で終われば良いのですが、毎月データ件数がランダムな為、上のVBでは使えないですよね？？データ件数に関係なく、ピボットテーブルを自動で作成するには、どこをどのように変えたら宜しいでしょうか？？

お手数ですが、ご教授頂けると幸いです。

回答No.4

Wendy02 · ベストアンサー

私は、少し、誤解していた部分があったのですが、最初にご質問で書かれたコードは、Excelの自動記録マクロのもので、これをAccessに利用できないか、という類の内容だと思います。

ActiveWorkBook は、Excel側のもので、Access 側のものではありません。ExcelのApplicationのオブジェクトとブックのオブジェクトを作って、それからコントロールさせます。

＞アクセス側からエクセルに出力させた際、
Access のVBAのコードで、Excelを開けたり、データを出力するコードはおできになっているのですか？それを、教えていただきたいのですが。

そうでないのでしたら、Access からのコントロールは止めてしまって、そのまま、#1 さんのおっしゃるように、後は、Excel側に任せてしまう方法ぐらいしかないのですが。

もちろん、私のお教えした部分も生きます。

お礼

大変参考になりました。有難うございました。

今後も色々と勉強していこうと思っております。

回答No.3

Wendy02

＞ほとんどエクセルから貼り付けたものでして、一体何の処理をしているのかが、さっぱりでして。。。

それで、Access 側のモジュールに貼り付けて、とりあえず動いているのですか？
そうでないなら、Access側で、ピボットテーブルを作ってしまったほうが早いのではありませんか？
内容的には、ほぼ同じでしょうから。

唐突に、
ActiveWorkbook.PivotCaches.Add....

と、出てきていますが、下記の場合は、一応、先の回答は、以下のように書き換えるのですが……。

'-------Sub ○○○ ---------------
Dim strDataRng As String
・
・
'(ワークブックがOpenされて）
strDataRng = ActiveWorkbook.Worksheets("受注データ(確定)").Range("A1").CurrentRegion.Address(, , xlR1C1)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'受注データ（確定）'!" & strDataRng).CreatePivotTable TableDestination:="", _
　　TableName:="ピボットテーブル1", DefaultVersion:=xlPivotTableVersion10
　　ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
　　ActiveSheet.Cells(3, 1).Select

・
・

'------End Sub ---------------

補足

有難うございます。早速書き直してみました。

実行をしますと、実行時エラー1004「worksheetクラスのpivottableプロパティを取得できません」のエラーが出てしまいました。

デバッグをしてみたところ、下記構文でエラーになっているようです。

ActiveSheet.PivotTables("ピボットテーブル.PrintTitles = True

教えて頂いた構文の直ぐ下に記述しているのですが、何か問題があるのでしょうか？

お手数ですが、宜しくお願い致します。

回答No.2

Wendy02

変わるところは、以下だけですか？

'受注データ（確定）'!R1C1:R7231C31"

strDataRng = Worksheets("受注データ(確定)").Range("A1").CurrentRegion.Address(, , xlR1C1)

データソートには、"'受注データ!" & strDataRng
とでもすればよいのでは？

補足

有難うございます。

教えて頂いた部分を変更したいのですが、なにぶんVB初心者な者でして、どこをどのように変えたら良いのかが。。。先程私が書いたVBも。ほとんどエクセルから貼り付けたものでして、一体何の処理をしているのかが、さっぱりでして。。。

お手数でなければ、教えて頂きたいのですが。

回答No.1

VT250F

アクセスVBAですべてさせようとするのも手ですが、
アクセスはエクセルのVBAを起動するだけで、
後はエクセルVBAにさせると言う手もあります。

エクセルでツール－マクロの記録で、
データの一番最後に位置づけるやり方を、
VBAにしてみると、ある程度類推できると思います。

きちんとVBAにしてくれない場合もあるので、
マクロのヘルプを見てみましょう。

補足

有難うございます。

私、正直VB初心者で、何がなんだか、さっぱり分からない状態でVBを書いています。先程のも、エクセルからそのまま貼り付けたものでして。。。

何をどのように処理したら良いのか、さっぱり？？な状態です。

お手数でなければ、教えて頂きたいのですが。。。