質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

Q&A

2回答

13987閲覧

AccessのVBAで、クエリの結果を既にあるエクセルファイルの指定したシート(入力用)に出力したい

veinte

総合スコア4

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

0グッド

1クリップ

投稿2020/04/17 08:27

前提・実現したいこと

Accessでクエリの結果をすでにあるエクセルファイルの指定したシート(入力用)に出力したい

発生している問題・エラーメッセージ

入力用$はすでに存在しています

該当のソースコード

Private Sub コマンド901_Click() Dim Path As String Path = CreateObject("WScript.Shell").Specialfolders("Desktop") & "\" & "見積書_草稿.xlsx" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "見積書クエリ", Path, True, "入力用!" MsgBox "エクスポートされました" End Sub

試したこと

テーブルを任意のExcelシートへ出力する方法はソースを見つけて実現することができたのですが
そのソースコードではクエリの出力ができませんでした。
下記のコードでクエリが出力できるのが理想です。

Private Sub OutputTemplateExcel() Dim strsql As String Dim strTemplate As String Dim strFileName As String Dim xlapp As Object Dim myCn As New ADODB.Connection Dim myRs As New ADODB.Recordset Const csOutPutFileName = “C:\work\EXCEL出力” Const csOutputTemplate= “テンプレート.xlsx” On Error GoTo Err_Exit 'ファイル名作成 strFileName = csOutputFileName & "_" & Format(Date, "yyyymmdd") & ".xlsx" 'EXCELアプリケーションを起動 Set xlapp = CreateObject("Excel.Application") 'セットする過程が見えないよう一旦不可視 xlapp.Visible = False Set myCn = CurrentProject.Connection strsql = "SELECT * FROM 見積書クエリ" 'レコードセットオープン myRs.Open strsql, myCn, adOpenForwardOnly, adLockReadOnly With xlapp 'テンプレートを開く strTemplate = Application.CurrentProject.Path & "\" & csOutputTemplate 'テンプレートファイルが存在しないときはエラー If Dir(strTemplate) = "" Then MsgBox "テンプレートファイルを確認してください。", vbOKOnly + vbCritical, "エラー" .Visible = True .Quit Exit Sub End If 'テンプレートファイルオープン .Workbooks.Open strTemplate '結果値出力処理(1行目にヘッダーを表示しているので、2行目1列目からセット .Cells(2, 1).CopyFromRecordset myRs '完了したら保存 .ActiveWorkBook.SaveAs FileName:=strFileName MsgBox "出力しました。", vbOKOnly + vbInformation End With Set myRs = Nothing: Close Set myCn = Nothing: Close 'Excelを終了します xlapp.Quit Exit Sub Err_Exit: MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical, "OutputExcel()" Set myRs = Nothing: Close Set myCn = Nothing: Close xlapp.Quit End Sub

補足情報(FW/ツールのバージョンなど)

何卒よろしくお願いします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hatena19

2020/04/17 14:02 編集

前者のコード(DoCmd.TransferSpreadsheet)でも、後者のコード(CopyFromRecordset)でも、クエリの出力はできると思いますが、どのようにうまくいかないのでしょうか。
hatena19

2020/04/17 14:06

Set myRs = Nothing: Close Set myCn = Nothing: Close この部分は間違いですね。下記が正しいです。 myRs.Close: Set myRs = Nothing myCn.Close: Set myCn = Nothing Set myCn = Nothing
veinte

2020/04/18 04:19

コメントありがとうございます。 前者のコードだとクエリ自体の出力はできるのですが、既存のエクセルファイル内への出力がうまくできません。 また後者のコードを実行すると -2147217904:1つ以上の必要なパラメーターの値が設定されていません。 となってしまいます。 修正のコードに入れなおしても同じ結果でした・・・。
veinte

2020/04/18 04:20

ちなみに strsql = "SELECT * FROM 見積書クエリ" この部分をクエリではなくテーブル名に直すと問題なく出力されるので、クエリで動かないのはなぜだと悩んでいます・・・。
guest

回答2

0

見たソースコードには、何点かNG箇所があります。
一つは、レコードセットオブジェクトのインスタンスを作っていないこと。
ようするに、Set 文がありません。
レコードセットオブジェクトが無いのに、どうやって、データを扱うのでしょうか?

また、Openメソッドには、Optionパラメータがあります。
Optionパラメータには、adCmdText → SQL文、adCmdTable → テーブル 等が存在します。それを指定しましょう。
例題の場合は、SQL文をレコードセットにしているので、adCmdTextのパラメータ値を使用します。

Access

1 'レコードセット 2 Dim cnn As ADODB.Connection 3 Dim rst As ADODB.Recordset 4 Set cnn = CurrentProject.Connection 5 Set rst = New ADODB.Recordset 6 With rst 7 .CursorLocation = adUseServer 8 .CursorType = adOpenKeyset 9 .LockType = adLockReadOnly 10 End With 11 rst.Open "select * from 名簿", cnn, , , adCmdText 12 13 'Excelに出力 14 Dim appExcel As Excel.Application 15 Dim wb As Excel.Workbook 16 Set appExcel = CreateObject("Excel.Application") 17 appExcel.Visible = True 18 Set wb = appExcel.Workbooks.Open("C:\Data\Excel\しゅちゅりょく.xlsx") 19 wb.Worksheets(1).Range("B1").CopyFromRecordset rst 20

投稿2020/06/06 01:54

kai_keitai

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

DoCmd.TransferSpreadsheet はシート名を指定してのエクスポートはできないですね。
エクスポート先にクエリと同じ名前で定義されたセル範囲があればそこにエクスポートされるということだそうです。

Access2019から「DoCmd.TransferSpreadsheet メソッド - マイクロソフト コミュニティ

-2147217904:1つ以上の必要なパラメーターの値が設定されていません。

エクスポートするクエリがパラメータクエリではないですか。だとしたら、レコードセットを Open する前に、Paremetersプロパティの設定が必要です。詳細は下記をご参考に。

Access VBA:ADOでパラメータクエリーを実行してレコードセットを取得するサンプルプログラム

投稿2020/04/18 06:38

hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問