ID do artigo: 247412 - Última revisão: quinta-feira, 25 de maio de 2006 - Revisão: 8.0

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

Exibir os produtos aos quais esse artigo se aplica.
Dica do SistemaEste artigo aplica-se a um sistema operativo diferente do que está a utilizar. Foi desactivado o conteúdo do artigo, que pode não ser relevante para si.
Expandir tudo | Recolher tudo

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

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:
  1. Inicie uma nova pasta de trabalho no Excel.
  2. Adicione os seguintes cabeçalhos às células A1:B1 de Plan1:

    A1: Nome B1: Sobrenome
  3. Formate a célula B1 com alinhamento à direita.
  4. Selecione A1:B1.
  5. No menu Inserir, selecione Nome e Definir. Digite o nome da Minha_tabela e clique em OK.
  6. 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

Referências

Para obter informações adicionais, clique no número abaixo para ler o artigo na Base de Dados de Conhecimento da Microsoft:
306022  (http://support.microsoft.com/kb/306022/ ) Como transferir dados para uma planilha do Excel usando o Visual Basic .NET
Voltar para o início
A informação contida neste artigo aplica-se a:
  • 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 Enterprise Edition
  • Microsoft Visual Basic 6.0 Professional Edition
Voltar para o início
Palavras-chave: 
kbautomation kbdde kbinfo KB247412
Voltar para o início