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

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

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

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

Access

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

Q&A

解決済

2回答

784閲覧

AccessからExcelを操作し範囲可変の行列入れ替えを行いたい

退会済みユーザー

退会済みユーザー

総合スコア0

VBA

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

Access

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

0グッド

0クリップ

投稿2023/05/09 02:24

実現したいこと

Excelにエクスポートするクエリによってレコード数が変わるので、レコード数に応じてセル範囲を変更して行列の入れ替えを行いたい

前提

①既存のExcelファイルを開く
②B4セルを起点にクエリをエクスポート
③②でエクスポートしたデータの行列を入れ替える
④行列を入れ替えしたデータのすぐ下の行に別のクエリをエクスポートする
これらの操作をクリック時のイベントで行うフォームを作成しています。
今回の質問は③の部分です。

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

セル範囲の左上はB4で確定しているのですが、右下が不定のため、どのようにセル範囲について記述するかが見当がつきません。
また、AccessからExcelを操作するというのが今までやったことがなかったため、Excelの関数をAccessVBAでどのように記述するのか、どのような関数を使うのかも曖昧です。

該当のソースコード

Private Sub 出力_Click()

Dim dbs As Database Dim rst As Recordset Dim intRow As Integer Dim intCell As Integer Dim xls As Object Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Q_エクスポートするクエリ") Set xls = CreateObject("Excel.Application") With xls .ScreenUpdating = False .workbooks.Open "C:\Users\RY202\Desktop\出力.xlsx" intRow = 4 For intCell = 2 To 5 .Cells(intRow, intCell).Value = rst.Fields(intCell - 2).Name Next intCell .Range("B5").CopyFromRecordset rst .Cells.Select .Cells.EntireColumn.AutoFit .Range("A1").Select .ScreenUpdating = True .Visible = True End With Set xls = Nothing

End Sub

試したこと

上記コードで行列を入れ替えたいクエリをExcelに取り込むところまではできました。

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

MicrosoftAccess2019
ここにより詳細な情報を記載してください。

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

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

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

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

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

logres_Fan

2023/05/09 06:46

この辺りを参考にすれば自己解決出来るかも 【エクセルVBA】表の最終行・最終列を取得する方法のまとめ https://tonari-it.com/excel-vba-row-column/ もしくは、それぞれ別々のシートに出力して、あとはExcelで加工する話・・・。
guest

回答2

0

ベストアンサー

セル範囲の左上はB4で確定しているのですが、右下が不定のため、どのようにセル範囲について記述するかが見当がつきません。

CurrentRegionプロパティで表範囲を取得できます。
Range.CurrentRegion プロパティ (Excel) | Microsoft Learn

例えば、B4セルを含む表範囲は、Range("B4").CurrentRegion で取得できます。

また、AccessからExcelを操作するというのが今までやったことがなかったため、Excelの関数をAccessVBAでどのように記述するのか、どのような関数を使うのかも曖昧です。

縦横変換はWorksheetFunction.Transpose メソッドを使います。
WorksheetFunction.Transpose メソッド (Excel) | Microsoft Learn

Accessからエクセルの関数やメソッドを使う場合は、CreateObjectで生成したExcel.Applicationをメソッドの前に修飾すればいいです。
すでに下記の部分で同様のことをしています。(ScreenUpdating メソッドを実行しています。)それ以降も同様です。

vba

1Set xls = CreateObject("Excel.Application") 2With xls 3 4.ScreenUpdating = False

上記を踏まえて、下記のようなコードになります。

vba

1'前略 2 3Set xls = CreateObject("Excel.Application") 4With xls 5 6.ScreenUpdating = False 7 8.workbooks.Open "C:\Users\RY202\Desktop\出力.xlsx" 9 10intRow = 4 11For intCell = 2 To 5 12 .Cells(intRow, intCell).Value = rst.Fields(intCell - 2).Name 13Next intCell 14 15.Range("B5").CopyFromRecordset rst 16 17'縦横変換 18 Dim data As Variant 19 data = .Range("B4").CurrentRegion.Value '表範囲を配列に格納 20 data = .WorksheetFunction.Transpose(data) '配列を縦横変換 21 .Range("B4").CurrentRegion.ClearContents '表データを削除 22 .Range("B4").Resize(UBound(data, 1), UBound(data, 2)).Value = data '配列をセル範囲に出力 23 24'後略

④行列を入れ替えしたデータのすぐ下の行に別のクエリをエクスポートする

この場合、1行開けて出力してください。
CurrentRegionは空白行、空白列で囲まれた範囲を取得しますので。

もし、空行は無しにしたいということなら、出力してから、空行を削除するようにしてください。

投稿2023/05/09 07:00

編集2023/05/09 07:05
hatena19

総合スコア33856

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

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

退会済みユーザー

退会済みユーザー

2023/05/09 07:36

上記コードで、エクスポートしたクエリの行列入れ替えに関しては理想とする形になりました!ありがとうございます。 しかし、既存Excelフォルダにあらかじめ入力してあったB1,B2,B3のセルのデータが、B4、C5、C6に移動してしまい、さらにD7から行列を入れ替えた表が続いている感じになってしまいました(説明が下手ですいません) これはCurrentRegionの影響でしょうか?
hatena19

2023/05/09 10:50

回答で説明済みですが、CurrentRegionは空白行、空白列で囲まれた範囲を取得します。 B1,B2,B3 にデータがあれば B4 とつながってますのでそれを範囲としてしまいます。 クエリデータは1行空けてB5から出力するようにすればいいでしょう。 つづけて出力するクエリデータも1行空けて出力してください。 空行が不要ならあとからその空行を削除すればいいでしょう。
hatena19

2023/05/09 10:58

あるいは、CurrentRegionを使わずに、RcordSetのRecordCount, Fields.Count で行数、列数を取得するとか、エクセルのEndプロパティを使って最終行、最終列を取得する方法を使えば、空行を開ける必要はないです。
退会済みユーザー

退会済みユーザー

2023/05/10 00:33

様々な解決方法を提示して下さってありがとうございました! 知見が広がったように感じます。
guest

0

行列を入れ替えるんだから、挿入先の行と列を入れ替えれば良い事になります。
※同じ事は以前の質問での回答で述べています
単純に例えると以下です。

.Cells(intRow, intCell).Value = rst.Fields(intCell - 2).Name ↓ .Cells(intCell, intRow).Value = rst.Fields(intCell - 2).Name

ただ、挿入先の開始行位置については上記だとintCellの加工が必要ですし、クエリーを行ごとに繰り返す処理が必要になります。

投稿2023/05/09 06:29

sazi

総合スコア25252

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

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.44%

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

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

質問する

関連した質問