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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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 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
(http://support.microsoft.com/kb/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.QuitUtilizar 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.QuitCriar 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 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
(http://support.microsoft.com/kb/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
- Inicie um novo livro no Excel.
- Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:
A1: Nome B1: Apelido - Formate a célula B1 como alinhada à direita.
- Seleccione A1:B1.
- No menu Inserir, seleccione Nome e seleccione Definir. Introduza o nome AMinhaTabela e clique em OK.
- 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.CloseExcel 2007
- No Excel 2007, inicie um livro novo.
- Adicione os seguintes cabeçalhos às células A1:B1 da Folha1:
A1: Nome B1: Apelido - Formate a célula B1 como alinhada à direita.
- Seleccione A1:B1.
- No Friso, clique no separador Fórmulas e clique em Definir Nome. Introduza do nome AMinhaTabela e clique em OK.
- 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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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
(http://support.microsoft.com/kb/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.
Voltar ao topo
