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

Question

実現したいこと

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
ここにより詳細な情報を記載してください。

Comments

[logres_Fan]

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

Answer 1

セル範囲の左上は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 メソッドを実行しています。)それ以降も同様です。

Set xls = CreateObject("Excel.Application")
With xls

.ScreenUpdating = False

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

'前略

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

'縦横変換
    Dim data As Variant
    data = .Range("B4").CurrentRegion.Value '表範囲を配列に格納
    data = .WorksheetFunction.Transpose(data) '配列を縦横変換
    .Range("B4").CurrentRegion.ClearContents '表データを削除
    .Range("B4").Resize(UBound(data, 1), UBound(data, 2)).Value = data '配列をセル範囲に出力

'後略

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

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

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

Comments

[退会済みユーザー]

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

[hatena19]

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

[hatena19]

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

[退会済みユーザー]

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

Answer 2

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

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

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