Visual Basic/Excel - Importando e Exportando dados (inserindo dados, criando gráficos, convertendo tabela para Excel, acesso direto à planilha )

Para refrescar a memória sobre o assunto leia os artigos:

  1. OLE - Conceitos.
  2. Usando VBA
  3. Excel - Abrindo uma Planilha e Exportando p/ uma tabela

DDE , OLE , ActiveX ... Vamos direto a questão : " Como eu posso importar e exportar dados entre minha aplicação Visual Basic e o Excel ? "

A primeira coisa que você vai ter que fazer é uma comunicação entre as aplicações . O Excel estará funcionando como um servidor de dados ( ou cliente de dados ) .

Para efetivar esta comunicação você vai ter que criar um componente na sua aplicação VB que lhe permita acessar as propriedades e métodos do Excel.

Para instanciar um componente com essas propriedades você terá que conhecer quais os componentes que o Excel expõe. Damos abaixo um esquema bem simples do modelo exposto pelo Excel:

 No topo da hierarquia esta a classe Application

É ela que temos que instanciar em nossa aplicação Visual Basic para ter acesso as propriedades e métodos das demais classes expostas pelo Excel. 

Application - É a aplicação Excel

WorkBook  -  É o arquivo XLS 

WorkSheet - É a planilha Excel de trabalho

É claro que que para tudo isto funcionar corretamente , esta classe deverá estar registrada na máquina na qual a aplicação será executada . Trocando em miúdos : O Excel tem que estar instalado nesta máquina.

A primeira coisa a fazer ao iniciar o seu projeto no Visual Basic é habilitar a referência ao Excel dentro do seu projeto.

 Para isto, selecione References no menu Tools ou Project, dependendo da versão do VB, e marque uma opção semelhante a Microsoft Excel x.x Object Library, conforme mostra a figura a seguir:

Abrindo uma planilha Excel e exibindo o conteúdo de uma Célula

Vamos atacar  logo de cara com um código bem simples para abrir uma planilha Excel e obter o conteúdo de uma célula da planilha. Vamos devagar e sempre...

O nome do nosso arquivo será : Teste.xls

O nome da planilha será : Plan1

O arquivo será salvo no diretório : c:\teste

Agora vamos ao código para fazer este serviço :

    Dim xl As New Excel.Application
    Dim xlw As Excel.Workbook

    'Abrir o arquivo do Excel
    Set xlw = xl.Workbooks.Open("c:\teste\teste.xls")

    ' definir qual a planilha de trabalho
    xlw.Sheets("Plan1").Select

    'Exibe o conteúdo da célula na posição 2,3

   ' variavel = xlw.Application.Cells(2, 3).Value
    MsgBox xlw.Application.Cells(2, 3).Value

 

    ' Fechar a planilha sem salvar alterações
    ' Para salvar mude False para True

    xlw.Close False

    ' Liberamos a memória

    Set xlw = Nothing
    Set xl = Nothing

 Ao executar o código ao lado, você irá obter uma caixa de mensagem exibindo o conteúdo da célula da planilha Plan1 do arquivo teste.xls , localizada na linha 2 e coluna 3.

-Primeiro eu criei o componente Application do Excel e o atribui a variável objeto xl ( poderia ser outro nome )

-Depois eu criei a classe Workbook e atribui a variável objeto xlw

-A seguir abri o arquivo teste.xls atribuindo a xlw

-Defini a minha planilha de trabalho (Plan1)

-E , obtive o valor (Value) da célula (Cells) da planilha

-Para encerrar encerrei a aplicação e liberei memória.

Nota : Se você quiser trabalhar com o conteúdo da célula pode salvá-lo em uma variável para posterior tratamento(veja de codigo  variavel=xlw.Application.Cells(2, 3).Value )

Inserindo dados em uma planilha Excel e criando um Gráfico

Vamos para uma tarefa mais interessante:  Agora vamos criar uma planilha , inserir alguns valores e montar o gráfico dos valores inseridos e exibir o gráfico. 

Código da seção General Declarations :

