Métodos para transferir datos a Excel desde Visual Basic

Nota

Office 365 ProPlus pasa a llamarse Microsoft 365 Apps para empresas. Para obtener más información sobre este cambio, lea esta publicación de blog.

Resumen

En este artículo se de abordan numerosos métodos para transferir datos a Microsoft Excel desde la aplicación Visual Basic Microsoft. En este artículo también se presentan las ventajas y las desventajas de cada método para que pueda elegir la solución que mejor se adapte a usted.

Más información

El enfoque más usado para transferir datos a un libro Excel es la automatización. La automatización proporciona la mayor flexibilidad para especificar la ubicación de los datos en el libro, así como la capacidad de dar formato al libro y realizar varias configuraciones en tiempo de ejecución. Con automatización, puede usar varios métodos para transferir los datos:

  • Transferir celda de datos por celda
  • Transferir datos de una matriz a un rango de celdas
  • Transferir datos de un conjunto de registros de ADO a un rango de celdas mediante el método CopyFromRecordset
  • Crear un objeto QueryTable en una hoja Excel que contenga el resultado de una consulta en un origen de datos ODBC o OLEDB
  • Transferir datos al portapapeles y, a continuación, pegar el contenido del portapapeles en una hoja Excel de trabajo

También hay métodos que puede usar para transferir datos a Excel que no requieren necesariamente automatización. Si ejecuta un servidor de aplicaciones, este puede ser un buen método para quitar la mayor parte del procesamiento de los datos de los clientes. Los siguientes métodos se pueden usar para transferir los datos sin automatización:

  • Transferir los datos a un archivo de texto delimitado por comas o tabulación que Excel analizar posteriormente en celdas de una hoja de cálculo
  • Transferir los datos a una hoja de cálculo mediante ADO
  • Transferir datos a Excel con datos dinámicos Exchange (DDE)

Las secciones siguientes proporcionan más detalles sobre cada una de estas soluciones.

Nota Al usar Microsoft Office Excel 2007, puede usar el nuevo formato de archivo libro de Excel 2007 (*.xlsx) al guardar los libros. Para ello, busque la siguiente línea de código en los siguientes ejemplos de código:

oBook.SaveAs "C:\Book1.xls"

Reemplace este código por la siguiente línea de código:

oBook.SaveAs "C:\Book1.xlsx"

Además, la base de datos Northwind no se incluye en Office 2007 de forma predeterminada. Sin embargo, puede descargar la base de datos Northwind desde Microsoft Office Online.

Usar automatización para transferir celdas de datos por celda

Con automatización, puede transferir datos a una hoja de cálculo una celda 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
      
'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 celda de datos por celda puede ser un enfoque perfectamente aceptable si la cantidad de datos es pequeña. Tiene la flexibilidad de colocar datos en cualquier lugar del libro y puede dar formato a las celdas condicionalmente en tiempo de ejecución. Sin embargo, este enfoque no se recomienda si tiene una gran cantidad de datos para transferir a un Excel libro. Cada objeto Range que adquiera en tiempo de ejecución da como resultado una solicitud de interfaz para que la transferencia de datos de esta manera pueda ser lenta. Además, Microsoft Windows 95 y Windows 98 tienen una limitación de 64K en las solicitudes de interfaz. Si alcanza o supera este límite de 64 k en las solicitudes de interfaz, el servidor de automatización (Excel) puede dejar de responder o puede recibir errores que indican una memoria baja.

Una vez más, la transferencia de celdas de datos por celda solo es aceptable para pequeñas cantidades de datos. Si necesita transferir grandes conjuntos de datos a Excel, debe considerar una de las soluciones que se presentan más adelante.

Para obtener más código de ejemplo para automatizar Excel, vea How to automate Microsoft Excel from Visual Basic.

Usar la automatización para transferir una matriz de datos a un rango de 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 mediante una matriz en lugar de una celda por celda, puede obtener una enorme ganancia de rendimiento con una gran cantidad de datos. Tenga en cuenta esta línea del código anterior que transfiere datos a 300 celdas de la hoja de cálculo:

   oSheet.Range("A2").Resize(100, 3).Value = DataArray

