Excel automatiseren van Visual Basic .NET om gegevens in een bereik op te vullen of te verkrijgen met behulp van matrices

Artikel
06/20/2023
Van toepassing op:

Microsoft Excel

Samenvatting

In dit artikel wordt gedemonstreerd hoe u Microsoft Excel kunt automatiseren en hoe u een bereik met meerdere cellen kunt vullen met een matrix met waarden. In dit artikel wordt ook beschreven hoe u een bereik met meerdere cellen als matrix ophaalt met behulp van Automation.

Meer informatie

Als u een bereik met meerdere cellen wilt vullen zonder cellen één voor één in te vullen, kunt u de eigenschap Waarde van een bereikobject instellen op een tweedimensionale matrix. Op dezelfde manier kan een tweedimensionale matrix met waarden voor meerdere cellen tegelijk worden opgehaald met behulp van de eigenschap Waarde. De volgende stappen laten dit proces zien voor het instellen en ophalen van gegevens met behulp van tweedimensionale matrices.

De Automation-client voor Microsoft Excel bouwen

Start Microsoft Visual Studio .NET.
Klik in het menu Bestand op Nieuw en klik vervolgens op Project. Selecteer Windows-toepassing in de Visual Basic-projecttypen. Standaard wordt Form1 gemaakt.
Voeg een verwijzing toe naar de Microsoft Excel-objectbibliotheek. Ga hiervoor als volgt te werk:
1. Klik in het menu Project op Verwijzing toevoegen.
2. Zoek op het tabblad COM de Microsoft Excel-objectbibliotheek en klik op Selecteren.
Opmerking Microsoft Office 2007 en Microsoft Office 2003 bevatten PPA's (Primary Interop Assemblies). Microsoft Office XP bevat geen PIA's, maar ze kunnen wel worden gedownload.
Klik op OK in het dialoogvenster Verwijzingen toevoegen om uw selecties te accepteren. Als u wordt gevraagd om wrappers te genereren voor de bibliotheken die u hebt geselecteerd, klikt u op Ja.
Selecteer Werkset in het menu Weergave om de werkset weer te geven. Voeg twee knoppen en een selectievakje toe aan Formulier1.
Stel de eigenschap Name voor het selectievakje in op FillWithStrings.
Dubbelklik op Knop1. Het codevenster voor het formulier wordt weergegeven.
Voeg het volgende toe boven aan Form1.vb:
```
Imports Microsoft.Office.Interop
```

Vervang in het codevenster de volgende code

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

Met:

 '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

Ga terug naar de ontwerpweergave voor Form1 en dubbelklik op Knop2.

Vervang in het codevenster de volgende code

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

End Sub

Met:

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

De Automation-client testen

Druk op F5 om het voorbeeldprogramma te bouwen en uit te voeren.
Klik op Knop1. Microsoft Excel wordt gestart met een nieuwe werkmap en de cellen A1:E5 van het eerste werkblad worden gevuld met numerieke gegevens uit een matrix.
Klik op Knop2. Het programma haalt de gegevens in de cellen A1:E5 op in een nieuwe matrix en geeft de resultaten weer in een berichtvak.
Selecteer FillWithStrings en klik vervolgens op Knop1 om de cellen A1:E5 te vullen met de tekenreeksgegevens.

Verwijzingen

Voor meer informatie over het gebruik van matrices om Excel-gegevens in te stellen en op te halen met eerdere versies van Visual Studio, klikt u op de onderstaande artikelnummers om het artikel in de Microsoft Knowledge Base weer te geven:

247412 INFO: methoden voor het overdragen van gegevens naar Excel vanuit Visual Basic