Métodos para transferir dados para o Excel a partir do Visual Basic
Resumo
Este artigo aborda vários métodos para transferir dados para o Microsoft Excel a partir da sua aplicação Microsoft Visual Basic. Este artigo também apresenta as vantagens e desvantagens de cada método para que possa escolher a solução mais adequada para si.
Mais Informações
A abordagem mais frequentemente utilizada para transferir dados para um livro do Excel é a Automatização. A automatização dá-lhe a maior flexibilidade para especificar a localização dos seus dados no livro, bem como a capacidade de formatar o livro e fazer várias definições no tempo de execução. Com a Automatização, pode utilizar várias abordagens para transferir os seus dados:
- Transferir célula de dados por célula
- Transferir dados numa matriz para um intervalo de células
- Transferir dados num conjunto de registos ADO para um intervalo de células com o método CopyFromRecordset
- Criar uma Tabela de Consulta numa folha de cálculo do Excel que contenha o resultado de uma consulta numa origem de dados ODBC ou OLEDB
- Transferir dados para a área de transferência e, em seguida, colar os conteúdos da área de transferência numa folha de cálculo do Excel
Também existem métodos que pode utilizar para transferir dados para o Excel que não necessitam necessariamente de Automatização. Se estiver a executar uma aplicação do lado do servidor, esta pode ser uma boa abordagem para tirar a maior parte do processamento dos dados dos seus clientes. Os seguintes métodos podem ser utilizados para transferir os seus dados sem Automatização:
- Transferir os seus dados para um ficheiro de texto delimitado por tabulações ou vírgulas que o Excel pode analisar posteriormente em células numa folha de cálculo
- Transferir os seus dados para uma folha de cálculo com o ADO
- Transferir dados para o Excel com o Dynamic Data Exchange (DDE)
As secções seguintes fornecem mais detalhes sobre cada uma destas soluções.
Nota Quando utiliza o Microsoft Office Excel 2007, pode utilizar o novo formato de ficheiro livro do Excel 2007 (*.xlsx) quando guarda os livros. Para tal, localize a seguinte linha de código nos seguintes exemplos de código:
oBook.SaveAs "C:\Book1.xls"
Substitua este código pela seguinte linha de código:
oBook.SaveAs "C:\Book1.xlsx"
Além disso, a base de dados da Northwind não está incluída no Office 2007 por predefinição. No entanto, pode transferir a base de dados Northwind a partir do Microsoft Office Online.
Utilizar a Automatização para transferir células de dados por 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
Transferir células de dados por célula pode ser uma abordagem perfeitamente aceitável se a quantidade de dados for pequena. Tem a flexibilidade de colocar dados em qualquer parte do livro e pode formatar as células condicionalmente no tempo de execução. No entanto, esta abordagem não é recomendada se tiver uma grande quantidade de dados para transferir para um livro do Excel. Cada objeto de Intervalo adquirido no tempo de execução resulta num pedido de interface para que a transferência de dados desta forma possa ser lenta. Além disso, o Microsoft Windows 95 e o Windows 98 têm uma limitação de 64K nos pedidos de interface. Se atingir ou exceder este limite de 64 mil nos pedidos de interface, o servidor de Automatização (Excel) poderá deixar de responder ou poderá receber erros que indicam pouca memória.
Mais uma vez, a transferência de células de dados por célula só é aceitável para pequenas quantidades de dados. Se precisar de transferir grandes conjuntos de dados para o Excel, deve considerar uma das soluções apresentadas mais tarde.
Para obter mais código de exemplo para Automatizar o Excel, consulte Como automatizar o Microsoft Excel a partir do Visual Basic.
Utilizar a automatização para transferir uma matriz de dados para um intervalo numa folha de cálculo
Uma matriz de dados pode ser transferida para um intervalo de múltiplas células ao mesmo tempo:
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 seus dados através de uma matriz em vez de célula a célula, pode obter um enorme ganho de desempenho com uma grande quantidade de dados. Considere esta linha do código acima que transfere dados para 300 células na folha de cálculo:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Esta linha representa dois pedidos de interface (um para o objeto Intervalo que o método Range devolve e outro para o objeto Intervalo que o método Redimensionar devolve). Por outro lado, a transferência da célula de dados por célula exigiria pedidos de 300 interfaces para objetos de Intervalo. Sempre que possível, pode beneficiar da transferência dos seus dados em massa e da redução do número de pedidos de interface que fizer.
Utilizar a automatização para transferir um conjunto de registos ADO para um intervalo de folhas de cálculo
O Excel 2000 introduziu o método CopyFromRecordset que lhe permite transferir um conjunto de registos ADO (ou DAO) para um intervalo numa folha de cálculo. O código seguinte ilustra como pode automatizar o Excel 2000, Excel 2002 ou Office Excel 2003 e transferir os conteúdos da tabela Encomendas na Base de Dados de Exemplo da 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
Nota Se utilizar a versão do Office 2007 da base de dados Northwind, 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 pela seguinte linha de código:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _ sNWind & ";"
O Excel 97 também fornece um método CopyFromRecordset, mas só pode utilizá-lo com um conjunto de registos DAO. CopyFromRecordset com o Excel 97 não suporta ADO.
Para obter mais informações sobre como utilizar o ADO e o método CopyFromRecordset, veja Como transferir dados de um conjunto de registos ADO para o Excel com automatização.
Utilizar a automatização para criar uma Tabela de Consulta numa folha de cálculo
Um objeto QueryTable representa uma tabela criada a partir de dados devolvidos a partir de uma origem de dados externa. Ao automatizar o Microsoft Excel, pode criar uma Tabela de Consulta ao simplesmente fornecer um cadeia de ligação a 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 oferece várias vantagens em vez do método CopyFromRecordset:
- O Excel processa a criação do conjunto de registos e a respetiva colocação na folha de cálculo.
- A consulta pode ser guardada com a QueryTable para que possa ser atualizada posteriormente para obter um conjunto de registos atualizado.
- Quando uma nova Tabela de Consulta é adicionada à sua folha de cálculo, pode especificar que os dados já existentes nas células da folha de cálculo sejam movidos para acomodar os novos dados (consulte a propriedade RefreshStyle para obter detalhes).
O código seguinte demonstra como pode automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 para criar uma nova Tabela de Consulta numa folha de cálculo do Excel com dados da Base de Dados de Exemplo da 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
Utilizar a área de transferência
A Área de Transferência do Windows também pode ser utilizada como um mecanismo para transferir dados para uma folha de cálculo. Para colar dados em múltiplas células numa folha de cálculo, pode copiar uma cadeia onde as colunas são delimitadas por carateres de tabulação e as linhas são delimitadas por símbolos de retorno. O código seguinte ilustra como o Visual Basic pode utilizar o objeto Á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 pode analisar em linhas e colunas
O Excel pode abrir ficheiros separados por tabulações ou vírgulas e analisar corretamente os dados em células. Pode tirar partido desta funcionalidade quando pretender transferir uma grande quantidade de dados para uma folha de cálculo enquanto utiliza pouca Automatização, se existir. Esta pode ser uma boa abordagem para uma aplicação cliente-servidor porque o ficheiro de texto pode ser gerado do lado do servidor. Em seguida, pode abrir o ficheiro de texto no cliente, utilizando a Automatização, quando for adequado.
O código seguinte ilustra 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 do Office 2007 da base de dados Northwind, 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 pela 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 de .CSV, o Excel abre o ficheiro sem apresentar o Assistente de Importação de Texto e assume automaticamente que o ficheiro está delimitado por vírgulas. Da mesma forma, se o seu ficheiro tiver uma extensão .TXT, o Excel analisa automaticamente o ficheiro com delimitadores de separadores.
No exemplo de código anterior, o Excel foi iniciado com a instrução Shell e o nome do ficheiro foi utilizado como um argumento de linha de comandos. Não foi utilizada Nenhuma Automatização no exemplo anterior. No entanto, se assim o desejar, pode utilizar uma quantidade mínima de Automatização para abrir o ficheiro de texto e guardá-lo no 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
Transferir dados para uma folha de cálculo com o ADO
Com o Fornecedor OLE DB do Microsoft Jet, pode adicionar registos a uma tabela num 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 campo) e todas as linhas subsequentes contêm os registos. Os passos seguintes ilustram como pode criar um livro com uma tabela vazia chamada MyTable.
Excel 97, Excel 2000 e Excel 2003
Iniciar um novo livro no Excel.
Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:
A1: NomePróprio B1: Apelido
Formate a célula B1 como alinhada à direita.
Selecione A1:B1.
No menu Inserir, selecione Nomes e, em seguida, selecione Definir. Introduza o nome MyTable e clique em OK.
Guarde o novo livro como C:\Book1.xls e saia do Excel.
Para adicionar registos ao MyTable com o 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
No Excel 2007, inicie um novo livro.
Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:
A1: NomePróprio B1: Apelido
Formate a célula B1 como alinhada à direita.
Selecione A1:B1.
No Friso , clique no separador Fórmulas e, em seguida, clique em Definir Nome. Escreva o nome MyTable e, em seguida, clique em OK.
Guarde o novo livro como C:\Book1.xlsx e, em seguida, saia do Excel.
Para adicionar registos à tabela MyTable com o ADO, utilize código semelhante ao seguinte exemplo de código.
'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 no livro é mantida. No exemplo anterior, os novos campos adicionados à coluna B são formatados com alinhamento à direita. Cada registo que é adicionado a uma linha empresta o formato da linha acima.
Deve ter em atenção que, quando um registo é adicionado a uma célula ou células na folha de cálculo, este substitui todos os dados anteriormente nessas células; por outras palavras, as linhas na folha de cálculo não são "enviadas para baixo" quando são adicionados novos registos. Deve ter isto em mente ao estruturar o esquema de dados nas suas folhas de cálculo.
Nota
O método para atualizar dados numa folha de cálculo do Excel através do ADO ou do DAO não funciona no ambiente do Visual Basic for Application no Access depois de instalar o Office 2003 Service Pack 2 (SP2) ou depois de instalar a atualização para o Access 2002 incluída no artigo da Base de Dados de Conhecimento Microsoft 904018. O método funciona bem no ambiente do Visual Basic for Application a partir de outras aplicações do Office, como Word, Excel e Outlook.
Para obter mais informações, consulte o seguinte artigo:
Para obter mais informações sobre a utilização do ADO para aceder a um livro do Excel, consulte Como Consultar e Atualizar Dados do Excel utilizando a ADO do ASP.
Utilizar a DDE para transferir dados para o Excel
A DDE é uma alternativa à Automatização como forma de comunicar com o Excel e transferir dados; no entanto, com o advento da Automatização e COM, a DDE já não é o método preferencial para comunicar com outras aplicações e só deve ser utilizada quando não existe outra solução disponível para si.
Para transferir dados para o Excel através de DDE, pode utilizar o método LinkPoke para colocar dados num intervalo específico de células ou utilizar o método LinkExecute para enviar comandos que o Excel irá executar.
O exemplo de código seguinte ilustra como estabelecer uma conversação DDE com o Excel para que possa colocar dados em células numa folha de cálculo e executar comandos. Com este exemplo, para que uma conversação DDE seja estabelecida com êxito no LinkTopic Excel|MyBook.xls, um livro com o nome MyBook.xls já tem de ser 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 atualiza o nome do ficheiro no seguinte exemplo de código. Neste exemplo, Text1 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
Ao utilizar o LinkPoke com o Excel, especifique o intervalo na notação de coluna de linha (R1C1) para o LinkItem. Se estiver a introduzir dados em múltiplas células, pode utilizar uma cadeia onde as colunas são delimitadas por tabulações e as linhas são delimitadas por símbolos de retorno.
Quando utiliza o LinkExecute para pedir ao Excel para executar um comando, tem de dar ao Excel o comando na sintaxe da Linguagem de Macros do Excel (XLM). A documentação XLM não está incluída nas versões 97 e posteriores do Excel.
A DDE não é uma solução recomendada para comunicar com o Excel. A automatização proporciona a maior flexibilidade e dá-lhe mais acesso às novas funcionalidades que o Excel tem para oferecer.
Comentários
Brevemente: Ao longo de 2024, vamos descontinuar progressivamente o GitHub Issues como mecanismo de feedback para conteúdos e substituí-lo por um novo sistema de feedback. Para obter mais informações, veja: https://aka.ms/ContentUserFeedback.
Submeter e ver comentários