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

Traduções de Artigos Traduções de Artigos
Artigo: 247412 - Ver produtos para os quais este artigo se aplica.
Expandir tudo | Reduzir tudo

Sumário

Este artigo aborda os vários métodos de transferência de dados para o Microsoft Excel, a partir da aplicação do Microsoft Visual Basic. Este artigo apresenta também as vantagens e desvantagens de cada método para que possa escolher a solução mais adequada à sua situação.

Mais Informação

A abordagem utilizada com maior frequência para transferir dados para um livro do Excel é a automatização. A automatização permite uma maior flexibilidade para especificar a localização dos seus dados no livro, bem como a capacidade de formatar o livro e de efectuar várias definições em tempo de execução. Com a automatização, pode utilizar várias abordagens para transferir os seus dados:
  • Transferir dados célula a célula
  • Transferir dados de uma matriz para um intervalo de células
  • Transferir dados de um conjunto de registos ADO para um intervalo de células utilizando o método CopyFromRecordset
  • Criar uma QueryTable numa folha de cálculo do Excel que contenha o resultado de uma consulta a uma origem de dados ODBC ou OLEDB
  • Transferir dados para a área de transferência e colar o conteúdo da área de transferência numa folha de cálculo do Excel
Existem também métodos que podem ser utilizados para transferir dados para o Excel que não requerem necessariamente automatização. Se estiver a executar uma aplicação do lado do servidor, esta poderá ser uma boa abordagem no sentido de não sobrecarregar os clientes com o processamento de dados. Os seguintes métodos podem ser utilizados para transferir os dados sem automatização:
  • Transferir os dados para um ficheiro de texto delimitado por tabulações ou vírgulas, de forma a que o Excel os possa distribuir posteriormente em células numa folha de cálculo
  • Transferir os dados para uma folha de cálculo utilizando ADO
  • Transferir dados para o Excel utilizando o intercâmbio dinâmico de dados (DDE, Dynamic Data Exchange)
As seguintes secções fornecem mais detalhes sobre cada uma destas soluções.

Nota: quando utiliza o Microsoft Office Excel 2007, pode utilizar o novo formato de ficheiro (*.xlsx) do Excel 2007 Workbook ao guardar os livros. Para o fazer, localize a linha de código seguinte nos seguintes exemplos de código:
oBook.SaveAs "C:\Book1.xls"
Substitua este código com a seguinte linha de código:
oBook.SaveAs "C:\Book1.xlsx"
Além disso, a base de dados Adamastor não está incluída no Office 2007 por predefinição. Contudo, pode transferir a base de dados Adamastor a partir do Microsoft Office Online.

Utilizar a automatização para transferir dados célula a célula

Com a automatização, pode transferir dados para uma folha de cálculo uma célula de cada 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
A transferência de dados célula a célula pode ser uma abordagem perfeitamente aceitável se a quantidade de dados for pequena. O utilizador tem flexibilidade para colocar dados em qualquer parte do livro e pode formatar as células de forma condicional em tempo de execução. No entanto, esta abordagem não é recomendada se tiver uma grande quantidade de dados a transferir para um livro do Excel. Cada objecto Range que obtiver em tempo de execução resulta num pedido de interface, pelo que a transferência de dados desta maneira pode ser lenta. Adicionalmente, o Microsoft Windows 95 e Windows 98 têm uma limitação de 64 K relativamente a pedidos de interface. Se atingir ou exceder este limite de 64 K relativo a pedidos de interface, o servidor de automatização (Excel) poderá deixar de responder ou o utilizador poderá receber erros indicando falta de memória. Esta limitação do Windows 95 e do Windows 98 é abordada no seguinte artigo da base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
216400 Cross-process COM automation can hang client application on Win 95/98
Uma vez mais, a transferência de dados célula a célula é aceitável apenas para pequenas quantidades de dados. Se tiver de transferir grandes conjuntos de dados para o Excel, deverá considerar uma das soluções apresentadas mais adiante neste artigo.

Para obter mais exemplos de código para automatizar do Excel, consulte o seguinte artigo na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
219151 How to automate Microsoft Excel from Visual Basic

Utilizar a automatização para transferir uma matriz de dados para um intervalo de uma folha de cálculo

Uma matriz de dados pode ser transferida 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
Se transferir os dados utilizando uma matriz, em vez de célula a célula, poderá obter um ganho significativo em termos de desempenho com uma grande quantidade de dados. Considere esta linha do código acima que transfere dados para 300 células da folha de cálculo:
   oSheet.Range("A2").Resize(100, 3).Value = DataArray
Esta linha representa dois pedidos de interface (um para o objecto Range devolvido pelo método Range e outro para o objecto Range devolvido pelo método Resize). Por outro lado, a transferência de dados célula a célula iria necessitar de pedidos de 300 interfaces para objectos Range. Sempre que possível, pode beneficiar da transferência de dados em massa e reduzir o número de pedidos de interface efectuados..

Utilizar a automatização para transferir um conjunto de registos ADO para um intervalo da folha de cálculo

