Comment automatiser Excel à partir de Visual Basic .NET pour remplir ou obtenir des données dans une plage à l’aide de tableaux

Article
06/20/2023
S’applique à:

Microsoft Excel

Résumé

Cet article montre comment automatiser Microsoft Excel et comment remplir une plage à plusieurs cellules avec un tableau de valeurs. Cet article explique également comment récupérer une plage à plusieurs cellules en tant que tableau à l’aide d’Automation.

Informations supplémentaires

Pour remplir une plage à plusieurs cellules sans remplir les cellules une par une, vous pouvez définir la propriété Value d’un objet Range sur un tableau à deux dimensions. De même, un tableau à deux dimensions de valeurs peut être récupéré pour plusieurs cellules à la fois à l’aide de la propriété Value. Les étapes suivantes illustrent ce processus pour définir et récupérer des données à l’aide de tableaux à deux dimensions.

Créer le client Automation pour Microsoft Excel

Démarrez Microsoft Visual Studio .NET.
Dans le menu Fichier, cliquez sur Nouveau, puis sur Projet. Sélectionnez Application Windows dans les types de projet Visual Basic. Par défaut, Form1 est créé.
Ajoutez une référence à la bibliothèque d’objets Microsoft Excel. Pour cela, procédez comme suit :
1. Dans le menu Projet, cliquez sur Ajouter une référence.
2. Sous l’onglet COM, recherchez la bibliothèque d’objets Microsoft Excel, puis cliquez sur Sélectionner.
Note Microsoft Office 2007 et Microsoft Office 2003 incluent des assemblys d’interopérabilité principale (PIA). Microsoft Office XP n’inclut pas de codes confidentiels, mais ils peuvent être téléchargés.
Cliquez sur OK dans la boîte de dialogue Ajouter des références pour accepter vos sélections. Si vous êtes invité à générer des wrappers pour les bibliothèques que vous avez sélectionnées, cliquez sur Oui.
Dans le menu Affichage, sélectionnez Boîte à outils pour afficher la boîte à outils. Ajoutez deux boutons et une case à cocher à Form1.
Définissez la propriété Name de la case à cocher sur FillWithStrings.
Double-cliquez sur Button1. La fenêtre de code du formulaire s’affiche.
Ajoutez ce qui suit en haut de Form1.vb :
```
Imports Microsoft.Office.Interop
```

Dans la fenêtre de code, remplacez le code suivant

 Private Sub Button1_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles Button1.Click
End Sub

avec :

 'Keep the application object and the workbook object global, so you can  
 'retrieve the data in Button2_Click that was set in Button1_Click.
 Dim objApp As Excel.Application
 Dim objBook As Excel._Workbook

Private Sub Button1_Click(ByVal sender As System.Object, _
   ByVal e As System.EventArgs) Handles Button1.Click
     Dim objBooks As Excel.Workbooks
     Dim objSheets As Excel.Sheets
     Dim objSheet As Excel._Worksheet
     Dim range As Excel.Range

' Create a new instance of Excel and start a new workbook.
     objApp = New Excel.Application()
     objBooks = objApp.Workbooks
     objBook = objBooks.Add
     objSheets = objBook.Worksheets
     objSheet = objSheets(1)

'Get the range where the starting cell has the address
     'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
     range = objSheet.Range("A1", Reflection.Missing.Value)
     range = range.Resize(5, 5)

If (Me.FillWithStrings.Checked = False) Then
         'Create an array.
         Dim saRet(5, 5) As Double

'Fill the array.
         Dim iRow As Long
         Dim iCol As Long
         For iRow = 0 To 5
             For iCol = 0 To 5

'Put a counter in the cell.
                 saRet(iRow, iCol) = iRow * iCol
             Next iCol
         Next iRow

'Set the range value to the array.
         range.Value = saRet

Else
         'Create an array.
         Dim saRet(5, 5) As String

'Fill the array.
         Dim iRow As Long
         Dim iCol As Long
         For iRow = 0 To 5
             For iCol = 0 To 5

'Put the row and column address in the cell.
                 saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()
             Next iCol
         Next iRow

'Set the range value to the array.
         range.Value = saRet
     End If

'Return control of Excel to the user.
     objApp.Visible = True
     objApp.UserControl = True

'Clean up a little.
     range = Nothing
     objSheet = Nothing
     objSheets = Nothing
     objBooks = Nothing
 End Sub

Revenez en mode Création pour Form1, puis double-cliquez sur Button2.

Dans la fenêtre de code, remplacez le code suivant

Private Sub Button2_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button2.Click

End Sub

avec :

Private Sub Button2_Click(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles Button2.Click
    Dim objSheets As Excel.Sheets
    Dim objSheet As Excel._Worksheet
    Dim range As Excel.Range

'Get a reference to the first sheet of the workbook.
    On Error Goto ExcelNotRunning
    objSheets = objBook.Worksheets
    objSheet = objSheets(1)

ExcelNotRunning:
    If (Not (Err.Number = 0)) Then
        MessageBox.Show("Cannot find the Excel workbook.  Try clicking Button1 to " + _
        "create an Excel workbook with data before running Button2.", _
        "Missing Workbook?")

'We cannot automate Excel if we cannot find the data we created, 
        'so leave the subroutine.
        Exit Sub
    End If

'Get a range of data.
    range = objSheet.Range("A1", "E5")

'Retrieve the data from the range.
    Dim saRet(,) As Object
    saRet = range.Value

'Determine the dimensions of the array.
    Dim iRows As Long
    Dim iCols As Long
    iRows = saRet.GetUpperBound(0)
    iCols = saRet.GetUpperBound(1)

'Build a string that contains the data of the array.
    Dim valueString As String
    valueString = "Array Data" + vbCrLf

Dim rowCounter As Long
    Dim colCounter As Long
    For rowCounter = 1 To iRows
        For colCounter = 1 To iCols

'Write the next value into the string.
            valueString = String.Concat(valueString, _
                saRet(rowCounter, colCounter).ToString() + ", ")

Next colCounter

'Write in a new line.
        valueString = String.Concat(valueString, vbCrLf)
    Next rowCounter

'Report the value of the array.
    MessageBox.Show(valueString, "Array Values")

'Clean up a little.
    range = Nothing
    objSheet = Nothing
    objSheets = Nothing
End Sub

Tester le client Automation

Appuyez sur F5 pour générer et exécuter l’exemple de programme.
Cliquez sur Bouton1. Microsoft Excel est démarré avec un nouveau classeur, et les cellules A1:E5 de la première feuille de calcul sont remplies avec des données numériques d’un tableau.
Cliquez sur Button2. Le programme récupère les données des cellules A1:E5 dans un nouveau tableau et affiche les résultats dans une boîte de message.
Sélectionnez FillWithStrings, puis cliquez sur Button1 pour remplir les cellules A1:E5 avec les données de chaîne.

References

Pour plus d’informations sur l’utilisation de tableaux pour définir et récupérer des données Excel avec des versions antérieures de Visual Studio, cliquez sur les numéros d’article ci-dessous pour afficher l’article dans la Base de connaissances Microsoft :

247412 INFO : méthodes de transfert de données vers Excel à partir de Visual Basic