Métodos para transferir datos a Excel desde Visual Basic

Seleccione idioma Seleccione idioma
Id. de artículo: 247412 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

Resumen

En este artículo se explican numerosos métodos para transferir datos a Microsoft Excel desde una aplicación de Microsoft Visual Basic. Este artículo también presenta las ventajas y las desventajas de cada método para que pueda elegir la solución que más le convenga.

Más información

La solución que se usa con más frecuencia para transferir datos a un libro de Excel es la automatización. La automatización le da la mayor flexibilidad para especificar la ubicación de los datos en un libro además de la capacidad de dar formato al libro y realizar diversas configuraciones en tiempo de ejecución. Con la automatización, puede utilizar varias soluciones para transferir los datos:
  • Transferir los datos celda por celda
  • Transferir los datos de una matriz a un rango de celdas
  • Transferir los datos de un conjunto de registros ADO a un rango de celdas utilizando el método CopyFromRecordset
  • Crear una QueryTable en una hoja de cálculo de Excel que contenga el resultado de una consulta de un origen de datos ODBC u OLEDB
  • Transferir los datos al Portapapeles y, a continuación, pegar el contenido del Portapapeles en una hoja de cálculo de Excel
También hay métodos que puede utilizar para transferir datos a Excel que no requieren necesariamente la automatización. Si ejecuta un servidor de aplicaciones, ésta puede ser una solución apropiada para evitar a los clientes la mayor parte del procesamiento de los datos. Los métodos siguientes se pueden utilizar para transferir los datos sin la automatización:
  • Transferir los datos a un archivo de texto delimitado por comas o tabuladores que Excel puede analizar después en las celdas de una hoja de cálculo
  • Transferir los datos a una hoja de cálculo con ADO
  • Transferir los datos a Excel con Intercambio dinámico de datos (DDE)
Las secciones siguientes proporcionan más detalles de cada una de estas soluciones.

Nota: cuando utiliza Microsoft Office Excel 2007, puede utilizar el nuevo formato de archivo de libro (*.xlsx) de Excel 2007 cuando guarda los libros. Para ello, busque la línea siguiente de código en los ejemplos de código siguientes:
oBook.SaveAs "C:\Book1.xls"
Reemplace este código con la línea siguiente:
oBook.SaveAs "C:\Book1.xlsx"
Además, la base de datos Neptuno (Northwind) no está incluida de forma predeterminada en Office 2007. Sin embargo, puede descargarla desde Microsoft Office Online.

Utilice la automatización para transferir los datos celda por celda

Con la automatización, puede transferir los datos a una hoja de cálculo celda por celda:
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add


   'Add data to cells of the first worksheet in the new workbook
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1").Value = "Last Name"
   oSheet.Range("B1").Value = "First Name"
   oSheet.Range("A1:B1").Font.Bold = True
   oSheet.Range("A2").Value = "Doe"
   oSheet.Range("B2").Value = "John"

   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
Transferir los datos celda por celda puede ser una solución totalmente aceptable si la cantidad de datos es pequeña. Tiene la flexibilidad de colocar los datos en cualquier parte del libro y puede dar formato a las celdas de modo condicional en tiempo de ejecución. Sin embargo, esta solución no se recomienda si tiene una cantidad grande de datos que transferir a un libro de Excel. Cada objeto Range que adquiere en tiempo de ejecución provoca una solicitud a la interfaz, por lo que transferir los datos de esta manera puede ser lento. Además, Microsoft Windows 95 y Windows 98 tienen una limitación de 64 KB en las solicitudes a la interfaz. Si alcanza o sobrepasa este límite de 64 KB en las solicitudes a la interfaz, el servidor de automatización (Excel) podría dejar de responder o podría recibir errores que indican que queda poca memoria. Esta limitación de Windows 95 y Windows 98 se explica en el artículo de Knowledge Base siguiente:
216400 La automatización COM entre procesos puede bloquear la aplicación de cliente en Windows 95 o Windows 98
Una vez más, transferir los datos celda por celda sólo es aceptable con cantidades pequeñas de datos. Si necesita transferir a Excel conjuntos de datos grandes, debería considerar una de las soluciones que se presentan más adelante.