Esta línea representa dos solicitudes de 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 otra parte, la transferencia de la celda de datos por celda requeriría solicitudes para 300 interfaces a objetos Range. Siempre que sea posible, puede beneficiarse de la transferencia masiva de datos y la reducción del número de solicitudes de interfaz que realice.

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

Excel 2000 introdujo el método CopyFromRecordset que permite transferir un conjunto de registros de ADO (o DAO) a un rango de una hoja de cálculo. En el siguiente código se muestra cómo automatizar Excel 2000, Excel 2002 o Office Excel 2003 y transferir el contenido de la tabla Pedidos de la base de datos de ejemplo Northwind mediante 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 usa la versión Office 2007 de la base de datos Northwind, debe reemplazar la siguiente línea 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 por la siguiente línea de código:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"

Excel 97 también proporciona un método CopyFromRecordset, pero solo se puede usar con un conjunto de registros DAO. CopyFromRecordset con Excel 97 no admite ADO.

Para obtener más información acerca del uso de ADO y el método CopyFromRecordset, vea How to transfer data from an ADO recordset to Excel with automation.

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

Un objeto QueryTable representa una tabla creada a partir de datos devueltos desde un origen de datos externo. Al automatizar Microsoft Excel, puede crear un objeto QueryTable simplemente proporcionando una cadena de conexión a un oledb o un origen de datos ODBC junto con una cadena SQL. Excel asume la responsabilidad de generar el conjunto de registros e insertarlo en la hoja de cálculo en la ubicación especificada. El uso de QueryTables ofrece varias ventajas sobre el método CopyFromRecordset:

  • Excel controla la creación del conjunto de registros y su ubicación en la hoja de cálculo.
  • La consulta se puede guardar con querytable para que se pueda actualizar más adelante para obtener un conjunto de registros actualizado.
  • Cuando se agrega un nuevo objeto 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 cambien para dar cabida a los nuevos datos (vea la propiedad RefreshStyle para obtener más información).

El siguiente código muestra cómo automatizar Excel 2000, Excel 2002 o Office Excel 2003 para crear una nueva tabla de consulta en una hoja de cálculo de Excel con datos de la base de datos de ejemplo Northwind:

'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 Windows también se puede usar como mecanismo para transferir datos a una hoja de cálculo. Para pegar datos en varias celdas de una hoja de cálculo, puede copiar una cadena donde las columnas están delimitadas por caracteres de tabulación y filas delimitadas por retornos de carro. El siguiente código ilustra cómo Visual Basic puede usar 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 analizar en filas y columnas

Excel abrir archivos delimitados por tabulaciones o comas y analizar correctamente los datos en celdas. Puede aprovechar esta característica cuando desee transferir una gran cantidad de datos a una hoja de cálculo mientras usa poca automatización, si la hay. Este puede ser un buen enfoque para una aplicación cliente-servidor porque el archivo de texto se puede generar en el lado servidor. A continuación, puede abrir el archivo de texto en el cliente, mediante automatización cuando sea apropiado.

El siguiente código ilustra cómo puede crear un archivo de texto delimitado por comas a partir de un conjunto de registros de 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 usa la versión Office 2007 de la base de datos Northwind, debe reemplazar la siguiente línea 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 por la siguiente línea de código:

conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

Si el archivo de texto tiene una extensión .CSV, Excel abre el archivo sin mostrar el Asistente para importación de texto y supone automáticamente que el archivo está delimitado por comas. Del mismo modo, si el archivo tiene una extensión .TXT, Excel analizar automáticamente el archivo mediante delimitadores de tabulación.

En el ejemplo de código anterior, Excel se inició con la instrucción Shell y el nombre del archivo se usó como argumento de línea de comandos. No se usó ninguna automatización en el ejemplo anterior. Sin embargo, si así lo desea, podría usar una cantidad mínima de automatización para abrir el archivo de texto y guardarlo en el formato de libro Excel texto:

'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

Transferir datos a una hoja de cálculo mediante ADO

