Iniciar Sess�o

Select the product you need help with

M�todos para transferir dados do Visual Basic para o Excel

ID do artigo: 247412 - Exibir os produtos aos quais esse artigo se aplica.

Sum�rio

Este artigo descreve os v�rios m�todos para transferir os dados do aplicativo Microsoft Visual Basic para o Microsoft Excel. O artigo tamb�m apresenta as vantagens e as desvantagens de cada um dos m�todos para que voc� possa selecionar a solu��o que mais bem se adapte � sua situa��o.

Voltar para o in�cio | Submeter coment�rios

Mais Informa��es

O m�todo mais usado para transferir dados para uma pasta de trabalho do Excel � Automa��o. A automa��o lhe d� o que h� de melhor em termos de flexibilidade para a especifica��o do local dos dados na pasta de trabalho, bem como a capacidade de formatar a pasta de trabalho e fazer v�rias configura��es no momento da execu��o. Com ela, � poss�vel usar v�rios m�todos para transferir os dados:

Transferir os dados c�lula por c�lula
Transferir dados de uma matriz para um intervalo de c�lulas
Transferir dados de um conjunto de registros ADO para um intervalo de c�lulas, usando o m�todo CopyFromRecordset
Criar uma QueryTable em uma planilha do Excel que cont�m o resultado de uma consulta em uma origem de dados ODBC ou OLEDB
Transferir dados para a �rea de transfer�ncia e colar o conte�do em uma planilha do Excel

Tamb�m h� m�todos que podem ser usados na transfer�ncia de dados para o Excel que n�o necessariamente exigem a automa��o. Se estiver executando um aplicativo no lado do servidor, este pode ser um bom m�todo para retirar grande parte do processamento de dados dos clientes. Os seguintes m�todos podem ser usados para transferir os dados sem a Automa��o:

Transferir os dados para um arquivo de texto separado por v�rgulas ou tabula��es que possa ser usado pelo Excel para analisar as c�lulas de uma planilha
Transferir dados para uma planilha usando o ADO
Transferir dados para o Excel, usando DDE (Dynamic Data Exchange)

As se��es a seguir fornecem mais detalhes sobre cada uma dessas solu��es.

Usar a automa��o para transferir os dados c�lula por c�lula

Com a Automa��o, � poss�vel transferir os dados para uma planilha uma c�lula por vez, da seguinte forma:

   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

A transfer�ncia de dados c�lula por c�lula pode ser um m�todo perfeitamente aceit�vel caso haja poucos dados. Voc� tem a flexibilidade de colocar os dados em qualquer lugar da pasta de trabalho e formatar as c�lulas no momento da execu��o. No entanto, esse m�todo n�o � recomend�vel caso haja muitos dados a serem transferidos para uma pasta de trabalho do Excel. Como cada objeto Range adquirido no momento da execu��o resulta em uma solicita��o de interface, a transfer�ncia de dados dessa maneira pode ser muito lenta. Al�m disso, o Microsoft Windows 95 e o Windows 98 possuem uma limita��o de 64K em solicita��es de interface. Caso voc� atinja ou exceda esse limite de 64k em solicita��es de interface, o servidor de automa��es (Excel) pode parar de responder ou voc� pode receber erros, indicando pouca mem�ria. Essa limita��o do Windows 95 e do Windows 98 � analisada no seguinte artigo da Base de Dados de Conhecimento da Microsoft:

216400

