● ファイルを開かずに特定のデータを取得する。
● 相手のファイルを開かずに、こちらのデータを複数行書き込む
Option Explicit ' 書き忘れたら動作しない。
------------------------------------------------
Sub 相手のファイルを開かずにこちらのデータを複数行書き込む()
' このプログラムは、Excel 2007 以前のデータベースファイルを使用する場合である。
' 拡張子は、 xls になっている。
Dim Cnn As ADODB.Connection
Dim Rec As ADODB.Recordset
Dim i As Integer
Set Cnn = New ADODB.Connection
Cnn.Provider = "Microsoft.Jet.OLEDB.4.0" ' ここが違うところである。
'Excel97,2000のブックはExcel8.0で設定する
Cnn.Properties("Extended Properties") = "Excel 8.0" '
ここが違うところである。
Cnn.Open "E" & ":" & "\" & "TestData.xls"
Set Rec = New ADODB.Recordset
Set Rec.ActiveConnection = Cnn
Rec.Open "[Sheet2$]", , adOpenKeyset, adLockPessimistic, adCmdTable
'データを追加する
For i = 2 To 4
'新しいレコードを追加する
Rec.AddNew
Rec!製番 = Range("A" & i)
Rec!日付 = Range("B" & i)
'カレント行の内容を更新する
Rec.Update
Next
Rec.Close
Cnn.Close
Set Rec = Nothing
Set Cnn = Nothing
End Sub
Eドライブのファイルに、データが追加された。
このファイルから、データベースを開かないで
このデータを追加する。
Eドライブにこのようなデータベースがある。
そのデータの最下行に、こちらのデータを
追加して書き込む。
Option Explicit
------------------------------------------------
Sub 実績を取得する()
'Excel 2007バージョンのファイルの取得
'Microsoft ActiveX Data Objects 2.1 Library 以上 2.8は不可
'ツール - 参照設定にてチェックを付ける。
Dim Cnn As ADODB.Connection
Dim Rec As ADODB.Recordset
Dim intMon As Integer
Worksheets("Sheet2").Select
Range("B3:O50").ClearContents
Set Cnn = New ADODB.Connection
Cnn.Provider = "Microsoft.ACE.OLEDB.12.0"
'Excel97,2000のブックはExcel8.0で設定する
Cnn.Properties("Extended Properties") = "Excel 12.0"
Cnn.Open "E" & ":" & "\" & "Book2.xlsm"
Set Rec = New ADODB.Recordset
Set Rec.ActiveConnection = Cnn
Rec.Open "[集計$F21:F22]", , adOpenKeyset, adLockPessimistic, adCmdTable
' BooK2.xlsm の集計シートの F22 のセルに、合計値が Sum 関数で記載されている
' ものを取得する。
' ここでは、[集計 $ F21:F22]と記入しないで、[集計 $ F22]と記入すればエラーになるし、
' [集計 $ F22:F22] と記入すれば、データが取得されない。
' 取得するデータの一つ上のセルから、取得するセルまでを範囲としなければならない。
Worksheets("Sheet2").Range("B4").CopyFromRecordset
Rec
Rec.Close
Cnn.Close
Set Rec = Nothing
Set Cnn = Nothing
End Sub
ファイルを開かずに取得したデータである。
上図のようなデータがEドライブにあるのを、ファイルを開かずに金額の合計値を取得する。
'Excel 2007バージョンのファイルの取得
'Microsoft ActiveX Data Objects 2.1 Library 以上 2.8は不可
'ツール - 参照設定にてチェックを付ける。
参照可能なライブラリファイルが開くので、Microsoft ActiveX Data Objects ―Library が上部にない場合は、
スクロールをクリックして下部のM範囲を探せば見つかるので、Objects 2.1以上2.8以下にチェックを付けると、
上部に移動される。[OK]をクリックして閉じる。
尚、一度チェックを付けるとExcelを終了しても記憶される。
Dim Cnn As ADODB.Connection ' この変数定義はこのまま使用する。
Dim Rec As ADODB.Recordset ' この変数定義はこのまま使用する。
Dim intMon As Integer ' この変数名は変更可能です。
Worksheets("当月仕入").Select ' 操作ファイルのシート
Range("B4:H100").ClearContents ' セル選択のデータ消去
Set Cnn = New ADODB.Connection ' このままの文法で使用する。
'Excel97,2000のブックはExcel 8.0で設定する
'Excel2007以上のブックはExcel 12.0で設定する
Cnn.Provider = "Microsoft.ACE.OLEDB.12.0" ' バージョンで変わる
Cnn.Properties("Extended Properties") = "Excel 12.0" ' バージョンで変わる
Cnn.Open "E" & ":" & "\" & "当月仕入データファイル.xlsm" '
開かないで参照するファイル
Set Rec = New ADODB.Recordset ' このままの文法で使用する。
Set Rec.ActiveConnection = Cnn ' このままの文法で使用する。
Rec.Open "[当月仕入$B2:H100]", , adOpenKeyset, adLockPessimistic, adCmdTable
' 開かないで参照するファイルのシート名と取得範囲
Worksheets("当月仕入").Range("B2").CopyFromRecordset Rec
' 取得した範囲を転記するシート名
Rec.Close ' Recを閉じている
Cnn.Close
Set Rec = Nothing
Set Cnn = Nothing
Option Explicit ' 先頭に書き忘れてはいけない。
----------------------------------------------------------------------
VBAで[ツール]-[参照設定]の順のクリックする。
● ファイルを開かずに、データを取得するような操作の設定方法
上図がデータを取得したものである。
下がプログラムである。
Eドライブに、上図のようなデータベースがある。ファイル名は「当月仕入データファイル.xlsm」である。
下のファイルは、「当月仕入データファイル.xlsm」を開かずに、必要なデータを取得する。
相手のファイルを開かないと、[開く]-[閉じる]の動作が無いので実行が速い。
容量の大きなデータベースでは、顕著に時間短縮が分かる。
● 相手のファイルを開かずにデータを取得する。
基本的なマクロの習得
相手のファイルを開かないでデータの操作
実際に使っているシステムの内容で解説
EXCEL VBA 講座