Para obtener más código de ejemplo para la automatización de Excel, consulte el artículo siguiente en Microsoft Knowledge Base:
219151 Cómo automatizar Microsoft Excel desde Visual Basic

Utilizar la automatización para transferir una matriz de datos a un rango en una hoja de cálculo

Una matriz de datos se puede transferir a un rango de varias celdas a la vez:
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object

   'Start a new workbook in Excel
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add

   'Create an array with 3 columns and 100 rows
   Dim DataArray(1 To 100, 1 To 3) As Variant
   Dim r As Integer
   For r = 1 To 100
      DataArray(r, 1) = "ORD" & Format(r, "0000")
      DataArray(r, 2) = Rnd() * 1000
      DataArray(r, 3) = DataArray(r, 2) * 0.7
   Next

   'Add headers to the worksheet on row 1
   Set oSheet = oBook.Worksheets(1)
   oSheet.Range("A1:C1").Value = Array("Order ID", "Amount", "Tax")

   'Transfer the array to the worksheet starting at cell A2
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
Si transfiere los datos con una matriz en lugar de celda por celda, puede conseguir un gran aumento en el rendimiento con una cantidad grande de datos. Considere esta línea del código anterior que transfiere los datos a 300 celdas de la hoja de cálculo:
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
Esta línea representa dos solicitudes a la interfaz (una para el objeto Range que devuelve el método Range y otra para el objeto Range que devuelve el método Resize). Por otro lado, al transferir los datos celda por celda, se requerirían solicitudes para 300 interfaces a los objetos Range. Siempre que sea posible, puede beneficiarse de transferir de forma masiva los datos y reducir el número de solicitudes de la interfaz que realiza.

Usar la automatización para transferir un conjunto de registros ADO a un rango de la hoja de cálculo

Excel 2000 introdujo el método CopyFromRecordset que permite transferir un conjunto de registros ADO (o DAO) a un rango en una hoja de cálculo. El código siguiente muestra cómo podría automatizar Excel 2000, Excel 2002 u Office Excel 2003, y transferir el contenido de la tabla Orders de la base de datos Neptuno de ejemplo con el método CopyFromRecordset.
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Transfer the data to Excel
   oSheet.Range("A1").CopyFromRecordset rs
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit
   
   'Close the connection
   rs.Close
   conn.Close
Nota: si utiliza la versión de Office 2007 de la base de datos Neptuno, debe reemplazar la línea siguiente de código en el ejemplo:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Reemplace esta línea de código con la línea siguiente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
Excel 97 también proporciona un método CopyFromRecordset pero sólo puede utilizarlo con un conjunto de registros DAO. CopyFromRecordset con Excel 97 no admite ADO.

Para obtener más información acerca de cómo usar ADO y el método CopyFromRecordset, vea el siguiente artículo de Microsoft Knowledge Base:
246335 Cómo transferir datos desde un conjunto de registros ADO a Excel con Automatización

Usar la automatización para crear QueryTable en una hoja de cálculo

Un objeto QueryTable representa una tabla generada a partir de los datos devueltos desde un origen de datos externo. Al automatizar Microsoft Excel, puede crear una QueryTable simplemente proporcionando una cadena de conexión a un origen de datos OLEDB u ODBC junto con una cadena de SQL. Excel asume la responsabilidad de generar el conjunto de registros e insertarlo en la hoja de cálculo en la ubicación que se especifique. El uso de QueryTables ofrece varias ventajas sobre el método CopyFromRecordset:
  • Excel controla la creación del conjunto de registros y su colocación en la hoja de cálculo.
  • La consulta se puede guardar con la QueryTable para que se pueda actualizar en un momento posterior con el fin de obtener un conjunto de registros actualizado.
  • Cuando se agrega una nueva QueryTable a la hoja de cálculo, puede especificar que los datos que ya existen en las celdas de la hoja de cálculo se desplacen para alojar los nuevos datos (vea la propiedad RefreshStyle para obtener detalles).
