Cómo automatizar Excel desde Visual Basic .NET para rellenar u obtener datos en un intervalo mediante matrices

Artículo
06/20/2023
Se aplica a:

Microsoft Excel

Resumen

En este artículo se muestra cómo automatizar Microsoft Excel y cómo rellenar un intervalo de varias celdas con una matriz de valores. En este artículo también se muestra cómo recuperar un rango de varias celdas como una matriz mediante Automation.

Más información

Para rellenar un rango de varias celdas sin rellenar celdas de una en una, puede establecer la propiedad Value de un objeto Range en una matriz bidimensional. Del mismo modo, se puede recuperar una matriz bidimensional de valores para varias celdas a la vez mediante la propiedad Value. En los pasos siguientes se muestra este proceso para establecer y recuperar datos mediante matrices bidimensionales.

Compilación del cliente de Automation para Microsoft Excel

Inicie Microsoft Visual Studio .NET.
En el menú Archivo, haga clic en Nuevo y, a continuación, haga clic en Proyecto. Seleccione Aplicación windows en los tipos de proyecto de Visual Basic. De forma predeterminada, se crea Form1.
Agregue una referencia a la biblioteca de objetos de Microsoft Excel. Para ello, siga estos pasos:
1. On the Project menu, click Add Reference.
2. En la pestaña COM, busque Biblioteca de objetos de Microsoft Excel y, a continuación, haga clic en Seleccionar.
Nota Microsoft Office 2007 y Microsoft Office 2003 incluyen ensamblados de interoperabilidad primarios (PIA). Microsoft Office XP no incluye los PIA, pero se pueden descargar.
Haga clic en Aceptar en el cuadro de diálogo Agregar referencias para aceptar las selecciones. Si se le pide que genere contenedores para las bibliotecas que seleccionó, haga clic en Sí.
En el menú Ver, seleccione Cuadro de herramientas para mostrar el cuadro de herramientas. Agregue dos botones y una casilla a Form1.
Establezca la propiedad Name de la casilla en FillWithStrings.
Haga doble clic en Button1. Aparece la ventana de código del formulario.
Agregue lo siguiente a la parte superior de Form1.vb:
```
Imports Microsoft.Office.Interop
```

En la ventana de código, reemplace el código siguiente.

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

con:

 '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

Vuelva a la vista de diseño de Form1 y, a continuación, haga doble clic en Botón2.

En la ventana de código, reemplace el código siguiente.

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

End Sub

con:

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

Prueba del cliente de Automation

Presione F5 para compilar y ejecutar el programa de ejemplo.
Haga clic en Botón1. Microsoft Excel se inicia con un nuevo libro y las celdas A1:E5 de la primera hoja de cálculo se rellenan con datos numéricos de una matriz.
Haga clic en Botón2. El programa recupera los datos de las celdas A1:E5 en una nueva matriz y muestra los resultados en un cuadro de mensaje.
Seleccione FillWithStrings y, a continuación, haga clic en Button1 para rellenar las celdas A1:E5 con los datos de cadena.

Referencias

Para obtener información adicional sobre el uso de matrices para establecer y recuperar datos de Excel con versiones anteriores de Visual Studio, haga clic en los números de artículo siguientes para verlo en Microsoft Knowledge Base:

247412 INFO: métodos para transferir datos a Excel desde Visual Basic