Importer des données à partir d’un classeur fermé (ADO) en utilisant VBA dans Microsoft Excel
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.