El código siguiente demuestra cómo podría automatizar Excel 2000, Excel 2002 u Office Excel 2003 para crear una nueva QueryTable en una hoja de cálculo de Excel utilizando los datos de la base de datos Neptuno de ejemplo:
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   Set oSheet = oBook.Worksheets(1)
   
   'Create the QueryTable
   Dim sNWind As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   Dim oQryTable As Object
   Set oQryTable = oSheet.QueryTables.Add( _
   "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";", oSheet.Range("A1"), "Select * from Orders")
   oQryTable.RefreshStyle = xlInsertEntireRows
   oQryTable.Refresh False
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Usar el Portapapeles

El Portapapeles de Windows también se puede utilizar como mecanismo para transferir datos a una hoja de cálculo. Para pegar los datos en varias celdas de una hoja de cálculo, puede copiar una cadena en la que las columnas se delimiten mediante caracteres de tabulación y las filas se delimiten con retornos de carro. El código siguiente demuestra el modo en que Visual Basic puede utilizar su objeto Clipboard para transferir datos a Excel:
   'Copy a string to the clipboard
   Dim sData As String
   sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
           & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
           & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
   Clipboard.Clear

   Clipboard.SetText sData
   
   'Create a new workbook in Excel
   Dim oExcel As Object
   Dim oBook As Object
   Set oExcel = CreateObject("Excel.Application")
   Set oBook = oExcel.Workbooks.Add
   

   'Paste the data
   oBook.Worksheets(1).Range("A1").Select
   oBook.Worksheets(1).Paste
   
   'Save the Workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls"
   oExcel.Quit

Crear un archivo de texto delimitado que Excel pueda analizar en las filas y columnas

Excel puede abrir los archivos delimitados por tabuladores o comas, y analizar correctamente los datos de las celdas. Puede aprovechar esta característica cuando desee transferir una cantidad grande de datos a una hoja de cálculo con algo de automatización, si es necesario. Ésta podría ser una solución adecuada para una aplicación cliente-servidor porque el archivo de texto se puede generar en el servidor. Puede abrir a continuación el archivo de texto en el cliente, utilizando la automatización siempre que resulte apropiada.

El código siguiente muestra cómo puede crear un archivo de texto delimitado por comas a partir de un conjunto de registros ADO:
   'Create a Recordset from all the records in the Orders table
   Dim sNWind As String
   Dim conn As New ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim sData As String
   sNWind = _
      "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
   conn.CursorLocation = adUseClient
   Set rs = conn.Execute("Orders", , adCmdTable)
   
   'Save the recordset as a tab-delimited file
   sData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)
   Open "C:\Test.txt" For Output As #1
   Print #1, sData
   Close #1
    
   'Close the connection
   rs.Close
   conn.Close
   
   'Open the new text file in Excel
   Shell "C:\Program Files\Microsoft Office\Office\Excel.exe " & _
      Chr(34) & "C:\Test.txt" & Chr(34), vbMaximizedFocus
Nota: si utiliza la versión de Office 2007 de la base de datos Neptuno, debe reemplazar la línea siguiente de código en el ejemplo de código:
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
Reemplace esta línea de código con la línea siguiente:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
      sNWind & ";"
Si el archivo de texto tiene la extensión .csv, Excel lo abre sin mostrar el Asistente para importar texto y supone automáticamente que el archivo está separado por comas. De igual forma, si el archivo tiene la extensión .txt, Excel lo analiza automáticamente usando tabuladores como delimitadores.

En el ejemplo de código anterior, Excel se inició con la instrucción Shell y el nombre del archivo se utilizó como argumento de la línea de comandos. En el ejemplo anterior no se usó automatización. Sin embargo, si lo desea, podría utilizar una cantidad mínima de automatización para abrir el archivo de texto y guardarlo en el formato de libro de Excel:
   'Create a new instance of Excel
   Dim oExcel As Object
   Dim oBook As Object
   Dim oSheet As Object
   Set oExcel = CreateObject("Excel.Application")
       
   'Open the text file
   Set oBook = oExcel.Workbooks.Open("C:\Test.txt")
   
   'Save as Excel workbook and Quit Excel
   oBook.SaveAs "C:\Book1.xls", xlWorkbookNormal
   oExcel.Quit
