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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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: 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
(http://support.microsoft.com/kb/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.QuitUsar 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.QuitCrear 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: 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
(http://support.microsoft.com/kb/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
- Inicie un nuevo libro en Excel.
- Agregue los encabezados siguientes a las celdas A1:B1 de
Sheet1:
A1: FirstName 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 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.CloseExcel 2007
- En Excel 2007, inicie un libro nuevo.
- Agregue los encabezados siguientes a las celdas A1:B1 de
Sheet1:
A1: FirstName B1: LastName - Dé formato a la celda B1 como alineada a la
derecha.
- Seleccione A1:B1.
- 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.
- 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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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.
Volver al principio
