Si vous souhaitez importer beaucoup de données à partir d’un classeur fermé, vous pouvez le faire avec ADO et la macro ci-dessous.

Si vous souhaitez récupérer des données à partir d’une autre feuille de calcul que la première feuille de calcul du classeur fermé, vous devez faire référence à une plage nommée définie par l’utilisateur. La macro ci-dessous peut être utilisée comme ceci (dans Excel 2000 ou version ultérieure):

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

Une autre méthode qui n’utilise pas la méthode CopyFromRecordSet Avec la macro ci-dessous, vous pouvez effectuer l’importation et avoir un meilleur contrôle sur les résultats renvoyés par 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

L’exemple de macro suppose que votre projet VBA a ajouté une référence à la bibliothèque d’objets ADO.

Vous pouvez le faire à partir du VBE en sélectionnant le menu Outils, Références et en sélectionnant Bibliothèque d’objets Microsoft ActiveX Data Objects x.x.

Utilisez ADO si vous pouvez choisir entre ADO et DAO pour l’importation ou l’exportation de données.