Para obtener más información acerca de cómo usar la E/S de archivos desde una aplicación de Visual Basic, vea el siguiente artículo en Microsoft Knowledge Base:
172267 RECEDIT.VBP muestra el archivo E/S en Visual Basic

Transferir los datos a una hoja de cálculo con ADO

Con el Proveedor OLE DB de Microsoft Jet, puede agregar registros a una tabla en un libro de Excel existente. Una "tabla" de Excel es simplemente un rango con un nombre definido. La primera fila del rango debe contener los encabezados (o nombres de campo) y todas las filas subsiguientes contienen los registros. Los pasos siguientes muestran cómo puede crear un libro con una tabla vacía denominada MyTable.
Excel 97, Excel 2000 y Excel 2003
  1. Inicie un nuevo libro en Excel.
  2. Agregue los encabezados siguientes a las celdas A1:B1 de Sheet1:

    A1: FirstName B1: LastName
  3. Dé formato a la celda B1 como alineada a la derecha.
  4. Seleccione A1:B1.
  5. En el menú Insertar, elija Nombres y, a continuación, seleccione Definir. Escriba el nombre MyTable y haga clic en Aceptar.
  6. Guarde el nuevo libro como C:\Book1.xls y cierre Excel.
Para agregar registros a MyTable con ADO, puede usar código similar al siguiente:
   'Create a new connection object for Book1.xls
   Dim conn As New ADODB.Connection
   conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Bill', 'Brown')"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Joe', 'Thomas')"
   conn.Close
Excel 2007
  1. En Excel 2007, inicie un libro nuevo.
  2. Agregue los encabezados siguientes a las celdas A1:B1 de Sheet1:

    A1: FirstName B1: LastName
  3. Dé formato a la celda B1 como alineada a la derecha.
  4. Seleccione A1:B1.
  5. En la Cinta, haga clic en la ficha Fórmulas y, a continuación, haga clic Definir el nombre. Escriba el nombre MyTable y, después, haga clic en Aceptar.
  6. Guarde el nuevo libro como C:\Book1.xlsx y cierre Excel.
Para agregar registros a la tabla MyTable con ADO, utilice código parecido al ejemplo de código siguiente.
   'Create a new connection object for Book1.xls
   Dim conn As New ADODB.Connection
   conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Scott', 'Brown')"
   conn.Execute "Insert into MyTable (FirstName, LastName)" & _
      " values ('Jane', 'Dow')"
   conn.Close
Al agregar los registros a la tabla de esta manera, se mantiene el formato del libro. En el ejemplo anterior, se da formato a los nuevos campos agregados a la columna B con alineación a la derecha. Cada registro que se agrega a una fila toma prestado el formato de la fila que está por encima de él.

Debería tener en cuenta que cuando un registro se agrega a una celda o celdas de la hoja de cálculo, sobrescribe cualquier dato que esté en esas celdas; en otros términos, las filas de la hoja de cálculo no se "empujan hacia abajo" cuando se agregan registros nuevos. Debería tener presente esto al diseñar la disposición de los datos de las hojas de cálculo.

Nota: el método para actualizar los datos de una hoja de cálculo de Excel con ADO o con DAO no funciona en un entorno de Visual Basic para Aplicaciones dentro de Access después de instalar el Service Pack 2 (SP2) de Office 2003 o después de instalar la actualización para Access 2002 que se incluye en el artículo 904018 de Microsoft Knowledge Base. El método funciona bien en el entorno de Visual Basic para Aplicaciones desde otras aplicaciones de Office, como Word, Excel y Outlook. Para obtener más información al respecto, haga clic en los números de artículo siguientes para verlos en Microsoft Knowledge Base:
904953 No puede cambiar, agregar o eliminar datos en las tablas que están vinculadas a un libro de Excel en Office Access 2003 o en Access 2002
904018 Descripción de la actualización para Access 2002: 18 de octubre de 2005