Con el proveedor OLE DB de Microsoft Jet, puede agregar registros a una tabla de un libro de Excel existente. Una "tabla" en Excel es simplemente un intervalo con un nombre definido. La primera fila del intervalo debe contener los encabezados (o nombres de campo) y todas las filas posteriores contienen los registros. Los pasos siguientes ilustran 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 siguientes encabezados a las celdas A1:B1 de Sheet1:

    A1: FirstName B1: LastName

  3. Formatee 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 salga de Excel.

Para agregar registros a MyTable mediante 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 nuevo libro.

  2. Agregue los siguientes encabezados a las celdas A1:B1 de Sheet1:

    A1: FirstName B1: LastName

  3. Formatee la celda B1 como alineada a la derecha.

  4. Seleccione A1:B1.

  5. En la cinta de opciones, haga clic en la pestaña Fórmulas y, a continuación, haga clic en Definir nombre. Escriba el nombre MyTable y, a continuación, haga clic en Aceptar.

  6. Guarde el nuevo libro como C:\Book1.xlsx y, a continuación, salga Excel.

Para agregar registros a la tabla MyTable mediante ADO, use código similar al siguiente ejemplo de código.

'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 registros a la tabla de esta manera, se mantiene el formato del libro. En el ejemplo anterior, los nuevos campos agregados a la columna B tienen el formato de alineación derecha. Cada registro que se agrega a una fila toma prestado el formato de la fila que hay encima.

Debe tener en cuenta que cuando se agrega un registro a una celda o celda de la hoja de cálculo, sobrescribe los datos que haya anteriormente en esas celdas; en otras palabras, las filas de la hoja de cálculo no se "presionan hacia abajo" cuando se agregan nuevos registros. Debe tener esto en cuenta al diseñar el diseño de los datos en las hojas de cálculo.

Nota

El método para actualizar datos en una hoja de cálculo de Excel mediante ADO o mediante DAO no funciona en Visual Basic para entorno de aplicaciones en Access después de instalar Office 2003 Service Pack 2 (SP2) o después de instalar la actualización de Access 2002 que se incluye en el artículo de Microsoft Knowledge Base 904018. El método funciona bien en Visual Basic entorno application desde otras aplicaciones Office, como Word, Excel y Outlook.

Para obtener más información, vea el siguiente artículo:

No puede cambiar, agregar ni eliminar datos en tablas vinculadas a un libro de Excel en Office Access 2003 ni en Access 2002

Para obtener más información acerca del uso de ADO para obtener acceso a un libro Excel, vea How To Query and Update Excel Data Using ADO From ASP.

Use DDE para transferir datos a Excel

DDE 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 método preferido para comunicarse con otras aplicaciones y solo debe usarse cuando no hay otra solución disponible.

Para transferir datos a Excel mediante DDE, puede usar el método LinkPoke para enviar datos a un intervalo específico de celdas, o bien usar el método LinkExecute para enviar comandos que Excel ejecutar.

En el siguiente ejemplo de código se muestra cómo establecer una conversación de DDE con Excel para que pueda meter datos en las celdas de una hoja de cálculo y ejecutar comandos. Con este ejemplo, para que una conversación de DDE se establezca correctamente en el Excel|MyBook.xls LinkTopic, un libro con el nombre MyBook.xls debe abrirse ya en una instancia en ejecución de Excel.

Nota

Cuando use Excel 2007, puede usar el nuevo formato .xlsx archivo para guardar los libros. Asegúrese de actualizar el nombre de archivo en el siguiente ejemplo de código. En este ejemplo, Text1 representa un control Cuadro de texto en un Visual Basic formulario:

'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 usar LinkPoke con Excel, se especifica el intervalo en la notación de columna de fila (R1C1) para linkitem. Si va a insertar datos en varias celdas, puede usar una cadena en la que las columnas estén delimitadas por pestañas y filas delimitadas por retornos de carro.

Cuando use LinkExecute para pedir a Excel que realice un comando, debe proporcionar Excel el comando en la sintaxis del lenguaje de macros de Excel (XLM). La documentación de XLM no se incluye Excel versiones 97 y posteriores.
DDE no es una solución recomendada para comunicarse con Excel. La automatización proporciona la mayor flexibilidad y le ofrece más acceso a las nuevas características que Excel ofrecer.