No Excel 2000, foi introduzido o método CopyFromRecordset, que permite transferir um conjunto de registos ADO (ou DAO) para um intervalo de uma folha de cálculo. O seguinte código ilustra como poderia automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 e transferir o conteúdo da tabela Encomendas da base de dados de exemplo Adamastor utilizando 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
Nota: se utilizar a versão Office 2007 da base de dados Adamastor, tem de substituir a seguinte linha de código no exemplo de código:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Substitua esta linha de código com a seguinte linha de código:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
O Excel 97 fornece também um método CopyFromRecordset, mas este só pode ser utilizado com um conjunto de registos DAO. O método CopyFromRecordset do Excel 97 não suporta ADO.

Para mais informações sobre como utilizar ADO e o método CopyFromRecordset, consulte o seguinte artigo na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
246335 How to transfer data from an ADO recordset to Excel with automation

Utilizar a automatização para criar uma 'QueryTable' numa folha de cálculo

Um objecto QueryTable representa uma tabela criada a partir de dados devolvidos pode uma origem de dados externa. Durante a automatização do Microsoft Excel, pode criar um objecto QueryTable fornecendo simplesmente uma cadeia de ligação para uma origem de dados OLEDB ou ODBC juntamente com uma cadeia de SQL. O Excel assume a responsabilidade de gerar o conjunto de registos e inseri-lo na folha de cálculo na localização que especificar. A utilização de QueryTables proporciona várias vantagens relativamente ao método CopyFromRecordset:
  • O Excel processa a criação do conjunto de registos e a respectiva colocação na folha de cálculo.
  • A consulta pode ser guardada com a QueryTable para que possa ser actualizada posteriormente para obter um conjunto de registos actualizado.
  • Quando um novo objecto QueryTable é adicionado à folha de cálculo, pode especificar que dados já existentes em células da folha de cálculo sejam deslocados para dar lugar aos novos dados (consulte a propriedade RefreshStyle para obter detalhes).
O seguinte código demonstra como pode automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 para criar um novo objecto QueryTable numa folha de cálculo do Excel utilizando dados da base de dados de exemplo Adamastor:
   '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

Utilizar a área de transferência

A área de transferência do Windows pode também ser utilizada como um mecanismo para transferir dados para uma folha de cálculo. Para colar dados em várias células numa folha de cálculo, pode copiar uma cadeia onde as colunas estejam delimitadas por caracteres de tabulação e as linhas estejam delimitadas por caracteres de mudança de linha. O seguinte código ilustra o modo como o Visual Basic pode utilizar o respectivo objecto de área de transferência 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 ficheiro de texto delimitado que o Excel possa distribuir em linhas e colunas

O Excel pode abrir ficheiros delimitados por tabulações ou por vírgulas e distribuir correctamente os dados por células. O utilizador pode tirar partido desta funcionalidade quando pretender transferir uma grande quantidade de dados para uma folha de cálculo utilizando pouca, ou nenhuma, automatização. Esta poderá constituir uma boa abordagem para uma aplicação cliente-servidor, uma vez que o ficheiro de texto pode ser gerado do lado do servidor. Pode então abrir o ficheiro de texto no cliente, utilizando a automatização onde for adequado.

O seguinte código ilustra a forma como pode criar um ficheiro de texto delimitado por vírgulas a partir de um conjunto de registos 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: se utilizar a versão Office 2007 da base de dados Adamastor, tem de substituir a seguinte linha de código no exemplo de código:
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      sNWind & ";"
Substitua esta linha de código com a seguinte linha de código:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
      sNWind & ";"
Se o ficheiro de texto tiver uma extensão .CSV, o Excel abre o ficheiro sem apresentar o Assistente de importações de texto e assume automaticamente que o ficheiro é delimitado por vírgulas. De modo semelhante, se o ficheiro tiver uma extensão .TXT, o Excel analisa automaticamente o ficheiro utilizando delimitadores de tabulação.

No exemplo de código anterior, o Excel era iniciado utilizando a instrução Shell e o nome do ficheiro era utilizado como um argumento da linha de comandos. Não foi utilizada qualquer automatização no exemplo anterior. No entanto, se assim for pretendido, poderá utilizar um mínimo de automatização para abrir o ficheiro de texto e guardá-lo com o formato de livro 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 mais informações sobre como utilizar E/S de ficheiros a partir da aplicação do Visual Basic, consulte o seguinte artigo na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
172267 RECEDIT.VBP demonstrates file I/O in Visual Basic

Transferir dados para uma folha de cálculo utilizando ADO

Através da utilização do fornecedor de OLE DB do Microsoft Jet, pode adicionar registos a uma tabela de um livro do Excel existente. Uma "tabela" no Excel é apenas um intervalo com um nome definido. A primeira linha do intervalo tem de conter os cabeçalhos (ou nomes de campos) e todas as linhas posteriores contêm os registos. Os seguintes passos ilustram a forma como pode criar um livro com uma tabela vazia com o nome AMinhaTabela.
Excel 97, Excel 2000 e Excel 2003
  1. Inicie um novo livro no Excel.
  2. Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:

    A1: Nome B1: Apelido
  3. Formate a célula B1 como alinhada à direita.
  4. Seleccione A1:B1.
  5. No menu Inserir, seleccione Nome e seleccione Definir. Introduza o nome AMinhaTabela e clique em OK.
  6. Guarde o novo livro como C:\Livro1.xls e saia do Excel.