Option Explicit
Dim EApp As Object
Dim EwkB As Object
Dim EwkS As Object		 Define as variáveis objetos que deverão ser visíveis em todo o formulário

Código do botão de comando CmdExell - Ativa o Excel insere dados nas células e cria o gráfico

Private Sub CmdExcel_Click()
Dim a, b As String
Dim i As Integer
Dim Exlc As New Chart

'
' Cria a componente da classe application
' inclui um novo arquivo e uma nova planilha
'
Set EApp = CreateObject("excel.application")
Set EwkB = EApp.Workbooks.Add
Set EwkS = EwkB.Sheets(1)
'
' exibe a aplicação Excel
'
EApp.Application.Visible = True

' Preenche a primeira e a segunda coluna
' com alguns valores numéricos
'
For i = 1 To 10
   a = "A"
   b = "B"
   Range(a & i).FormulaR1C1 = Str(i)
   Range(b & i).FormulaR1C1 = Str(i / 2)
Next i

'seleciona da célula b2 até a b10
Range("B2:B10").Select

'Cria e exibe o gráfico
Set Exlc = EApp.Charts.Add

'torna o formulário do VB visível para poder fechar o Excel
frmexcelvb.Show

End Sub 		-Define as variáveis locais

-Cria os componentes e atribui as variáveis objeto

-Torna o Excel Visivel

-Inclui valores nas células A1 a A10 e B2 a B10

-Selecione a faixa das células de B2 a B10

-Cria um gráfico de barras com esta seleção

-Torna o formulário do VB visivel

Abaixo as células com os valores e a seleção

Nota: Usamos a instrução CreatObject para criar o objeto Excel , embora Usar Dim com a cláusula New seja mais rápido . 

Código do botão de comando cmdSair - Encerra o Excel e a aplicação VB

Private Sub cmdsair_Click()

Me.Hide
' fecha o arquivo xls
'
EwkB.Close

' encerra o excel e sai

EApp.Application.Quit

End

End Sub		 - Esconde o formulário do VB

- Fecha o arquivo e encerra o Excel

Abaixo o gráfico gerado

 

 

 

 

 

 

 

 

 

 

Convertendo os dados de uma tabela em uma Panilha Excel

Que tal agora fazer algo mais prático :  Converter os dados de uma tabela em uma planilha. 

Vamos converter os dados da tabela Titles presente no banco de dados Biblio.mdb para em uma planilha Excel. Para fazer o serviço vamos usar uma função chamada CopiarTabelaExcel que irá obter os dados da tabela e criar a planilha Excel para receber os dados. (Article ID: Q172058 da Microsoft)

Estaremos a DAO para acessar o banco de dados e a tabela. Vamos criar um recordset do tipo SnapShot. Lembra disto ??? Não !!! Então leia o artigo - Recordsets : Tables, Dynasets e Snapshots.

O projeto :

Vamos ao código :

Código da seção General Declarations : Define variáveis 

Option Explicit
Dim oExcel As Object
Dim objExlSht As Object
Dim db As Database    
Dim Sn As Recordset   ' Recordset do tipo Snapshot

Private Type ExlCell
   row As Long
   col As Long
End Type		 -Define as variáveis objetos que deverão ser visíveis em todo o formulário tanto para o Excel como para o banco de dados 

 
 

Agora o código do botão de comando cmdExcel : Prepara o Excel , abre o Recordset e chama a função - CopiarTabelaExcel().

Sub CmdExcel_Click()
Dim stCell As ExlCell

MousePointer = vbHourglass ' Muda o ponteiro do mouse

Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add   'inclui o workbook
Set objExlSht = oExcel.ActiveWorkbook.Sheets(1)

Set db = OpenDatabase("c:\teste\BIBLIO.MDB")
Set Sn = db.OpenRecordset("Titles", dbOpenSnapshot)

' Inclui os dados a partir da celula A1
stCell.row = 1
stCell.col = 1
CopiarTabelaExcel Sn, objExlSht, stCell

' Salva a planilha
objExlSht.SaveAs "C:\teste\teste.xls"

oExcel.Visible = True
frmexcelvb.Show

End Sub

