オートメーション機能を利用してAccessのデータをExcel経由で編集して印刷するお手本コード集（基礎編）
図6-1-1 AccessからExcelを起動してワークシート上にデータを作成するサンプルコード集
このサンプルデータベースでは、AccessからExcelを起動して、ワークシートにデータを貼り付ける方法などオートメーションの基礎的なノウハウをサンプルコード集として紹介します。メニューのリストボックスからアイテムをクリックして、Show Codeボタンをクリックすると、サンプルコードがVBE(Visual Basic Editor)上に表示されます。Run Exampleボタンをクリックするとサンプルプログラムを実行しますので、実行結果を確認することができます。
メニューからShow Codeボタンをクリックしたとき、VBE上にソースコードを表示するには、モジュールのFind、LinesそしてAddFromStringメソッドを使用します。Findメソッドでは、引数に”Sample1()”と”End Function”を指定して関数の開始行と終了行を求めて行数を計算します。行数を計算したら、Linesメソッドを使用してメモリ変数に、サンプルコードを抽出します。最後に、DoCmdのOpenModuleメソッドでモジュールbasViewCodeを開いたら、AddFromStringメソッドで抽出したサンプルコードを挿入します。
Dim modDemo As Module
Dim modAutomation As Module
Dim strCodeText As String
・・・・
DoCmd.OpenModule “basAutomation1”
Set modAutomation =
Modules(“basAutomation1”)
intRC=modAutomation.Find(“Sample1()”,･･･)
intRC=modAutomation.Find("End Function",･･･)
strCodeText =
modAutomation.Lines(StartLine, LineCount,･･･)
・・・・
DoCmd.OpenModule "basViewCode"
Set modDemo = Modules("basViewCode")
modDemo.AddFromString (strCodeText)
メニューから、Run Exampleボタンをクリックしたとき、サンプルを実行するにはEval()関数を使用します。例えば、モジュールbasAutomation1の関数Sample1()を実行するには、Eval()関数の引数に関数名”Sample1()”を指定します。
varRC = Eval(“Sample1()”)
本章では、サンプルコード集を中心に解説しますので、Show Code, Run Exampleボタンをクリックしたときの処理については、frmAutomation Switchboardのフォームモジュールを参照してください。
● AccessからExcelを起動するには（Early Binding方式）
１ Accessを起動したら作業フォルダに、新規データベース CH6-1.mdbを作成します。
２ データベースウィンドウが表示されたら、モジュール
タブをクリックして新規作成ボタンをクリックします。
３ VBEが起動されてモジュールウィンドウが表示されたら、VBEのツールメニューから参照設定をクリックします。参照設定のダイアログが表示されたら、参照可能なライブラリファイルからMicrosoft Excel X.X Objct LibraryとMicrosoft DAO X.X Object Libraryを選択して、OKボタンをクリックします。Visual Basic For Applications、Microsoft Access X.X Object Library、OLE AutomationそしてMicrosoft ActiveX Data Objects X.X Libraryは、既に選択されています。
図6-1-2 リストボックスからMicrosoft Excel 9.0 Object Library、Microsoft DAO 3.6 Object Libraryを選択した例
４ モジュールウィンドウに戻ったら、CH6-1.mdbを開いて、basAutomation1のSample1をコピーして貼り付けます。
５ VBEの表示メニューからイミディエイトウィンドウをクリックして表示させます。イミディエイトウィンドウにSample1を入力したら、リターンキーを押して実行させます。Excelが起動されて、Excel の画面に切り替わります。
図6-1-3 イミディエイトウィンドウからSample1を実行した例
６ WindowsのタスクバーからAccessのアイコンをクリックしてフォーカスを移動します。”Sample1 – Early Binding”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
図6-1-4 MsgBoxのOKボタンをクリックしてExcelを終了させます。
７ Sample1をbasAutomation1の名称で保存して、Accessを終了させます。
Function Sample1()の説明
Sample1では、VBAからExcelを起動するとき、Early Binding方式を採用しています。Dim xlApp As New Excel.Applicationステートメントで、Excelのオブジェクトを指定していますので、VBAのコンパイル時、Excelのオブジェクトがバインドされます。Excelのオブジェクトは、New, CreateObject(), GetObject()メソッドを使用して生成します。 New Excel.ApplicationでExcelのインスタンスが生成されて、オブジェクトポインタがxlAppに設定されます。xlApp.Visible=TrueでExcelの可視プロパティをTrueに設定すると、Excelの画面が表示されます。MsgBoxでAccessが待ち状態になるとExcelの画面に切り替わります。MsgBoxのOKボタンをクリックすると、xlApp.Quitが実行されてExcelを終了させます。最後に、Set xlApp=Nothingで、Excelのオブジェクトを解放して終了します。
リスト6-1-1 Sample1()のソースコード
|
Function Sample1() ' ' AccessからExcelを起動する（Early
Binding方式） ' Dim xlApp As New Excel.Application
With xlApp .Visible = True MsgBox "Sample1 - Early Binding" .Quit End With Set xlApp = Nothing |
● AccessからExcel を起動するには (Late Binding方式)
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation1を開きます。
３ VBEが起動されてモジュールウィンドウが表示されたら、CH6-1.mdbを開いて、モジュールbasAutomation1のSample2をコピーして貼り付けます。
４ VBEの表示メニューからイミディエイトウィンドウを表示させて、Sample2を実行させます。Excelが起動されて、Excelの画面に切り替わります。
５ Windowsのタスクバーから、Accessのアイコンをクリックしてフォーカスを移動します。 “Sample2- Late Binding”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
６ basAutomation1を保存して、Accessを終了させます。
Function Sample2()の説明
Sample2では、VBAからExcel を起動するとき、Late Binding方式を採用しています。Dim xlApp As Objectステートメントでは、Excelのオブジェクトを指定していませんので、Early Binding方式のようにVBAのコンパイル時、Excelのオブジェクトがバインドされません。Excelのオブジェクトは、CreateObject("Excel.Application")のステートメントが実行されたときに、初めてバインドされます。CreateObject()で、Excelのインスタンスを生成したら、オブジェクトポインタをxlAppに設定します。Excelの可視(Visible)プロパティをTrueに設定すると、Excelの画面が表示されます。このプロパティは、デフォルトではFalseに設定されています。フォーカスをAccessに移動すると、”Sample2 - Late Binding”のメッセージが表示されていますので、ＯＫボタンをクリックしてExcelを終了させます。ユーザーインタフェースは、Sample1とSample2の違いはありませんが、Sample1のEarly Binding方式を採用すると、Excelのプロパティ/メソッドを記述するとき、候補リストが表示されるので便利です。また、Early Binding方式は、Late Binding方式より高速です。
図6-1-5 Early Binding方式を採用すると、Excelのプロパティ/メソッドを記述するとき、候補リストが表示される
リスト6-1-2 Sample2のソースコード
|
Function Sample2() ' ' AccessからExcelを起動する（Late
Bniding方式） ' Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application") With xlApp .Visible = True MsgBox "Sample2 - Late Binding" .Quit End With Set xlApp = Nothing End Function |
Tips
|
Dim xlApp As Objectのように、アプリケーションのオブジェクト名を指定しない場合、オブジェクトのメソッド/プロパティを記述するとき、候補リストが表示されません。この場合、VBEの表示メニューからオブジェクトブラウザを表示させて、オブジェクトのメソッド/プロパティを参照することができます。 (Fig3_Tip1.tif)
|
● 既に起動されているExcelを再使用するには
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation1を開きます。
３ VBEが起動されてモジュールウィンドウが表示されたら、CH6-1.mdbを開いて、モジュールbasAutomation1のSample3をコピーして貼り付けます。
４ VBEの表示メニューからイミディエイトウィンドウを表示させて、Sample3を実行させます。Excel が起動されて、Excelの画面に切り替わります。
５ Windowsのタスクバーから、Accessのアイコンをクリックしてフォーカスを移動します。”Sample3 – CreateObject”のメッセージが表示されていますので、OKボタンをクリックしてExcel 2000を終了させます。（メッセージのCreateObjectは、Excelのインスタンスを新規に生成したことを意味します。）
６ Windowsのスタートボタンをクリックしたら、プログラムからExcel を起動しておきます。VBEにフォーカスを移動したら、イミディエイトウィンドウからSample3を再起動します。Excelが既に起動されていますので、再使用されます。（WindowsのタスクバーにExcel 2000のアイコンが１個しか存在しないことを確認します。）フォーカスをAccess 2000に移動すると、”Sample3 – GetObject”のメッセージが表示されています。（メッセージのGetObjectは、既に生成されているExcelのインスタンスを取得したことを意味します。）
７ Excelのインスタンスが再使用されることを確認したらbasAutomation1を保存してAccessを終了させます。
Function Sample3()の説明
Sample3では、GetObject()でExcelのインスタンスを取得します。GetObject()は、Excelが既に起動されているときは、Excelのオブジェクトを返します。Excelが起動されていないときは、エラーになります。サンプルではエラーが発生したとき、処理が中断されないようにOn Error Resume Nextで処理を続行するように指示しています。fExcelWasRunningには、Excelが既に起動されているときTrue、起動されていないときFalseが設定されます。
On Error Resume Next
Set xlApp = GetObject(,
"Excel.Application")
fExcelWasRunning = (Err.Number = 0)
Excelが起動されていないときは、CreateObject()で新たにExcelのインスタンスを生成します。
Set xlApp = CreateObject("Excel.Application")
ExcelのオブジェクトがxlAppに設定されたら、Excelの可視(Visible)プロパティをTrueに設定して表示します。MsgBoxには、”Sample3 – GetObject” または “Sample3 – CreateObject”のいずれかのメッセージが表示されます。
With xlApp
.Visible = True
MsgBox "Sample3 - " &
IIf(fExcelWasRunning, "GetObject", "CreateObject")
End With
MsgBoxのOKボタンをクリックすると、fExcelWasRunningのフラグを調べて、インスタンスを新たに生成したときのみ、QuitメソッドでExcelを終了させてオブジェクトを解放します。
If Not fExcelWasRunning Then
xlApp.Quit
Set xlApp = Nothing
End If
リスト6-1-3 Sample3のソースコード
|
Function Sample3() ' Excelが既に起動されているか調べてそのｲﾝｽﾀﾝｽを使用する ' Dim xlApp As Excel.Application Dim fExcelWasRunning As Boolean
On Error Resume Next Set xlApp = GetObject(, "Excel.Application") fExcelWasRunning = (Err.Number = 0) Err.Clear On Error GoTo 0 If Not fExcelWasRunning Then Set xlApp = CreateObject("Excel.Application") If xlApp Is Nothing Then MsgBox "MS Excel 9.0 is not installed on your computer" Exit Function End If End If With xlApp .Visible = True MsgBox "Sample3 - " _ & IIf(fExcelWasRunning, "GetObject", "CreateObject") End With If Not fExcelWasRunning Then xlApp.Quit Set xlApp = Nothing End If End Function |
Tip
|
IsExcelRunning()関数を作成しておけば、Excelが既に起動されているか調べるのに便利です。 IsExcelRunning()関数の使用例： Sub ExcelInstance() Dim xlApp As Excel.Application Dim fExcelRunning as Boolean fExcelRunning = IsExcelRunning() If fExcelRunning Then Set xlApp = Getobject(, “Excel.Application”) Else Set xlApp = Createobject(“Excel.Application”) End If
･･･ Set xlApp = Nothing End If End Sub IsExcelRunning()関数： Public Function IsExcelRunning() As Blean Dim xlApp As Excel.Application On Error Resume Next Set xlApp = GetObject(, “Excel.Application”) IsExcelRunning = (Err.Number = 0) Set xlApp = Nothing Err.Clear End Function |
● Excel を起動/終了させる関数を作成するには
１ Access を起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックして新規作成ボタンをクリックします。
３ モジュールウィンドウが表示されたら、CH6-1.mdbを開いて、モジュールbasAutomation2のOpenExcel(), CloseExcel()関数をコピーして貼り付けます。 次に、モジュール宣言部のOption Explicitの下にPublic属性のメモリ変gxlAppとgfExcelWasRunningを定義します。
Option Compare
Database
Option Explicit
Public gxlApp As
Excel.Application
Public
gfExcelWasRunning As Boolean
４ イミディエイトウィンドウから、OpenExcel()/CloseExcel()関数を実行して動作確認ししたら、モジュールをbasAutomation2の名称で保存してAccessを終了させます。
Function OpenExcel()の説明
この関数は、GetObject()でExcelのインスタンスが既に生成されているか調べて、生成されているときは、そのインスタンスのポインタを返します。インスタンスが生成されていないときは、CreateObject()で新たにインスタンスを生成してそのインスタンスのポインタを返します。
Function CloseExcel()の説明
この関数は、gfExcelWasRunningのフラグを調べて、Excelのインスタンスを新規に成した場合のみ、Excelを終了させます。既に、生成されているインスタンスを再使用したときは、何もしないで戻ります。
リスト6-1-4 OpenExcel/CloseExcelのソースコード
|
Option Compare Database Option Explicit Public gxlApp As Excel.Application Public gfExcelWasRunning As Boolean Public Function OpenExcel() As Excel.Application ' ' Excelを起動する（ｲﾝｽﾀﾝｽを作成） ' On Error Resume Next Set gxlApp = GetObject(, "Excel.Application") gfExcelWasRunning = (Err.Number = 0) If Not gfExcelWasRunning Then Set gxlApp = CreateObject("Excel.Application") If gxlApp Is Nothing Then MsgBox "MS Excel 9.0 is not installed on your computer" Set OpenExcel = Nothing Exit Function End If End If Set OpenExcel = gxlApp End Function Public Function CloseExcel() ' ' Excelを終了させる ' On Error Resume Next If Not gxlApp Is Nothing Then If Not gfExcelWasRunning Then With gxlApp .ActiveWorkbook.Close False .Quit End With Set gxlApp = Nothing End If End If End Function |
● ワークシートのセルに値をセットするには
１ Access を起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation2を開きます。
３ モジュールウィンドウが表示されたらCH6-1.mdbを開いて、モジュールbasAutomation2のSample4をコピーして貼り付けます。
４ イミディエイトウィンドウから、Sample4を実行します。Excelが起動されてワークシート上のセルA1、A2に値がセットされます。Accessにフォーカスを移動すると、”Hello Sample4”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
図6-1-6 セルA1、A2に値をセットした例
５ basAutomation2を保存したら、Access を終了させます。
Function Sample4()の説明
Sample4では、Excelを起動してワークシートのセルに値をセットします。このサンプルでは、Excelを起動/終了させるのにOpenExcel()/CloseExcel()関数を使用しています。WorkbooksオブジェクトのAddメソッドでワークシートを追加したら、Range()プロパティでA1、A2のセルに”A1”、”A2”をセットします。Range()の引数には、セルのアドレスを”A1”,”A2”のように指定します。セルに値をセットするには、Range()の他にCells()を使用する方法もあります。Cells()のときは、引数としてRowIndex（行）, ColumnIndex（カラム）を指定します。例えば、”A2”のセルの場合、Cells(RowIndex:=2,ColumnIndex:=1)またはCells(2,1) のように引数を指定します。また、Range()/Cells()を組み合わせて、Range(Cells(3,1).Address)のように指定する方法もあります。
Cells(3,1)のAddressプロパティでは、”$A$3”のように絶対アドレスが取得できます。Cells(4,1).Address(False,False)のように指定すると、相対アドレスを取得することができます。
Debug.Print.Cells(3,1).Addrss => $A$3
Debug.Print.Cells(3,1).Address(False,False) =>A3
Range()で範囲を指定するには、Range(“B1”,”B3”)のように指定します。Range/Cellsを組み合わせて(“B1”,”B3”)の範囲を選択するには、Range(Cells(1, 2).Address, Cells(3, 2).Address)のように指定します。
Debug.Print
.Range(.Cells(1, 2).Address, .Cells(3, 2).Address).Address =>$B41:$B$3
Tip:
|
Range()プロパティで範囲を指定するとき、Range(“B1:B3”)のようにコロン（：）を使用する方法もあります。 |
リスト6-1-5 Sample4のソースコード
|
Function Sample4() ' ' セルに値をセットする方法(Range,Cells) ' If OpenExcel() Is Nothing Then Exit Function End If
With gxlApp .Workbooks.Add .Cells(2, 1).Value = "A2" '
.Range(.Cells(3, 1).Address).Value = "A3" ' Debug.Print .Cells(3, 1).Address ' Debug.Print .Cells(3,
1).Address(False, False) ' .Range("B1", "B3").Value = "B9" ' Debug.Print .Range(.Cells(1, 2).Address, .Cells(3, 2).Address).Address .Visible = True MsgBox "Hello Sample4" End With
CloseExcel End Function |
● 指定した範囲（レンジ）の縦横の合計を計算するには
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation2を開きます。
３ モジュールウィンドウが表示されたらCH6-1.mdbを開いて、モジュールbasAutomation2のSample5をコピーして貼り付けます。
４ イミディエイトウィンドウから、Sample5を実行します。Excelが起動されてワークシート上のセルB2,C2,B3.C3に数値1,2,3,4がセットされます。更に、範囲(B2:C3)の列合計(B4:C4)と行合計(D2:D4)を計算します。Accessにフォーカスを移動すると、”Hello Sample5”のメッセージが表示されていますので、OKボタンをクリックして、Excelを終了させます。
図6-1-7 範囲(B2:C3)の列合計(B4:C4)と行合計(D2:D4)を計算した例
５ basAutomation2を保存したら、Accessを終了させます。
Function Sample5()の説明
Sample5では、Excelを起動してワークシートに作成した表の列合計/行合計を計算します。WorkbooksオブジェクトのAddメソッドでワークシートを追加したら、Range()プロパティでB2,C2,B3.C3のセルに数値１，２，３，４をセットします。表を作成したら、ActiveSheetのUsedRangeプロパティで表の範囲(B2:C3)をrngDataに設定します。（rngDataに設定された範囲を確認するには、Debug.Print rngData.Addressのようにアドレスを印刷すると便利です。）表の範囲をrngDataに設定するには、UsedRangeの他にCurrentRegionプロパティを使用する方法もあります。
Set rngData =
.Range(“B2”).CurrentRegion
列合計を計算するには、rngColumnTotalに計算式を埋め込む範囲(B4:C4)を設定してから、Formulaプロパティで計算式（SUM関数）を埋め込みます。
rngData.Offset(rngData.Rows.Count).Rows(1)では、列合計の範囲(B4:C4)が設定されます。
"=Sum(" & rngData.Columns(1).Address(False, False) & ")"を実行すると、列合計のセルB4,C4に計算式=SUM(B2:B3), =SUM(C2:C3)が埋め込まれます。
Set rngColumnTotal =
rngData.Offset(rngData.Rows.Count).Rows(1)
rngColumnTotal.Formula =
"=Sum(" & rngData.Columns(1).Address(False, False) & ")"
行合計を計算するには、表の範囲を拡張して列合計の欄も含むように再設定します。
Set rngData2 =
rngData.Offset(0, 0).Resize(rngData.Rows.Count + 1, rngData.Columns.Count)
を実行するとrngData2には、B2:C4の範囲が表として再設定されます。行合計を計算するには、rngRowTotalに計算式を埋め込む範囲(D2:D4)を設定してから、Formulaプロパティで計算式（SUM関数）を埋め込みます。
"=Sum(" & rngData2.Rows(1).Address(False, False) & ")"を実行すると、行合計のセルD2,D3,D4に=SUM(B2:C2),=SUM(B3:C3),=SUM(B4:C4)が埋め込まれます。
Set rngRowTotal =
rngData2.Offset(0, rngData2.Columns.Count).Columns(1)
rngRowTotal.Formula = "=Sum(" &
rngData2.Rows(1).Address(False, False) & ")"
列合計/行合計の計算式を埋め込んだら、可視(Visible)プロパティをTrueに設定してExcelの画面を表示させます。
リスト6-1-6 Sample5のソースコード
|
Function Sample5() ' ' 指定した範囲のデータの行/列の合計を計算するには ' Dim rngRowTotal As Excel.Range Dim rngColumnTotal As Excel.Range If OpenExcel() Is Nothing Then Exit Function End If
With gxlApp .Workbooks.Add .Range("B2").Value = "1" .Range("C2").Value = "2" .Range("B3").Value = "3" .Range("C3").Value = "4" Set rngData = .ActiveSheet.UsedRange ' B2:C3
'
列（縦）の合計を計算する Set rngColumnTotal = _ rngData.Offset(rngData.Rows.Count).Rows(1) ' B4:C4 rngColumnTotal.Formula = "=Sum(" _ & rngData.Columns(1).Address(False, False) _ & ")"
'
行（横）の合計を計算する(列の合計も含む） Set rngData2 = _ rngData.Offset(0, 0).Resize(rngData.Rows.Count + 1, _ rngData.Columns.Count) ' B2:C4 Set rngRowTotal = _ rngData2.Offset(0, rngData2.Columns.Count).Columns(1) ' D2:D4 rngRowTotal.Formula = "=Sum(" _ & rngData2.Rows(1).Address(False, False) _ & ")" .Visible = True MsgBox "Hello Sample5" End With
CloseExcel End Function |
Tip
|
RangeオブジェクトのOffset/Resizeプロパティの使い方が理解できないときは、Debug.Printコマンドでレンジ（範囲）のアドレスを表示させて確認すると便利です。 例えば、rngData.Offset(rngData.Rows.Count).Rows(1)の範囲を確認するには、Debugコマンドで以下のように指定します。 Debug.Print rngData.Offset(rngData.Rows.Count).Rows(1).Address(False, False) 同様に、Resizeされた範囲を確認するにはDebugコマンドで以下のように指定します。 Debug.Print rngData.Offset(0, 0).Resize(rngData.Rows.Count + 1, _ rngData.Columns.Count).Address(False, False) |
● レコードセットをワークシートに出力するには（GetRows）
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックして新規作成ボタンをクリックします。
３ モジュールウィンドウが表示されたらCH6-1.mdbを開いて、モジュールbasAutomation3のSample6をコピーして貼り付けます。
４ イミディエイトウィンドウから、Sample6を実行させます。Excelが実行されてワークシート上にtblAutomationExamplesテーブルのレコードが３件表示されます。フォーカスをAccessに移動すると、”Hello Sample6”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
図6-1-8 GetRowsメソッドを使用してレコードセットの内容を３件ワークシートに出力した例
５ モジュールをbasAutomation3の名称で保存して、Access 2000を終了します。
Function Sample6()の説明
Sample6では、RecordsetオブジェクトのGetRows()メソッドを使用してレコードセットの内容をExcelのワークシート上に出力します。サンプルでは、OpenRecordsetメソッドでtblAutomationExamplesテーブルを開いたら、GetRows()メソッドでレコードセットの内容を配列変数に取り込みます。GetRowsの引数には、レコード数を指定します。サンプルでは、RecordCountプロパティを指定しています。GetRowsで取得したレコードセットは、２次元の配列変数に格納されます。配列の要素１には、フィールド値、要素２にはレコード番号が格納されています。
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL,
dbOpenSnapshot)
avarRec = rs.GetRows(rs.RecordCount)
WorkbooksオブジェクトのAddメソッドでワークシートを追加したら、A1:D1にフィールド名をセットします。
.Range("A1").Value = "No"
.Range("B1").Value =
"Automation Examples"
.Range("C1").Value = "Module
Name"
.Range("D1").Value = "Call
Procedure"
配列変数avarRec()から１番目のレコードをワークシートに出力するには
.Range("A2").Value = avarRec(0,
0) ' SeqNo
.Range("B2").Value = avarRec(1,
0) ' Automation Examples
.Range("C2").Value = avarRec(2,
0) ' Module Name
.Range("D2").Value = avarRec(3,
0) ' Call Procedure
のように記述します。同様の手順で２番目のレコードと３番目のレコードをワークシート上に出力します。３件のレコードを出力したら、UsedRangeプロパティを使用して範囲(A1:D4)をxlSelectionに設定します。最後に、RangeオブジェクトのColumns.AutoFitメソッドを実行してセルの幅を自動調整します。
リスト6-1-７ Sample６のソースコード
|
Function Sample6() ' ' レコードセットをワークシート上に出力するには GetRows()ﾒｿｯﾄﾞの使い方 ' Dim xlSelection As Excel.Range Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String If OpenExcel() Is Nothing Then Exit Function End If
strSQL = "SELECT SeqNo, AutomationExamples, ModuleName, CallProcedure" _ & " FROM tblAutomationExamples" _ & " ORDER BY SeqNo;"
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) avarRec = rs.GetRows(rs.RecordCount)
With gxlApp .Workbooks.Add
'
ﾌｨｰﾙﾄﾞ名をﾜｰｸｼｰﾄに出力 .Range("A1").Value = "No" .Range("B1").Value = "Automation Examples" .Range("C1").Value = "Module Name" .Range("D1").Value = "Call Procedure" ' 1番目のﾚｺｰﾄﾞの内容をﾜｰｸｼｰﾄに出力 .Range("A2").Value = avarRec(0, 0) ' SeqNo .Range("B2").Value = avarRec(1, 0) ' Automation Examples .Range("C2").Value = avarRec(2, 0) ' Module Name .Range("D2").Value = avarRec(3, 0) ' Call Procedure ' 2番目のﾚｺｰﾄﾞの内容をﾜｰｸｼｰﾄに出力 .Range("A3").Value = avarRec(0, 1) .Range("B3").Value = avarRec(1, 1) .Range("C3").Value = avarRec(2, 1) .Range("D3").Value = avarRec(3, 1) ' 3番目のﾚｺｰﾄﾞの内容をﾜｰｸｼｰﾄに出力 .Range("A4").Value = avarRec(0, 2) .Range("B4").Value = avarRec(1, 2) .Range("C4").Value = avarRec(2, 2) .Range("D4").Value = avarRec(3, 2)
Set xlSelection = .ActiveSheet.UsedRange xlSelection.Columns.AutoFit
.Visible = True MsgBox "Hello Sample6" End With
rs.Close db.Close Set rs = Nothing Set db = Nothing
CloseExcel End Function |
● 配列変数の内容をワークシートに出力するには（Array）
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation3を開きます。
３ モジュールウィンドウが表示されたらCH6-1.mdbを開いて、モジュールbasAutomation3のSample７をコピーして貼り付けます。
４ イミディエイトウィンドウから、Sample7を実行させます。Excelが実行されてワークシート上にtblAutomationExamplesテーブルのレコードが全件表示されます。フォーカスをAccessに移動すると、”Hello Sample7”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
図6-1-9 配列変数の内容をセル（A2）を基点にワークシート上に出力した例
５ basAutomation3を保存したら、Access 2000を終了させます。
Function Sample7()の説明
Sample7では、配列変数の内容をExcelのワークシート上直接に出力します。GetRows()メソッドでレコードセットの内容を配列変数avarRecに取り込んだら、表形式に再配置します。
Set rs =
db.OpenRecordset(strSQL, dbOpenSnapshot)
avarRec =
rs.GetRows(rs.RecordCount)
avarRecの内容
Rec1
Rec2 Rec3 Rec4 Rec N
|
Field0 |
(0,0) |
(0,1) |
(0,2) |
(0,3) |
・・・ |
|
Field1 |
(1,0) |
(1,1) |
(1,2) |
(1,3) |
・・・ |
|
Field2 |
(2,0) |
(2,1) |
(2,2) |
(2,3) |
・・・ |
|
Field3 |
(3,0) |
(3,1) |
(3,2) |
(3,3) |
・・・ |
ReDim avarData(rs.RecordCount - 1,
rs.Fields.Count - 1)
For intRec = 0 To UBound(avarRec, 2)
avarData(intRec, 0) = avarRec(0, intRec)
avarData(intRec, 1) = avarRec(1, intRec)
avarData(intRec, 2) = avarRec(2, intRec)
avarData(intRec, 3) = avarRec(3, intRec)
Next
avarDataの内容
Field0 Field1 Field2 Field3
|
Rec0 |
(0,0) |
(0,1) |
(0,2) |
(0,3) |
|
Rec1 |
(1,0) |
(1,1) |
(1,2) |
(1,3) |
|
Rec2 |
(2,0) |
(2,1) |
(2,2) |
(2,3) |
|
Rec3 |
(3,0) |
(3,1) |
(3,2) |
(3,3) |
|
RecN |
|
|
|
|
再配置した配列変数avarDataの内容を、ワークシートのセル（A2）を基点に出力します。RangeオブジェクトのResize()プロパティには、引数としてRowSize(行数)とColumnSize(列数)を指定します。
.Range("A2").Resize(RowSize:=rs.RecordCount,ColumnSize:=rs.Fields.Count).Value =
avarData
配列変数の内容を一括してワークシート上に出力することにより、Sample6のレコード単位に出力する方式と比較して高速化できます。
リスト6-1-8 Sample7のソースコード
|
Function Sample7() ' ' 配列変数の内容をワークシート上に出力するには ' Dim xlSelection As Excel.Range
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim avarRec As Variant Dim intRec As Integer If OpenExcel() Is Nothing Then Exit Function End If
strSQL = "SELECT SeqNo, AutomationExamples, ModuleName, CallProcedure" _ & " FROM tblAutomationExamples" _ & " ORDER BY SeqNo;"
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot) avarRec = rs.GetRows(rs.RecordCount)
ReDim avarData(rs.RecordCount - 1, rs.Fields.Count - 1)
' avarRec()の内容をavarData(ﾌｨｰﾙﾄﾞ値、ﾚｺｰﾄﾞ番号)に置換する For intRec = 0 To UBound(avarRec, 2) avarData(intRec, 0) = avarRec(0, intRec) avarData(intRec, 1) = avarRec(1, intRec) avarData(intRec, 2) = avarRec(2, intRec) avarData(intRec, 3) = avarRec(3, intRec) Next
With gxlApp .Workbooks.Add
' ﾌｨｰﾙﾄﾞ名をﾜｰｸｼｰﾄにｾｯﾄする .Range("A1").Value = "No" .Range("B1").Value = "Automation Examples" .Range("C1").Value = "Module Name" .Range("D1").Value = "Call Procedure"
'
A2のｾﾙを基点に配列変数の内容をﾜｰｸｼｰﾄ上に出力する .Range("A2").Resize( _ RowSize:=rs.RecordCount, _ ColumnSize:=rs.Fields.Count).Value = avarData
Set xlSelection = .ActiveSheet.UsedRange xlSelection.Columns.AutoFit .Visible = True MsgBox "Hello Sample7" End With
rs.Close db.Close Set rs = Nothing Set db = Nothing
CloseExcel End Function |
● レコードセットをワークシートに出力するには（CopyFromRecorset）
１ Accessを起動したら、作業フォルダに作成したCH6-1.mdbを開きます。
２ データベースウィンドウが表示されたら、モジュールタブをクリックしてbasAutomation3を開きます。
３ モジュールウィンドウが表示されたらCH6-1.mdbを開いて、モジュールbasAutomation3のSample8をコピーして貼り付けます。
４ イミディエイトウィンドウから、Sample8を実行させます。Excelが実行されてワークシート上にtblAutomationExamplesテーブルのレコードが全件表示されます。フォーカスをAccessに移動すると、”Hello Sample8”のメッセージが表示されていますので、OKボタンをクリックしてExcelを終了させます。
５ basAutomation3を保存したら、Access を終了させます。
Function Sample8()の説明
Sample8では、レコードセットの内容をExcelのワークシート上直接に出力します。
OpenRecordsetメソッドで、tblAutomationExamplesテーブルを開いたら、RangeオブジェクトのCopyFromRecordsetメソッドを使用して、セル(A2)を基点にレコードセットの内容を全件出力します。
.Range("A2").CopyFromRecordset rs
この方式は、Sample7と同様レコードセットの全件を一括してワークシートに出力しますので、Sample6よりも高速です。
リスト6-1-9 Sample8のソースコード
|
Function Sample8() ' ' ﾚｺｰﾄﾞｾｯﾄの内容をワークシート上に出力するには （CopyFromRecordSetﾒｿｯﾄﾞ） ' Dim xlSelection As Excel.Range
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim avarRec As Variant If OpenExcel() Is Nothing Then Exit Function End If
strSQL = "SELECT SeqNo, AutomationExamples, ModuleName, CallProcedure" _ & " FROM tblAutomationExamples" _ & " ORDER BY SeqNo;"
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With gxlApp .Workbooks.Add
'
ﾌｨｰﾙﾄﾞ名をﾜｰｸｼｰﾄにｾｯﾄする .Range("A1").Value = "No" .Range("B1").Value = "Automation Examples" .Range("C1").Value = "Module Name" .Range("D1").Value = "Call Procedure"
' A2のｾﾙを基点にﾚｺｰﾄﾞｾｯﾄの内容をﾜｰｸｼｰﾄ上に出力する .Range("A2").CopyFromRecordset rs
Set xlSelection = .ActiveSheet.UsedRange xlSelection.Columns.AutoFit
.Visible = True MsgBox "Hello Sample8" End With
rs.Close db.Close Set rs = Nothing Set db = Nothing
CloseExcel End Function |