Para obtener información adicional acerca de cómo usar ADO para tener acceso a un libro de Excel, vea los siguientes artículos de Microsoft Knowledge Base:
195951 CÓMO: Utilizar ADO desde ASP para realizar consultas y actualizaciones en Excel

Usar DDE para transferir datos a Excel

COM es una alternativa a la automatización como medio para comunicarse con Excel y transferir datos; sin embargo, con la llegada de la automatización y COM, DDE ya no es el mejor método para comunicarse con otras aplicaciones y sólo se debería utilizar cuando no haya ninguna otra solución disponible.

Para transferir datos a Excel con DDE, puede:
  • Usar el método LinkPoke para escribir datos en un rango de celdas concreto

    O bien
  • Utilice el método LinkExecute para enviar los comandos que Excel vaya a ejecutar.
El ejemplo de código siguiente muestra cómo establecer una conversación DDE con Excel para que pueda escribir datos en las celdas de una hoja de cálculo y ejecutar comandos. Usando este ejemplo, para que una conversación DDE se establezca correctamente en LinkTopic Excel|MyBook.xls, un libro con el nombre MyBook.xls ya debe estar abierto en una instancia en ejecución de Excel.

Nota: cuando utiliza Office Excel 2007, puede utilizar el nuevo formato de archivo *.xlsx para guardar los libros. Asegúrese de que actualiza el nombre de archivo en el ejemplo de código siguiente.

Nota: en este ejemplo, Text1 representa un control TextBox de un formulario de Visual Basic:
   'Initiate a DDE communication with Excel
   Text1.LinkMode = 0
   Text1.LinkTopic = "Excel|MyBook.xls"
   Text1.LinkItem = "R1C1:R2C3"
   Text1.LinkMode = 1
   
   'Poke the text in Text1 to the R1C1:R2C3 in MyBook.xls
   Text1.Text = "one" & vbTab & "two" & vbTab & "three" & vbCr & _
                "four" & vbTab & "five" & vbTab & "six"
   Text1.LinkPoke
   
   'Execute commands to select cell A1 (same as R1C1) and change the font
   'format
   Text1.LinkExecute "[SELECT(""R1C1"")]"
   Text1.LinkExecute "[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]"
   
   'Terminate the DDE communication
   Text1.LinkMode = 0
Al utilizar LinkPoke con Excel, especifica el rango en notación fila-columna (R1C1) para el LinkItem. Si está escribiendo datos en varias celdas, puede utilizar una cadena en la que las columnas se delimiten mediante tabuladores y las filas mediante retornos de carro.

Cuando utilice LinkExecute para pedirle a Excel que ejecute un comando, debe darle el comando en la sintaxis del Lenguaje de macros de Excel (XLM). La documentación de XLM no se incluye con las versiones 97 y posteriores de Excel. Para obtener más información acerca de cómo puede obtener la documentación de XLM, vea el artículo siguiente en Microsoft Knowledge Base:
143466 El archivo Macro97.exe está disponible en los servicios en línea
DDE no se recomienda como solución para comunicarse con Excel. La automatización proporciona la mayor flexibilidad y le da más acceso a las nuevas características que Excel proporciona.

Referencias

Para obtener más información al respecto, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
306022 Cómo transferir datos a un libro de Excel utilizando Visual Basic .NET

Propiedades

Id. de artículo: 247412 - Última revisión: martes, 22 de mayo de 2007 - Versión: 8.0
La información de este artículo se refiere a:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Visual Basic for Applications 5.0
  • Microsoft Visual Basic for Applications 6.0
  • Microsoft Visual Basic 6.0 Edición empresarial
  • Microsoft Visual Basic 6.0 Professional Edition
Palabras clave: 
kbautomation kbdde kbinfo KB247412

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com