O código da Função CopiarTabelaExcel: A função recebe como parâmetros o Recordset, a planilha e a célula inicial e copia os registros para a planilha

Private Sub CopiarTabelaExcel(rs As Recordset, ws As Worksheet,  StartingCell As ExlCell)
Dim Vetor() As Variant
Dim row As Long, col As Long
Dim fd As Field

rs.MoveLast
ReDim Vetor(rs.RecordCount + 1, rs.Fields.Count)

' Copia as colunas do cabecalho para um vetor
col = 0
For Each fd In rs.Fields
  Vetor(0, col) = fd.Name
  col = col + 1
Next

' copia o rs par um vetor
rs.MoveFirst
For row = 1 To rs.RecordCount - 1
   For col = 0 To rs.Fields.Count - 1
       Vetor(row, col) = rs.Fields(col).Value
       'O Excel não suporta valores NULL em uma célula.
       If IsNull(Vetor(row, col)) Then Vetor(row, col) = ""
   Next
   rs.MoveNext
Next

ws.Range(ws.Cells(StartingCell.row, StartingCell.col),ws.Cells(StartingCell.row + rs.RecordCount + 1, _
StartingCell.col + rs.Fields.Count)).Value = Vetor

End Sub

Nota: Primeiro copiamos os nomes dos campos e depois o recordset para o array(Vetor); a seguir atribuimos os valores as células da planilha ( linha de código azul)

O código do botão cmdSair: Encerra o Excel e libera memória das variáveis objeto.

Private Sub cmdsair_Click()

Label1.Caption = "Encerrando o Excel"
Label1.Refresh
objExlSht.Application.Quit

Set objExlSht = Nothing   ' remove a variavel objeto
Set oExcel = Nothing       ' remove a variavel objeto
Set Sn = Nothing             ' reomove a variavel objeto
Set db = Nothing             ' reomove a variavel objeto

MousePointer = vbDefault     ' Restaura o ponteiro do mouse.
Label1.Caption = "Muito bem, deu certo ! "
Label1.Refresh

End Sub
A tabela Titles exportada para o Excel 

Agora eu vou mostrar como fazer o mesmo serviço usando código mais enxuto:  o método CopyFromRecordset do objeto Range.

O método  CopyFromRecordset do objeto Range é muito útil para importar dados de uma aplicação externa para uma planilha Excel.

O único problema porém, é que ele só funciona com a DAO e não suporta recordsets ADO.  Abaixo temos o código que faz o mesmo serviço do exemplo acima abordado.

Dim oExcel As Object
Dim objExlSht As Object
Dim Db1 As Database
Dim Rs1 As Recordset


Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add                                           'inclui o workbook
 Set objExlSht = oExcel.ActiveWorkbook.Sheets(1)

'Abrindo o banco de dados
Set Db1 = DBEngine.OpenDatabase("c:\teste\biblio.mdb")

'Criamos um recordset selecionando todos os campos da tabela Titles do banco de dados Biblio.mdb onde o código PubId for menor que 10

Set Rs1 = Db1.OpenRecordset(Name:="Select * from titles where PubId < 10", Type:=dbOpenDynaset)

'Limpamos qualquer informação existente na planiha e copiamos o recordset criado
With Worksheets("Plan1").Range("A1")
  .CurrentRegion.Clear
  .CopyFromRecordset Rs1
 End With

objExlSht.SaveAs "C:\teste\teste.xls"
oExcel.Visible = True

'Fechamos o banco de dados
Db1.Close

set db1=nothing
Usando DAO - método CopyFromRecordset

Como já disse , se voce quiser a ADO para acessar o banco de dados , não vai poder usar o método CopyFromRecordset , mas calma , podemos usar outro recurso o método Transpose.

A seguir o código que executa a mesma tarefa ( apenas restringimos a quantidade de registros usando uma instrução SQL  - Select Title From Titles WHERE pubID < 5 ORDER BY Title ).

Dim oExcel As Object
Dim objExlSht As Object

Dim Rs1 As ADODB.Recordset


Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Add 'inclui o workbook
Set objExlSht = oExcel.ActiveWorkbook.Sheets(1)


