Métodos para transferir dados para o Excel do Visual Basic
Resumo
Este artigo discute vários métodos para transferir dados para o Microsoft Excel de seu aplicativo Do Microsoft Visual Basic. Este artigo também apresenta as vantagens e as desvantagens para cada método para que você possa escolher a solução que funciona melhor para você.
Informações adicionais
A abordagem mais usada para transferir dados para uma pasta de trabalho do Excel é Automação. A automação oferece a maior flexibilidade para especificar a localização de seus dados na pasta de trabalho, bem como a capacidade de formatar a pasta de trabalho e fazer várias configurações em tempo de execução. Com a Automação, você pode usar várias abordagens para transferir seus dados:
- Transferir célula de dados por célula
- Transferir dados em uma matriz para um intervalo de células
- Transferir dados em um conjunto de registros do ADO para um intervalo de células usando o método CopyFromRecordset
- Criar uma ConsultaTable em uma planilha do Excel que contém o resultado de uma consulta em uma fonte de dados ODBC ou OLEDB
- Transferir dados para a área de transferência e colar o conteúdo da área de transferência em uma planilha do Excel
Há também métodos que você pode usar para transferir dados para o Excel que não necessariamente exigem Automação. Se você estiver executando um servidor de aplicativo, essa pode ser uma boa abordagem para tirar a maior parte do processamento dos dados de seus clientes. Os seguintes métodos podem ser usados para transferir seus dados sem Automação:
- Transferir seus dados para um arquivo de texto delimitado por guia ou vírgula que o Excel pode analisar posteriormente em células em uma planilha
- Transferir seus dados para uma planilha usando o ADO
- Transferir dados para o Excel usando o DDE (Dynamic Data Exchange)
As seções a seguir fornecem mais detalhes sobre cada uma dessas soluções.
Nota Ao usar o Microsoft Office Excel 2007, você pode usar o novo formato de arquivo da Pasta de Trabalho do Excel 2007 (*.xlsx) ao salvar as pastas de trabalho. Para fazer isso, localize a seguinte linha de código nos seguintes exemplos de código:
oBook.SaveAs "C:\Book1.xls"
Substitua esse código pela seguinte linha de código:
oBook.SaveAs "C:\Book1.xlsx"
Além disso, o banco de dados Northwind não está incluído no Office 2007 por padrão. No entanto, você pode baixar o banco de dados Northwind do Microsoft Office Online.
Usar a Automação para transferir célula de dados por célula
Com a Automação, você pode transferir dados para uma planilha de uma célula por 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élula de dados por célula pode ser uma abordagem perfeitamente aceitável se a quantidade de dados for pequena. Você tem a flexibilidade de colocar dados em qualquer lugar da pasta de trabalho e pode formatar as células condicionalmente em tempo de execução. No entanto, essa abordagem não é recomendada se você tiver uma grande quantidade de dados para transferir para uma pasta de trabalho do Excel. Cada objeto Range que você adquire em tempo de execução resulta em uma solicitação de interface para que a transferência de dados dessa maneira possa ser lenta. Além disso, o Microsoft Windows 95 e o Windows 98 têm uma limitação de 64K em solicitações de interface. Se você atingir ou exceder esse limite de 64k em solicitações de interface, o servidor de Automação (Excel) poderá parar de responder ou você poderá receber erros que indicam memória baixa.
Mais uma vez, a transferência de célula de dados por célula é aceitável apenas para pequenas quantidades de dados. Se você precisar transferir grandes conjuntos de dados para o Excel, considere uma das soluções apresentadas posteriormente.
Para obter mais código de exemplo para Automatizar o Excel, consulte Como automatizar o Microsoft Excel no Visual Basic.
Usar a automação para transferir uma matriz de dados para um intervalo em uma planilha
Uma matriz de dados pode ser transferida para um intervalo de várias 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 você transferir seus dados usando uma matriz em vez de célula por célula, poderá realizar um enorme ganho de desempenho com uma grande quantidade de dados. Considere essa linha do código acima que transfere dados para 300 células na planilha:
oSheet.Range("A2").Resize(100, 3).Value = DataArray
Essa linha representa duas solicitações de interface (uma para o objeto Range que o método Range retorna e outra para o objeto Range que o método Resize retorna). Por outro lado, transferir a célula de dados por célula exigiria solicitações de 300 interfaces para objetos Range. Sempre que possível, você pode se beneficiar da transferência de seus dados em massa e da redução do número de solicitações de interface que você faz.
Usar a automação para transferir um conjunto de registros do ADO para um intervalo de planilhas
O Excel 2000 introduziu o método CopyFromRecordset que permite transferir um conjunto de registros ADO (ou DAO) para um intervalo em uma planilha. O código a seguir ilustra como você pode automatizar o Excel 2000, Excel 2002 ou Office Excel 2003 e transferir o conteúdo da tabela Orders no Banco de Dados de Exemplo northwind usando 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 você usar a versão do Office 2007 do banco de dados Northwind, deverá substituir a seguinte linha de código no exemplo de código:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ sNWind & ";"
Substitua essa 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 você só pode usá-lo com um conjunto de registros DAO. CopyFromRecordset com o Excel 97 não dá suporte ao ADO.
Para obter mais informações sobre como usar o ADO e o método CopyFromRecordset, consulte Como transferir dados de um conjunto de registros do ADO para o Excel com automação.
Usar a automação para criar uma ConsultaTable em uma planilha
Um objeto QueryTable representa uma tabela criada a partir de dados retornados de uma fonte de dados externa. Ao automatizar o Microsoft Excel, você pode criar uma ConsultaTable simplesmente fornecendo uma cadeia de conexão a um OLEDB ou a uma fonte de dados ODBC junto com uma cadeia de caracteres SQL. O Excel assume a responsabilidade de gerar o conjunto de registros e inseri-lo na planilha no local especificado. O uso de QueryTables oferece várias vantagens sobre o método CopyFromRecordset:
- O Excel manipula a criação do conjunto de registros e sua colocação na planilha.
- A consulta pode ser salva com a ConsultaTable para que ela possa ser atualizada posteriormente para obter um conjunto de registros atualizado.
- Quando uma nova QueryTable é adicionada à sua planilha, você pode especificar que os dados já existentes nas células na planilha sejam deslocados para acomodar os novos dados (consulte a propriedade RefreshStyle para obter detalhes).
O código a seguir demonstra como você pode automatizar o Excel 2000, o Excel 2002 ou o Office Excel 2003 para criar uma nova ConsultaTable em uma planilha do Excel usando 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
Usar a área de transferência
A Área de Transferência do Windows também pode ser usada como um mecanismo para transferir dados para uma planilha. Para colar dados em várias células em uma planilha, você pode copiar uma cadeia de caracteres em que as colunas são delimitadas por caracteres de guia e as linhas são delimitadas por retornos de transporte. O código a seguir ilustra como o Visual Basic pode usar seu 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 delimitado que o Excel possa analisar em linhas e colunas
O Excel pode abrir arquivos delimitados por guia ou vírgula e analisar corretamente os dados em células. Você pode aproveitar esse recurso quando quiser transferir uma grande quantidade de dados para uma planilha usando pouca, se houver, Automação. Essa pode ser uma boa abordagem para um aplicativo cliente-servidor porque o arquivo de texto pode ser gerado no lado do servidor. Em seguida, você pode abrir o arquivo de texto no cliente, usando Automação onde ele é apropriado.
O código a seguir ilustra como você pode criar um arquivo de texto delimitado por vírgulas de um conjunto de registros do 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
Observação Se você usar a versão do Office 2007 do banco de dados Northwind, deverá substituir a seguinte linha de código no exemplo de código:
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sNWind & ";"
Substitua essa linha de código pela seguinte linha de código:
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
sNWind & ";"
Se o arquivo de texto tiver uma extensão .CSV, o Excel abrirá o arquivo sem exibir o Assistente de Importação de Texto e assumirá automaticamente que o arquivo está delimitado por vírgula. Da mesma forma, se o arquivo tiver uma extensão .TXT, o Excel analisará automaticamente o arquivo usando delimitadores de guia.
No exemplo de código anterior, o Excel foi iniciado usando 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 assim desejar, você poderá 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
Transferir dados para uma planilha usando o ADO
Usando o Provedor OLE DB do Microsoft Jet, você pode adicionar registros a uma tabela em uma pasta de trabalho do Excel 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 linhas subsequentes contêm os registros. As etapas a seguir ilustram como você pode criar uma pasta de trabalho com uma tabela vazia chamada MyTable.
Excel 97, Excel 2000 e Excel 2003
Inicie uma nova pasta de trabalho no Excel.
Adicione os seguintes cabeçalhos às células A1:B1 de Sheet1:
A1: FirstName B1: LastName
Formatar a célula B1 como alinhada à direita.
Selecione A1:B1.
No menu Inserir, escolha Nomes e, em seguida, selecione Definir. Insira o nome MyTable e clique em OK.
Salve a nova pasta de trabalho como C:\Book1.xls e saia do Excel.
Para adicionar registros ao MyTable usando 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
Excel 2007
No Excel 2007, inicie uma nova pasta de trabalho.
Adicione os seguintes cabeçalhos às células A1:B1 de Sheet1:
A1: FirstName B1: LastName
Formatar a célula B1 como alinhada à direita.
Selecione A1:B1.
Na Faixa de Opções, clique na guia Fórmulas e clique em Definir Nome. Digite o nome MyTable e clique em OK.
Salve a nova pasta de trabalho como C:\Book1.xlsx e, em seguida, saia do Excel.
Para adicionar registros à tabela MyTable usando o ADO, use o código que se assemelha ao exemplo de código a seguir.
'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 você adiciona registros à tabela dessa maneira, a formatação na pasta de trabalho é mantida. No exemplo anterior, novos campos adicionados à coluna B são formatados com alinhamento correto. Cada registro adicionado a uma linha empresta o formato da linha acima dele.
Você deve observar que quando um registro é adicionado a uma célula ou células na planilha, ele substitui todos os dados anteriormente nessas células; em outras palavras, as linhas na planilha não são "pressionadas para baixo" quando novos registros são adicionados. Você deve ter isso em mente ao projetar o layout dos dados em suas planilhas.
Observação
O método para atualizar dados em uma planilha do Excel usando o ADO ou usando o DAO não funciona no ambiente Visual Basic for Application no Access depois de instalar o Office 2003 Service Pack 2 (SP2) ou depois de instalar a atualização do Access 2002 incluída no artigo da Base de Dados de Conhecimento da Microsoft 904018. O método funciona bem no ambiente do Visual Basic for Application de outros aplicativos do Office, como Word, Excel e Outlook.
Para obter mais informações, consulte o seguinte artigo:
Para obter mais informações sobre como usar o ADO para acessar uma pasta de trabalho do Excel, consulte Como consultar e atualizar dados do Excel usando o ADO do ASP.
Usar o DDE para transferir dados para o Excel
O DDE é uma alternativa à Automação como um meio de se comunicar com o Excel e transferir dados; no entanto, com o advento da Automação e da COM, o DDE não é mais o método preferencial para se comunicar com outros aplicativos e só deve ser usado quando não houver outra solução disponível para você.
Para transferir dados para o Excel usando o DDE, você pode usar o método LinkPoke para cutucar dados para um intervalo específico de células ou usar o método LinkExecute para enviar comandos que o Excel executará.
O exemplo de código a seguir ilustra como estabelecer uma conversa DDE com o Excel para que você possa cutucar dados em células em uma planilha e executar comandos. Usando este exemplo, para que uma conversa DDE seja estabelecida com êxito no LinkTopic Excel|MyBook.xls, uma pasta de trabalho com o nome MyBook.xls já deve ser aberta em uma instância em execução do Excel.
Observação
Ao usar o Excel 2007, você pode usar o novo formato de arquivo .xlsx para salvar as pastas de trabalho. Certifique-se de atualizar o nome do arquivo no exemplo de código a seguir. Neste exemplo, o Text1 representa um controle Text Box em 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 o LinkPoke com o Excel, você especifica o intervalo na notação de coluna de linha (R1C1) para o LinkItem. Se você estiver cutucando dados em várias células, poderá usar uma cadeia de caracteres em que as colunas são delimitadas por guias e linhas são delimitadas por retornos de transporte.
Quando você usa o LinkExecute para pedir ao Excel para executar um comando, você deve dar ao Excel o comando na sintaxe da XLM (Linguagem Macro do Excel). A documentação XLM não está incluída nas versões do Excel 97 e posteriores.
O DDE não é uma solução recomendada para se comunicar com o Excel. A automação fornece a maior flexibilidade e oferece mais acesso aos novos recursos que o Excel tem a oferecer.
Comentários
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulte https://aka.ms/ContentUserFeedback.
Enviar e exibir comentários de