ExcelからAccessでデータ集計を実行させ、その結果をExcelに戻したい

Question

【やりたいこと】
ExcelからAccessを起動しAccessで集計した結果を、もとのExcelのシートへ貼り付けたい。

【詳細】
１．Excelに作成したマクロ実行ボタンからAccessを起動

Sub Aecees集計()
    Dim objACCESS As Object  
    Set objACCESS = CreateObject("Access.Application")
    objACCESS.OpenCurrentDatabase ActiveWorkbook.Path & "\仕入用.accdb"
    With objACCESS
        .Visible = false
        .Run "Execute_Manager", Format(Cells(2, "c").Value, "yyyymm")        
    End With
    Set objACCESS = Nothing
End Sub

　２．Accessでは
・データのリンク処理
・クエリの実行(テーブル作成クエリ）
・作成したテーブルをCopyFromRecordsetでExcelのシートへ貼り付け

　３．その後、Excelへ戻り貼り付けたシートをもとに計算処理などをして保存して終了

【困っていること】
Accessから最初に開いていたExcelのシートにデータを貼り付ける処理がわかりません。
Accessからファイル名を指定して開くと、当然ですが別ファイルが開き困っています。
（ユーザーの操作はExcelのみとしたいため、また貼り付けた後に数値を確認し、
手修正で微調整を行い最終的に計算結果を確定させるため、このような処理の流れを検討しています）

どなたかお知恵をぜひお貸しください。。。
よろしくお願いいたします。

Comments

[takamak]

皆様
ご回答ありがとうございます。

Accessにすでにあるテーブルではなく、
Excelからボタンを押したら必要なデータをAccess内にインポートして集計し、その結果をExcelへ戻したいため、ExcelオブジェクトをAccessに渡せれば…
と思っているのですが書き方が分からず、困っています。

.Run "Execute_Manager", Format(Cells(2, "c").Value, "yyyymm") ←ここで渡す？？

また、Excelに戻したいテーブルは複数あります。

もともとExcelで、必要なデータ(レコード数もかなりあります)を複数のシートに貼りつけて計算処理をさせて複数シートを完成させていたのを
業務改善するためにAccessとの連携に切り替えようと思っています。

宜しくお願い致します。

[退会済みユーザー]

ここは追記修正依頼欄なので、情報不足があるなら質問を編集して追記してください。
いずれにせよ、何が不明なのかちょっとよく判らないんですけど。

Answer 1

思いついたアイデアだけ
実現性は不明です

1. Access側の関数'Execute_Manager'の引数にEXCELのオブジェクト

例えばWorkbookオブジェクト OR Worksheetオブジェクト OR Rangeオブジェクトを渡して、Execute_Manager内で中身を設定する。

1. Access側の関数'Execute_Manager'の戻り値をobject型にしていおいてRecordsetオブジェクトを設定して返し、Excel側のコードでCopyFromRecordsetメソッドを実行する。

2. Access側の関数'Execute_Manager'の戻り値をVariant型にしていおいてRecordsetの結果を2次元配列に変換して設定して返し、Excel側のコードで2次元配列をRangeに設定する

Answer 2

Accessのテーブルデータまたはクエリデータをエクセルのシートに貼り付けたいということですよね。
ならば、Accessを起動する必要はありません。

・作成したテーブルをCopyFromRecordsetでExcelのシートへ貼り付け

そもそも CopyFromRecordset は Access.Application のメソッドではありません。
ADODBのメソッドです。

「Excel ADODB CopyFromRecordset」でWEB検索すると解説やサンプルコードがいろいろ見つかるのでまずはそれで勉強して、コードを書いてみてください。

例えば、

【VBA】テーブル（Access）のデータをワークシートに転記する

Answer 3

ExcelからADOでAccessのクエリ実行すればいいんじゃないですかね。
ADOを使ってExcelからAccessデータを利用してみよう
"excel ado access"でググったら他にも記事出てくると思うので探してみてください。

Answer 4

ご回答いただいた皆様ありがとうございました。
またサイトの使い方を理解できておらず、記載方法が悪く申し訳ございませんでした。

ExcelからAccessを裏で動かし、テーブルの作成→Excelのシート操作までを行わせようと思っておりましたが
回答内容から考え方に間違いがあることに気づき、
Accessではテーブル作成までで処理を終了させ、残りはExcel内で行うように変更いたしました。

参考URL等とても勉強になりました。
ありがとうございました。

Sub Aecees集計()

    Dim objACCESS As Object  
    Set objACCESS = CreateObject("Access.Application")
    objACCESS.OpenCurrentDatabase ActiveWorkbook.Path & "\仕入用.accdb"

    objACCESS.Run "Execute_Manager", Format(Cells(2, "c").Value, "yyyymm") 
　　'モジュール"Execute_Manager"では ソースデータのリンク/テーブル作成クエリを実行　       
    Set objACCESS = Nothing
    Call AccessDataImport

End Sub


Sub AccessDataImport()
  Dim CN As New ADODB.Connection
  Dim RS As New ADODB.Recordset
  Dim dbFile As Variant
  
  dbFile = ActiveWorkbook.Path & "\仕入.accdb"
  CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & dbFile & ""
  CN.Open
    
'        '----- 計算シート---------------------------------
        RS.Open "計算", CN, adOpenKeyset
        Sheets("計算").Range("A10").CopyFromRecordset RS
        RS.Close

'        '----- 通知書シート---------------------------------
        RS.Open "通知書;", CN, adOpenKeyset
        Sheets("通知書").Range("C5").CopyFromRecordset RS
        RS.Close: Set RS = Nothing
        Calculate              '再計算

    '連番もろもろ
    '書式設定もろもろ

        xlApp.Calculation = xlCalculationAutomatic
        xlBook.SaveAs StrPath & "_" & strDate & ".xlsm"
        xlBook.Close: Set xlBook = Nothing
        xlApp.Quit: Set xlApp = Nothing

End Sub

Comments

[退会済みユーザー]

他回答と全然違う方法で解決したならともかく、
他回答を参考にしていて自己回答にベストアンサーは無いでしょう。

[takamak]

すみません。操作がいまいちわかってなくて自己解決に記載したところベストアンサーになってしまいました・・・。すみません。

[退会済みユーザー]

ベストアンサーは変更出来る筈です。

[退会済みユーザー]

つーか、過去にも似たような事やってんのか。スコアマイナスも納得。
こんな事ばかりやってると、誰も回答付けてくれなくなるよ。