(http://support.microsoft.com/kb/216400/ )

PRB: O processo cruzado de Automa��o COM � capaz de travar o aplicativo cliente no Win95/98

Mais uma vez, a transfer�ncia de dados c�lula por c�lula s� � aceit�vel para pequenas quantidades de dados. Se voc� precisar transferir conjuntos de dados muito grandes para o Excel, ser� necess�rio avaliar as solu��es apresentadas posteriormente.

Para obter um c�digo de exemplo para a automa��o do Excel, consulte o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

219151

(http://support.microsoft.com/kb/219151/ )

Como automatizar o Microsoft Excel a partir do Visual Basic

Usar a automa��o para transferir dados de uma matriz para um intervalo de c�lulas em uma planilha

� poss�vel transferir uma matriz de dados para um intervalo de v�rias c�lulas de uma s� 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

Caso transfira os dados com uma matriz, e n�o c�lula por c�lula, � poss�vel notar um incr�vel ganho de desempenho em caso de muitos dados. Considere esta linha do c�digo acima, que transfere dados para 300 c�lulas na planilha:

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

A linha representa duas solicita��es de interface (uma para o objeto Range retornado pelo m�todo Range e outra para o objeto Range retornado pelo m�todo Resize). Por outro lado, a transfer�ncia dos dados c�lula por c�lula exigiria solicita��es para 300 interfaces para objetos Range. Sempre que poss�vel, voc� pode transferir dados em massa e reduzir o n�mero de solicita��es de interface feitas.

Usar a automa��o para transferir um conjunto de dados ADO para um intervalo de planilha

O Excel 2000 introduziu o m�todo CopyFromRecordset que lhe permite transferir um conjunto de registros ADO (ou DAO) para um intervalo em uma planilha. O seguinte c�digo ilustra de que forma voc� poderia automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 e transferir o conte�do da tabela Orders no banco de dados de exemplo Northwind com o 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

O Excel 97 tamb�m fornece um m�todo CopyFromRecordset, embora s� seja poss�vel us�-lo com um conjunto de registros DAO. CopyFromRecordset com Excel 97 n�o oferece suporte a ADO.

Para obter informa��es adicionais sobre como usar o ADO e o m�todo CopyFromRecordset, consulte o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

246335

(http://support.microsoft.com/kb/246335/ )

Como transferir dados de um conjunto de registros ADO para o Excel com a automa��o

Usar a automa��o para criar um objeto QueryTable em uma planilha

Um objeto QueryTable representa uma tabela criada a partir dos dados retornados de uma origem de dados externa. Durante a automa��o do Microsoft Excel, voc� pode criar uma QueryTable, apenas fornecendo uma seq��ncia de conex�o a uma origem de dados OLEDB ou ODBC e uma seq��ncia SQL. O Excel assume a responsabilidade pela gera��o do conjunto de registros e pela inser��o na planilha no local especificado. O uso de QueryTables apresenta as seguintes vantagens em rela��o ao m�todo CopyFromRecordset:

O Excel processa a cria��o do conjunto de registros e a coloca��o na planilha.
A consulta pode ser salva com o objeto QueryTable para que seja posteriormente atualizada a fim de obter um conjunto de registros atualizado.
Quando um novo QueryTable � adicionado � planilha, � poss�vel especificar se os dados j� presentes nas c�lulas da planilha podem ser alterados de acordo com os novos dados (consulte a propriedade RefreshStyle para obter mais detalhes).

O seguinte c�digo demonstra como automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 para criar um novo QueryTable em uma planilha do Excel com os dados do banco de dados de exemplo 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 da �rea de transfer�ncia

Tamb�m � poss�vel usar a �rea de transfer�ncia do Windows como um mecanismo para transferir os dados para uma planilha. Para colar dados em v�rias c�lulas de uma planilha, voc� pode copiar uma seq��ncia em que as colunas s�o separadas por tabula��es e as linhas, por retornos de carro. O seguinte c�digo ilustra de que forma o Visual Basic pode usar o objeto Clipboard para transferir dados para o 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

Criar um arquivo de texto separado que pode ser analisado pelo Excel em linhas e colunas

O Excel � capaz de abrir arquivos separados por tabula��es ou v�rgulas e analisar corretamente os dados em c�lulas. Voc� pode usar esse recurso quando for preciso transferir uma grande quantidade de dados para uma planilha com pouca, ou nenhuma automa��o. Pode se tratar de um bom m�todo para um aplicativo do lado do cliente, j� que o arquivo de texto pode ser gerado do lado do servidor. Em seguida, � poss�vel abrir o arquivo de texto no cliente, usando a Automa��o quando for preciso.

O seguinte c�digo ilustra como � poss�vel criar um arquivo separado por v�rgulas a partir de um 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

Se o arquivo tiver uma extens�o .CSV, o Excel o abrir� sem exibir o Assistente para importa��o de texto, pressupondo automaticamente se tratar de um arquivo separado por v�rgulas. Da mesma forma, se o arquivo tiver uma extens�o .TXT, o Excel o analisar� automaticamente, usando os separadores de tabula��o.

No exemplo de c�digo anterior, o Excel foi iniciado com a instru��o Shell e o nome do arquivo foi usado como um argumento de linha de comando. Nenhuma automa��o foi usada no exemplo anterior. No entanto, se desejado, � poss�vel usar uma quantidade m�nima de automa��o para abrir o arquivo de texto e salv�-lo no formato de pasta de trabalho do 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 obter informa��es adicionais sobre como usar a E/S de Arquivo no aplicativo Visual Basic, consulte o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

172267

(http://support.microsoft.com/kb/172267/ )

Exemplo: RECEDIT.VBP apresenta E/S de arquivo no Visual Basic

Transferir dados para uma planilha usando o ADO.NET

Com o Microsoft Jet OLE DB Provider, voc� pode adicionar registros a uma tabela de uma pasta de trabalho do Excel j� existente. Uma "Tabela" no Excel � apenas um intervalo com um nome definido. A primeira linha do intervalo deve conter os cabe�alhos (ou nomes de campo) e todas as demais linhas subseq�entes, os registros. A seguinte etapa ilustra de que forma � poss�vel criar uma pasta de trabalho usando uma tabela vazia Minha_tabela:

Inicie uma nova pasta de trabalho no Excel.
Adicione os seguintes cabe�alhos �s c�lulas A1:B1 de Plan1:

A1: Nome B1: Sobrenome
Formate a c�lula B1 com alinhamento � direita.
Selecione A1:B1.
No menu Inserir, selecione Nome e Definir. Digite o nome da Minha_tabela e clique em OK.
Salve a nova pasta de trabalho como C:\Book1.xls e feche o Excel.

Para adicionar registros � Minha_tabela com o ADO, voc� pode usar um c�digo semelhante ao seguinte:

   '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

Durante a inclus�o de registros na tabela desta maneira, a formata��o da pasta de trabalho � mantida. No exemplo anterior, os novos campos adicionados � coluna B estavam formatados com alinhamento � direita. Cada registro adicionado a uma linha usa a formata��o da linha acima.

N�o se esque�a de que quando um registro � adicionado a uma c�lula ou a c�lulas na planilha, ele substitui todos os dados j� existentes, em outras palavras, as linhas da tabela n�o s�o "deslocadas" quando novos registros s�o adicionados. Voc� deve se lembrar disso ao criar o layout dos dados de suas planilhas.

Observa��o O m�todo de atualiza��o dos dados em uma planilha do Excel usando ADO ou DAO n�o funciona no ambiente Visual Basic for Application dentro do Access, ap�s a instala��o do Office 2003 Service Pack 2 (SP2) ou da atualiza��o para o Access 2002, inclusa no artigo 904018 da Base de Dados de Conhecimento da Microsoft. O m�todo funciona bem no ambiente Visual Basic for Application de outros aplicativos do Office, como Word, Excel e Outlook. Para obter informa��es adicionais, clique nos n�meros abaixo para ler os artigos na Base de Dados de Conhecimento da Microsoft:

904953

(http://support.microsoft.com/kb/904953/ )

Voc� n�o pode alterar, adicionar ou excluir dados nas tabelas que estejam vinculados a uma planilha do Excel no Office Access 2003 ou no Access 2002

904018

(http://support.microsoft.com/kb/904018/ )

Descri��o da atualiza��o para o Access 2002: 18 de outubro de 2005

Para obter informa��es adicionais sobre como usar o ADO para acessar uma planilha do Excel, consulte os seguintes artigos na Base de Dados de Conhecimento da Microsoft:

195951

(http://support.microsoft.com/kb/195951/ )

Como consultar e atualizar dados do Excel usando ADO a partir do ASP

Usar DDE para transferir dados para o Excel

DDE � uma alternativa � automa��o, como um meio de comunica��o com o Excel e de transfer�ncia de dados, por�m, com o advento da automa��o e do COM, o DDE deixou de ser o m�todo preferencial para a comunica��o com outros aplicativos e s� deve ser usado quando n�o houver outra solu��o dispon�vel.

Para transferir dados para o Excel usando DDE, voc� pode:

Usar o m�todo LinkPoke para enviar dados a um intervalo espec�fico de c�lulas,

-ou-
Usar o m�todo LinkExecute para enviar comandos a serem executados pelo Excel.

O c�digo de exemplo a seguir ilustra como estabelecer uma conversa��o DDE com o Excel, para que os dados sejam enviados �s c�lulas em uma planilha e os comandos sejam executados. Usando o exemplo, para que uma conversa��o DDE seja estabelecida corretamente com LinkTopic Excel|MyBook.xls, � preciso que haja uma pasta de trabalho de nome MyBook.xls j� aberta em uma inst�ncia em execu��o do Excel.

Observa��o No exemplo, Text1 representa um controle Text Box de um formul�rio do 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

Ao usar LinkPoke com Excel, voc� especifica o intervalo na nota��o linha-coluna (R1C1) para LinkItem. Caso os dados estejam sendo enviados para v�rias c�lulas, � poss�vel usar uma seq��ncia em que as colunas s�o separadas por tabula��es e as linhas, por retornos de carro.

Ao usar LinkExecute para solicitar a execu��o de um comando ao Excel, voc� deve informar a ele o comando na sintaxe XLM (Excel Macro Language). A documenta��o XLM n�o est� inclusa nas vers�es do Excel 97 e mais recentes. Para obter informa��es adicionais sobre como adquirir a documenta��o XLM, consulte o seguinte artigo na Base de Dados de Conhecimento da Microsoft:

143466

(http://support.microsoft.com/kb/143466/ )

XL97: Arquivo Macro97.exe dispon�vel em servi�os online

DDE n�o � uma solu��o recomendada para a comunica��o com o Excel. A automa��o oferece o que h� de melhor em flexibilidade, al�m de dar mais acesso aos novos recursos oferecidos pelo Excel.

Voltar para o in�cio | Submeter coment�rios