Excel e VB - Importando e Exportando Dados

30/7/2014 Excel e VB - Importando e Exportando dados

http://www.macoratti.net/excel_vb.htm 1/18

Excel - Visual Basic - Importando eExportando dados

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 míú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

http://www.geocities.com/macoratti/ole.htm

http://www.geocities.com/macoratti/vba_use.htm

http://www.geocities.com/macoratti/dica11.htm



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...

Crie uma planilha no Excel e coloque qualquer informação na célula da linha 2 e

coluna 3. Eu estarei criando o arquivo texte.xls e na planilha Plan1 na célula da linha 2

, coluna 3 estarei informando : Oi eu aqui (salvos no diretório c:\teste) veja figura

abaixo:

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 :

Ao executar o

código ao lado,

você irá obter

uma caixa de

mensagem

exibindo o



Ad by ShopDriop | Close This Ad

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

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.

http://advertising-support.com/why.php?type=3&zone=287609&pid=1649&ext=ShopDriop



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.

Inicie um novo projeto no Visual Basic

Faça referência a livraria Microsof Excel X.0 Object Library

Insira no formulário dois botões de comando : cmdExcel e cmdSair

Insira o código no formulário conforme explicado a seguir:

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

-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




Private Sub

cmdsair_Click()

Me.Hide

' fecha o arquivo xls

'

EwkB.Close

- Esconde o formulário do VB

- Fecha o arquivo e encerra o

Excel

Abaixo o gráfico gerado

' 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

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



' encerra o excel e sai

EApp.Application.Quit

End

End Sub

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 :

Inicie um novo projeto no Visual Basic

Faça referência a livraria Microsof Excel X.0 Object Library

Insira no formulário dois botões de comando : cmdExcel e cmdSair e uma etiqueta

label1.(para exibir mensagens)

Insira o código no formulário conforme explicado a seguir:

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().

http://www.geocities.com/macoratti/record.htm




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.

Close


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 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"


'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 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"


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....

Gostou ? Compartilhe no Facebook Compartilhe no Twitter

Veja os Destaques e novidades do SUPER DVD

Visual Basic (sempre atualizado) : clique e

confira !

Quer migrar para o VB .NET ?

Veja mais sistemas completos para a

plataforma .NET no Super DVD .NET , confira...

Quer APRENDER C# ??

Chegou o Super DVD C# com exclusivo material

de suporte e vídeo aulas com curso básico sobre

C#.

http://www.macoratti.net/destaque.htm

http://www.macoratti.net/destaques.htm

http://www.macoratti.net/superdvd.htm

http://www.macoratti.net/superdvdc.htm



Veja também os Cursos com v ídeo aulas e

projetos exemplos:

VB .NET Básico - Vídeo Aulas

C# Básico - Vídeo Aulas

Referências:

Seção VB .NET do Site Macoratti.net

Super DVD .NET - A sua porta de entrada na plataforma .NET

Super DVD Vídeo Aulas - Vídeo Aula sobre VB .NET, ASP .NET e C#

Seção C# do site Macoratti.net

Seção Visual Basic 6 do site Macoratti .net

José Carlos Macoratti

http://www.macoratti.net/curso_vbnet_basico.htm

http://www.macoratti.net/curso_cshp_basico.htm

http://www.macoratti.net/pageview.aspx?catid=1

http://www.macoratti.net/superdvd.htm

http://www.macoratti.net/Vendas/Produtos.aspx?ID=22



http://www.macoratti.net/

Documents

Excel e VB - Importando e Exportando Dados