Set Rs1 = New ADODB.Recordset

Rs1.Open Source:="Select Title From Titles WHERE pubID < 5 ORDER BY Title", _
ActiveConnection:="DBQ=C:\TESTE\biblio.MDB;Driver={Microsoft Access Driver (*.mdb)};", _
CursorType:=adOpenStatic, _
LockType:=adLockOptimistic, _
Options:=adCmdText


With Worksheets("Plan1")
   .Range("A1").CurrentRegion.Clear
   Application.Intersect(.Range(.Rows(1), .Rows(Rs1.RecordCount)), _
   .Range(.Columns(1), .Columns(Rs1.Fields.Count))).Value = _
   Application.Transpose(Rs1.GetRows(Rs1.RecordCount))
End With

objExlSht.SaveAs "C:\teste\teste901.xls"
oExcel.Visible = True

Rs1.Close
Set Rs1 = Nothing
Usando ADO - método Transpose

Observe que:

- Não fizemos uma referência direta a um banco de dados no código , na verdade usamos somente o objeto Recordset da ADO

- Quando invocamos o método Open do objeto Recordset passamos a informação da conexão com o argumento ActiveConnection

- Usamos outros parâmetros do método Open (CursorType, LockType , Options)

- Os dados do recordset são organizados em um tabela de duas dimensões onde cada coluna representa um registro e cada linha representa um campo. Vamos então copiar os registros para as linhas e os campos para as colunas.

-  Usamos o método Getrows do objeto Recordset da ADO para gerar um array que é passado via método Transpose para a planilha.

Acessando diretamente uma planilha Excel com ADO

E se voce quiser acessar diretamente uma planilha do Excel a partir de sua aplicação Visual Basic ? Tem jeito ? 

Claro que tem . Vamos mostrar o acesso usando ADO primeiro usando um driver ODBC e depois usando OLE DB.

1-  Usando um drive ODBC para acessar uma planilha do Excel

Vamos abrir a planilha chamada teste.xls presente no diretório c:\teste   usando um driver ODBC e exibir três colunas da planilha. (Esta planilha foi gerada pela exportação da tabela Titles do banco de dados Biblio.mdb)

Para navegar pela 'planilha' vamos criar dois botões de comando : command1(<) - navega para trás  e command2 ( > ) - navega para frente ; quando o usuário clicar nos botões os valores serão exibidos nas caixas de texto.

Usaremos um terceiro botão - cmdAcessaExcel - para criar uma conexão com a planilha e gerar um recordset a partir do qual iremos acessar os dados da planilha. 

-Inicie um novo projeto no VB e insira os controles como indicados acima. Veja abaixo o layout do formulário do projeto:

Primeiro o código da seção General Declarations : Declaramos as variáveis objeto Connection e Recordset.

Dim oConn As ADODB.Connection
Dim ors As ADODB.Recordset

O código do evento Click do botão - cmdAcessaExcel - é o que vai fazer todo o serviço , vejamos :

Private Sub cmdAcessaExcel_Click()

' cria uma conexão ADO
Set oConn = New ADODB.Connection
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
                   "FIL=excel 8.0;" & _
                   "DefaultDir=C:\teste;" & _
                   "MaxBufferSize=2048;" & _
                   "PageTimeout=5;" & _  
                   "DBQ=C:\teste\Teste.xls;"

' Cria o Recordset
Set ors = New ADODB.Recordset

' abre o recordset pelo nome da planilha
 ors.Open "[Plan1$]", oConn, adOpenStatic, adLockBatchOptimistic, adCmdTable

preenche_controles

End Sub

Perceba que:

- Estamos usando o driver ODBC - Driver={Microsoft Excel Driver (*.xls)};

- Informamos o diretório de localização da panilha - "DefaultDir=C:\teste;" & _

- A fonte de dados será o arquivo  - "DBQ=C:\teste\Teste.xls;"

- O recordset foi criado com base na planilha - Plan1 -    ors.Open "[Plan1$]", oConn, adOpenStatic, adLockBatchOptimistic, adCmdTable

