閉じたブックから大量のデータをインポートする場合は、ADOと以下のマクロを使用してインポートできます。

閉じたブックの最初のワークシート以外のワークシートからデータを取得する場合は、ユーザー定義の名前付き範囲を参照する必要があります。以下のマクロは次のように使用できます(Excel 2000以降):

GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "A1:B21", ActiveCell, False

GetDataFromClosedWorkbook "C:\FolderName\WorkbookName.xls", "MyDataRange", Range ("B3"), True

Sub GetDataFromClosedWorkbook(SourceFile As String, SourceRange As String, _

TargetRange As Range, IncludeFieldNames As Boolean)

' requires a reference to the Microsoft ActiveX Data Objects library

' if SourceRange is a range reference:

'   this will return data from the first worksheet in SourceFile

' if SourceRange is a defined name reference:

'   this will return data from any worksheet in SourceFile

' SourceRange must include the range headers

'

Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset

Dim dbConnectionString As String

Dim TargetCell As Range, i As Integer

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};" & _

"ReadOnly=1;DBQ=" & SourceFile

Set dbConnection = New ADODB.Connection

On Error GoTo InvalidInput

dbConnection.Open dbConnectionString ' open the database connection

Set rs = dbConnection.Execute("[" & SourceRange & "]")

Set TargetCell = TargetRange.Cells(1, 1)

If IncludeFieldNames Then

For i = 0 To rs.Fields.Count - 1

TargetCell.Offset(0, i).Formula = rs.Fields(i).Name

Next i

Set TargetCell = TargetCell.Offset(1, 0)

End If

TargetCell.CopyFromRecordset rs

rs.Close

dbConnection.Close ' close the database connection

Set TargetCell = Nothing

Set rs = Nothing

Set dbConnection = Nothing

On Error GoTo 0

Exit Sub

InvalidInput:

MsgBox "The source file or source range is invalid!", _

vbExclamation, "Get data from closed workbook"

End Sub

CopyFromRecordSetメソッドを使用しない別のメソッド以下のマクロを使用すると、インポートを実行し、RecordSetから返される結果をより適切に制御できます。

Sub TestReadDataFromWorkbook()

' fills data from a closed workbook in at the active cell

Dim tArray As Variant, r As Long, c As Long

tArray = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")

' without transposing

'    For r = LBound(tArray, 2) To UBound(tArray, 2)

'        For c = LBound(tArray, 1) To UBound(tArray, 1)

'            ActiveCell.Offset(r, c).Formula = tArray(c, r)

'        Next c

'    Next r

' with transposing

tArray = Application.WorksheetFunction.Transpose(tArray)

For r = LBound(tArray, 1) To UBound(tArray, 1)

For c = LBound(tArray, 2) To UBound(tArray, 2)

ActiveCell.Offset(r - 1, c - 1).Formula = tArray(r, c)

Next c

Next r

End Sub

Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As String) As Variant

' requires a reference to the Microsoft ActiveX Data Objects library

' if SourceRange is a range reference:

'   this function can only return data from the first worksheet in SourceFile

' if SourceRange is a defined name reference:

'   this function can return data from any worksheet in SourceFile

' SourceRange must include the range headers

' examples:

' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:A21")

' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "A1:B21")

' varRecordSetData = ReadDataFromWorkbook("C:\FolderName\SourceWbName.xls", "DefinedRangeName")

Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset

Dim dbConnectionString As String

dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & SourceFile

Set dbConnection = New ADODB.Connection

On Error GoTo InvalidInput

dbConnection.Open dbConnectionString ' open the database connection

Set rs = dbConnection.Execute("[" & SourceRange & "]")

On Error GoTo 0

ReadDataFromWorkbook = rs.GetRows ' returns a two dim array with all records in rs

rs.Close

dbConnection.Close ' close the database connection

Set rs = Nothing

Set dbConnection = Nothing

On Error GoTo 0

Exit Function

InvalidInput:

MsgBox "The source file or source range is invalid!", vbExclamation, "Get data from closed workbook"

Set rs = Nothing

Set dbConnection = Nothing

End Function

マクロの例では、VBAプロジェクトがADOオブジェクトライブラリへの参照を追加していることを前提としています。

これを行うには、VBE内から、[ツール]、[参照]メニューを選択し、[Microsoft ActiveXデータオブジェクトx.xオブジェクトライブラリ]を選択します。

データのインポートまたはエクスポートにADOとDAOのどちらかを選択できる場合は、ADOを使用します。