Métodos para transferir datos a Excel desde Visual Basic
Resumen
En este artículo se describen numerosos métodos para transferir datos a Microsoft Excel desde la aplicación de Microsoft Visual Basic. En este artículo también se presentan las ventajas y desventajas de cada método para que pueda elegir la solución que mejor le convena.
Más información
El enfoque más utilizado para transferir datos a un libro de Excel es Automation. Automation 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 Automation, puede usar varios enfoques para transferir los datos:
- Transferencia de celdas de datos por celda
- Transferencia de datos de una matriz a un rango de celdas
- Transferencia de datos de un conjunto de registros de ADO a un intervalo de celdas mediante el método CopyFromRecordset
- Crear una tabla de consulta en una hoja de cálculo de 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 de cálculo de Excel
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 enfoque para quitar la mayor parte del procesamiento de los datos de los clientes. Los métodos siguientes se pueden usar para transferir los datos sin Automation:
- Transferir los datos a un archivo de texto delimitado por tabulaciones o comas que Excel puede analizar posteriormente en celdas de una hoja de cálculo
- Transferencia de datos a una hoja de cálculo mediante ADO
- Transferencia de datos a Excel mediante intercambio dinámico de datos (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 ejemplos de código siguientes:
oBook.SaveAs "C:\Book1.xls"
Reemplace este código por 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.
Uso de Automation para transferir celdas de datos por celda
Con Automation, puede transferir datos a una hoja de cálculo de 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
La transferencia de celdas de datos por celda puede ser un enfoque perfectamente aceptable si la cantidad de datos es pequeña. Tiene la flexibilidad de colocar los datos en cualquier lugar del libro y puede dar formato condicional a las celdas en tiempo de ejecución. Sin embargo, este enfoque no se recomienda si tiene una gran cantidad de datos para transferir a un libro de Excel. 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 64 000 en las solicitudes de interfaz. Si alcanza o supera este límite de 64 000 en solicitudes de interfaz, es posible que el servidor de Automation (Excel) deje de responder o que reciba errores que indiquen poca memoria.
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 tener en cuenta una de las soluciones presentadas más adelante.
Para obtener más código de ejemplo para Automatizar Excel, consulte Automatización de Microsoft Excel desde Visual Basic.
Uso de 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 celda por celda, puede obtener una enorme ganancia de rendimiento con una gran cantidad de datos. Considere 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 otro lado, 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.
Uso de 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 código siguiente se muestra cómo puede automatizar Excel 2000, Excel 2002 u Office Excel 2003 y transferir el contenido de la tabla Orders en 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 de 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 puede usarlo con un conjunto de registros DAO. CopyFromRecordset con Excel 97 no admite ADO.
Para obtener más información sobre el uso de ADO y el método CopyFromRecordset, vea Transferencia de datos de un conjunto de registros de ADO a Excel con automatización.
Uso de la automatización para crear una tabla de consulta en una hoja de cálculo
Un objeto QueryTable representa una tabla compilada a partir de los datos devueltos desde un origen de datos externo. Al automatizar Microsoft Excel, puede crear una queryTable simplemente proporcionando un cadena de conexión a un origen de datos OLEDB o 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 una nueva tabla de consulta 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 dar cabida a los nuevos datos (consulte la propiedad RefreshStyle para obtener más información).
En el código siguiente se muestra cómo se puede automatizar Excel 2000, Excel 2002 u Office Excel 2003 para crear una nueva QueryTable en una hoja de cálculo de Excel mediante 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
Uso del Portapapeles
El Portapapeles de 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 en la que las columnas están delimitadas por caracteres de tabulación y las filas están delimitadas por retornos de carro. En el código siguiente se muestra 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
Creación de un archivo de texto delimitado que Excel puede analizar en filas y columnas
Excel puede 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. Podría 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 Automation donde sea adecuado.
En el código siguiente se muestra cómo 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 de 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 de .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. De forma similar, si el archivo tiene una extensión de .TXT, Excel analiza 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ó Automation en el ejemplo anterior. Sin embargo, si 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 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
Transferencia de 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 rango 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. En los pasos siguientes se muestra cómo puede crear un libro con una tabla vacía denominada MyTable.
Excel 97, Excel 2000 y Excel 2003
Inicie un nuevo libro en Excel.
Agregue los encabezados siguientes a las celdas A1:B1 de Sheet1:
A1: Nombre B1: LastName
Dé formato a la celda B1 como alineada a la derecha.
Seleccione A1:B1.
En el menú Insertar, elija Nombres y, a continuación, seleccione Definir. Escriba el nombre MyTable y haga clic en Aceptar.
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
En Excel 2007, inicie un nuevo libro.
Agregue los encabezados siguientes a las celdas A1:B1 de Sheet1:
A1: Nombre B1: LastName
Dé formato a la celda B1 como alineada a la derecha.
Seleccione A1:B1.
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 haga clic en Aceptar.
Guarde el nuevo libro como C:\Book1.xlsx y, a continuación, salga de Excel.
Para agregar registros a la tabla MyTable mediante ADO, use código similar 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 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 correcta. Cada registro que se agrega a una fila toma el formato de la fila anterior.
Debe tener en cuenta que cuando se agrega un registro a una celda o celdas de la hoja de cálculo, sobrescribe los datos previamente en esas celdas; en otras palabras, las filas de la hoja de cálculo no se "insertan" cuando se agregan nuevos registros. Debe tener esto en cuenta al diseñar el diseño de 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 el entorno de Visual Basic para 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 el entorno de Visual Basic para aplicaciones de otras aplicaciones de Office, como Word, Excel y Outlook.
Para obtener más información, vea el siguiente artículo:
Para obtener más información sobre el uso de ADO para acceder a un libro de Excel, vea Cómo consultar y actualizar datos de Excel mediante ADO desde ASP.
Uso de 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 Automation y COM, DDE ya no es el método preferido para comunicarse con otras aplicaciones y solo se debe usar cuando no haya ninguna otra solución disponible para usted.
Para transferir datos a Excel mediante DDE, puede usar el método LinkPoke para buscar datos en un intervalo específico de celdas, o bien usar el método LinkExecute para enviar comandos que Excel ejecutará.
En el ejemplo de código siguiente se muestra cómo establecer una conversación DDE con Excel para que pueda buscar datos en las celdas de una hoja de cálculo y ejecutar comandos. Con este ejemplo, para que una conversación DDE se establezca correctamente en LinkTopic Excel|MyBook.xls, ya debe abrirse un libro con el nombre MyBook.xls en una instancia en ejecución de Excel.
Nota:
Cuando se usa Excel 2007, puede usar el nuevo formato de archivo .xlsx para guardar los libros. Asegúrese de actualizar el nombre de archivo en el ejemplo de código siguiente. En este ejemplo, Text1 representa un control Cuadro de texto en 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
Cuando se usa 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 las filas estén delimitadas por retornos de carro.
Cuando se usa LinkExecute para pedir a Excel que lleve a cabo un comando, debe proporcionar a Excel 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.
DDE no es una solución recomendada para comunicarse con Excel. Automation proporciona la mayor flexibilidad y le proporciona más acceso a las nuevas características que Excel tiene para ofrecer.
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de