- A seguir invocamos a procedure preenche_controles que irá carregar as caixas de texto com os valores dos três registros que vamos exibir.

O código da Procedura Preenche_Controles :

Private Sub preenche_controles()

Text1.Text = ors(2)         'numero isbn
Text2.Text = ors(1)         'ano
Text3.Text = ors(0)         'nome publicação

End Sub


Onde: ors(2) é o campo ISBN  , ors(1) o campo  Year Published e ors(0) o campo Title. Poderiamos ter usado a sintaxe :  ors.fields(2) , ors.fields(1) e ors.fields(0)

Finalmente o código associado aos botões command1 ( < )   e   command ( > )  que permitem a navegação pela planilha:

Private Sub Command1_Click()
   ors.MovePrevious
  preenche_controles
End Sub
 		Private Sub Command2_Click()
  ors.MoveNext
  preenche_controles
End Sub
Botão command1 - vai para o registro anterior Botão command2  -  vai para o próximo registro

Agora é só executar , veja abaixo a tela com os dados da planilha exibidos no formulário:

2-  Usando um provedor OLE DB para acessar uma planilha do Excel

Vamos fazer o mesmo serviço usando um provedor OLE DB para acessar a planilha teste.xls que esta no diretório c:\teste.  A novidade é o botão Novo , onde podemos incluir dados diretamente na planilha ;

O código principal esta associado ao botão que carrega os dados da planilha. O layout do formulário é o seguinte:

- Aqui temos um botão que permite incluir registros 

- O total de registros é exibido na label1

 

Primeiro o código da seção General Declarations : Declaramos as variáveis objeto Connection e Recordset e command:

Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset

A seguir o código principal associado ao botão - Acessar Planilha Excel usando OLE DB.

Private Sub Command5_Click()
' abre uma conexao com a planilha excel
Set oConn = New ADODB.Connection

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=c:\teste\teste.xls;" & _
                     "Extended Properties=""Excel 8.0;HDR=Yes;"";"

' cria o objecto command e define a conexao ativa
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn

' abre a planilha
oCmd.CommandText = "SELECT * from [Plan1$]"

' cria o recordset com os dados
Set oRS = New ADODB.Recordset
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic

'exibe os dados
preenche_controles

End Sub

Nota : na string de conexão "HDR=Yes" significa que existe um cabeçalho na planilha referente as células e o provedor NÃO vai incluir a primeira linha da seleção no Recordset gerado. Para incluir esta linha defina "HDR=No".

O código do botão Novo e Atualizar permitem a inclusão de dados na planilha:

Private Sub Command3_Click()
 ' limpa as caixas de texto
 Text1.Text = ""
 Text2.Text = ""
 Text3.Text = ""
 Command3.Enabled = False
End Sub

Private Sub Command4_Click()
 'inclui o registro na planilha
 oRS.AddNew
 oRS(0).Value = Text3.Text 'titulo
 oRS(1).Value = Text1.Text 'isbn
 oRS(2).Value = Text2.Text 'ano
 oRS.Update
 Command3.Enabled = True
 preenche_controles
End Sub

O restante do código apenas permite a navegação pelos dados da planilha:

Private Sub Command1_Click()
   oRS.MovePrevious
   preenche_controles
End Sub

Private Sub Command2_Click()
   oRS.MoveNext
  preenche_controles
End Sub 

Private Sub Command7_Click()
  oRS.MoveLast
  preenche_controles
End Sub

Private Sub Command8_Click()
  oRS.MoveFirst
  preenche_controles
End Sub

Percebemos então que temos diversas formas de acessar as informações de uma planilha Excel usando o Visual Basic.  E para acessar uma planilha na Internet , como seria ???  (Ainda vamos mostrar como fazer...) 

Vamos ficando por aqui , voltaremos ao tema num futuro não muito distante...   até láaaaa....   

Veja os Destaques e novidades do SUPER DVD Visual Basic (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Quer aprender C# ??

Quer aprender os conceitos da Programação Orientada a objetos ?

Quer aprender o gerar relatórios com o ReportViewer no VS 2013 ?

  Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

José Carlos Macoratti