Para adicionar registos a AMinhaTabela utilizando ADO, pode utilizar 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
Excel 2007
  1. No Excel 2007, inicie um livro novo.
  2. Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:

    A1: Nome B1: Apelido
  3. Formate a célula B1 como alinhada à direita.
  4. Seleccione A1:B1.
  5. No Friso, clique no separador Fórmulas e clique em Definir Nome. Introduza do nome AMinhaTabela e clique em OK.
  6. Guarde o novo livro como C:\Book1.xlsx e saia do Excel.
Para adicionar registos à tabela AMinhaTabela utilizando ADO, pode utilizar código semelhante ao seguinte.
   '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
Quando adiciona registos à tabela desta forma, a formatação do livro é mantida. No exemplo anterior, os novos campos adicionados à coluna B são formatados com alinhamento à direita. Cada registo adicionado a uma linha assume a formatação da linha acima.

Deve ter em atenção que quando um registo é adicionado a uma célula ou células da folha de cálculo, substitui quaisquer dados anteriormente existentes nessas células; por outras palavras, as linhas da folha de cálculo não são "empurradas para baixo" quando são adicionados novos registos. Tenha este pormenor em mente quando criar o esquema de dados nas suas folhas de cálculo.

Nota: o método para actualizar dados numa folha de cálculo do Excel utilizando ADO ou DAO não funciona no ambiente do Visual Basic for Applications no Access depois de instalar o Office 2003 Service Pack 2 (SP2) ou depois de instalar a actualização para o Access 2002 incluída no artigo 904018 da base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base). O método funciona bem no ambiente do Visual Basic for Applications de outras aplicações do Office, tal como o Word, o Excel e o Outlook. Para obter mais informações, clique nos números de artigo que se seguem para visualizar os artigos na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
904953 Não é possível alterar, adicionar nem eliminar dados nas tabelas que estão ligadas a um livro do Excel no Office Access 2003 ou no Access 2002
904018 Descrição da actualização do Access 2002: 18 de Outubro de 2005:

Para obter informações adicionais sobre como utilizar ADO para aceder a um livro do Excel, consulte os seguintes artigos na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
195951 How to query and update Excel data using ADO from ASP

Utilizar DDE para transferir dados para o Excel

O DDE constitui uma alternativa à automatização como forma de comunicação com o Excel e de transferência de dados; no entanto, com o aparecimento da automatização e do COM, o DDE deixou de ser o método preferencial para comunicar com outras aplicações, devendo ser utilizado apenas quando não existe outra solução disponível.

Para transferir dados para o Excel utilizando DDE, pode:
  • Utilizar o método LinkPoke para efectuar o poke de dados num intervalo específico de células,

    - ou -
  • Utilizar o método LinkExecute para enviar comandos que serão executados pelo Excel.
O seguinte exemplo de código ilustra como estabelecer uma conversação DDE com o Excel, para que possa efectuar o poke de dados em células de uma folha de cálculo e executar comandos. Utilizando este exemplo, para que uma conversação DDE seja estabelecida com êxito com LinkTopic Excel|OMeuLivro.xls, um livro com o nome OMeuLivro.xls já deve estar aberto numa instância em execução do Excel.

Nota: quando utiliza o Excel 2007, pode utilizar o novo formato de ficheiro .xlsx para guardar os livros. Certifique-se de que actualiza o nome do ficheiro no seguinte exemplo de código.

Nota: neste exemplo, Texto1 representa um controlo de caixa de texto num 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
Quando utiliza LinkPoke com o Excel, o utilizador especifica o intervalo na notação linha-coluna (R1C1) para LinkItem. Se estiver a efectuar o poke de dados em várias células, poderá utilizar uma cadeia onde as colunas estejam delimitadas por tabulações e as linhas estejam delimitadas por caracteres de mudança de linha.

Quando utiliza LinkExecute para solicitar ao Excel que execute um comando, tem de fornecer o comando ao Excel na sintaxe da linguagem de macro do Excel (XLM). A documentação sobre XLM não está incluída nas versões 97 e posteriores do Excel. Para obter mais informações sobre como pode obter a documentação sobre XLM, consulte o seguinte artigo na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
143466 Macro97.exe file available on online services
DDE não é uma solução recomendada para comunicar com o Excel. A automatização proporciona a maior flexibilidade e um acesso mais alargado às novas funcionalidades do Excel.

Referências

Para obter mais informações, clique no número de artigo que se segue para visualizar o artigo na base de dados de conhecimento da Microsoft (KB, Microsoft Knowledge Base):
306022 How to transfer data to an Excel workbook by using Visual Basic .NET

Propriedades

Artigo: 247412 - Última revisão: 15 de março de 2007 - Revisão: 8.0
A informação contida neste artigo aplica-se a:
  • Microsoft Office Excel 2007
  • 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
Palavras-chave: 
kbautomation kbdde kbinfo KB247412

Submeter comentários

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com