Alta Interação com Consultas de Referências Cruzadas

(1)

QUANDO TRABALHEI NUM BANCO PRIVADO HÁ ALGUNS

ANOS, A EQUIPE DE CONTROLE FINANCEIRO DE TI TINHA A

NECESSIDADE DE GERENCIAR O CONSUMO DE RECURSOS

DE TI PELAS VÁRIAS ÁREAS DA DIRETORIA, COMO CUSTOS

DE IMPRESSÃO E ALOCAÇÃO DE SERVIDORES. EIS

ALGU-MAS CARACTERÍSTICAS DO PROJETO QUE LEVANTEI:

- consultas com múltiplos critérios definidos on-line;

- acompanhamento evolutivo durante períodos

defini-dos on-line;

- extração desses dados para relatórios e planilhas;

- tempo de desenvolvimento rápido e eficiente;

- ambiente utilizado: Access 97.

Juntando essas informações ao meu conhecimento da época,

concluí que as consultas de referência cruzada ajudariam muito

no desenvolvimento do projeto e, utilizando o

Access, teria ainda diversas opções para o

ambi-ente do aplicativo que essa ferramenta

proporci-ona.

Um dos recursos mais importantes do Access (em

todas as versões) são as consultas de referência

cruzada. A visualização de dados de forma

agru-pada em 2 dimensões possibilita uma

experiên-cia mais rica do usuário com o sistema.

Compa-rativamente, o Excel, com seus recursos de

tabe-la dinâmica, oferece bem mais opções de

contro-le e formatação do que o Access, mas

precisáva-mos ir mais longe, padronizando o tratamento das

informações, otimizando e protegendo o banco

de dados e possibilitando uma série de interfaces

para os usuários. Nesta matéria, a idéia é mostrar

como utilizá-las com grande interação usando parâmetros

di-nâmicos. Pode-se usar o Access 97 ou superior, porém o

aplicativo disponível para essa matéria está na versão 2000.

Paulo Sergio Sarraino * Artigo para Access 97, 2000, XP, 2003 e Excel 97, 2000, XP, 2003

Pré-requisitos: Conhecimentos de Access e VBA

DISPONÍVEL PARA DOWNLOAD

PIVOTTABLES.EXE (ACCESS 2000)

Disponível para download em www.forumaccess.com.br

Alta Interação com Consultas de

Referências Cruzadas

Como no projeto da equipe de TI há variáveis específicas desse

ambiente, construí um exemplo mais simples para facilitar e

ilus-trar esta matéria. Acompanhe.

NOTA: Cabe ressaltar que, a partir da versão XP (2002), o Access

suporta a construção de Tabelas e Gráficos Dinâmicos de forma

muito parecida com o Excel. Mas isso é assunto para outra

ma-téria.

CONSULTA E RELACIONAMENTOS

Precisamos identificar no mínimo 3 campos para a estrutura:

Campo de Linha: identifica o cabeçalho de linha (quantos campos

desejar)

Campo de Coluna: identifica o cabeçalho de coluna (apenas 1 campo) Campo de Valor: o cálculo da consulta (apenas 1 campo)

Veja a estrutura de uma consulta de referência cruzada:

Veja o comando SQL da mesma consulta:

cálculo do valor TRANSFORM Sum(Nz([dblPreco],0)*Nz([dblQuantidade],0)) AS Total

cabeçalhos de SELECT tbl_Grupo.nomGrupo AS Grupo, 2 AS TipoRegistro, linha Space(4) & tbl_Produto.nomProduto AS Produto relacionamentos FROM tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN entre as tabelas qry_Periodos ON tbl_Produto.codProduto =

(2)

próprios dados. Não tratarei aqui as interfaces para entrada

des-ses dados.

Para otimizar a performance das tabelas, acesse a janela de

Re-lacionamentos e defina as ligações entre as tabelas conforme o

desenho a seguir:

SOBRE FORMULÁRIOS E SEUS CONTROLES

Os dados do sistema referem-se a consumo de produtos e

pre-ços praticados. Com eles poderemos consultar:

• A quantidade de produtos vendidos pelo tempo

• Os preços praticados para os produtos vendidos pelo tempo

• O volume de vendas dos produtos (Preço x Quantidade) pelo

tempo

• Sub-totais por Grupo de Produtos

• Um gráfico de barras mostrando a relação entre os Produtos

• Um gráfico tipo pizza mostrando a relação de um Produto com

toda a base de dados

Vamos ver a estrutura do formulário principal de consulta aos

dados:

Para cada parametrização das consultas, criamos controles

com tratamentos e validações.

Figura 2 – Definindo o relacionamento

Figura 3 – Formulário principal

qry_Periodos.codProduto) LEFT JOIN tbl_Consumo ON (qry_Periodos.codProduto = tbl_Consumo.codProduto) AND (qry_Periodos.datPeriodo = tbl_Consumo.datPeriodo)) LEFT JOIN tbl_Preco ON (qry_Periodos.codProduto =

tbl_Preco.codProduto) AND (qry_Periodos.datPeriodo = tbl_Preco.datPeriodo)) ON tbl_Grupo.codGrupo = tbl_Produto.codGrupo

filtro WHERE tbl_Grupo.codGrupo In (1)

agrupamentos GROUP BY tbl_Grupo.nomGrupo, 2, Space(4) & tbl_Produto.nomProduto

classificação ORDER BY tbl_Grupo.nomGrupo, Space(4) & tbl_Produto.nomProduto

cabeçalho de PIVOT Format([qry_Periodos].[datPeriodo],'mm/yyyy') In

coluna (03/2005);

Para nosso projeto precisaremos das tabelas abaixo:

tbl_Grupo

Campo Tipo Descrição

codGrupo AutoNumeração [chave] Identifica o Grupo de Produtos

nomGrupo Texto (30) Nome do Grupo de Produtos

tbl_Produto

codProduto AutoNumeração [chave] Identifica o Produto

nomProduto Texto (30) Nome do Produto

codGrupo Número (Inteiro) Identifica o Grupo do Produto

tbl_Preço

codProduto Número (Inteiro) [chave] Identifica o Produto

datPeriodo Data [chave] Período do Preço

dblPreco Número (Duplo) Preço do Produto

tbl_Consumo

codProduto Número (Inteiro) [chave] Identifica o Produto

datPeriodo Data [chave] Período do Consumo

dblQuantidade Número (Duplo) Quantidade consumida do Produto

Também será necessária uma consulta especial: qun_Periodos.

Ela une os períodos registrados nas tabelas de Preços e

Consu-mo para terConsu-mos todos os itens disponíveis para a pesquisa de

dados. Nesse projeto, esse tipo de consulta previne pesquisas

de consumo sem preço e vice-versa. A ausência desses dados é

tratada com zeros nas consultas finais.

Precisaremos também carregar os dados. Você pode usar os

dados do exemplo disponível para essa matéria ou gerar seus

(3)

O grupo de opção grpOrient (identificado pela

legenda “Por...”) permite definir o Valor da

nos-sa pesquinos-sa, pode-se selecionar Preços ou

Con-sumo para uma pesquisa simples na base de

da-dos ou Vendas para realizar o cálculo Preços x

Consumo e assim termos uma evolução de

Ven-das dos nossos dados.

A caixa de listagem lstGrupo (identificado pela

legenda “Grupos:”) exibe os grupos de

produ-tos cadastrados na base e permite selecioná-los

para filtrar os dados pesquisados, pode-se

se-lecionar múltiplos itens para a pesquisa.

A caixa de listagem lstPeriodo (identificado pela

legenda “Período:”) exibe os períodos

cadas-trados na base através da consulta

qun_Periodos e permite selecioná-los para os

cabeçalho de coluna da consulta, pode-se

se-lecionar múltiplos itens para a pesquisa. Essa

seleção múltipla das caixas de listagem é

trata-da pelas caixas de texto ocultas strGrupo e

strPeriodo que, usando a função GetListItems

(controle, delimitador), retorna os itens selecionados

pre-parados para uso, por exemplo, em cláusulas In() de strings

do SQL.Como a quantidade de períodos disponíveis tende

a aumentar e esse critério será “casado” com as caixas de

texto dos subformulários, limitou-se a leitura desses

da-dos na geração das consultas da seguinte forma: para

visualização em tela pode-se selecionar até 49 períodos e

para visualização em relatórios apenas 12 períodos. Porém,

esses limites podem ser redefinidos conforme a

necessida-de do seu aplicativo.

O botão Pesquisar realiza a validação dos parâmetros e

cha-ma a rotina “AlteraRefCz” (detalhada adiante) que está em

cada subformulário da tela. A alteração de qualquer dos

con-troles citados solicita o recálculo dos dados.

Os subformulários interagem também com os botões

disponibilizados no rodapé do formulário.

O controle frm_Evolucaod_sub exibe os dados e subtotais

por Grupo ou um dos gráficos solicitados.

O controle frm_Evolucaot_sub exibe os totais gerais dos

dados (para os gráficos ele fica invisível).

Os subformulários de dados e de totais têm a mesma

estru-tura, diferenciando apenas a geração dos dados, veja

abai-xo. Existem 50 campos com suas respectivas legendas

identificadas como “Legenda1...Legenda50” e “Campo1...

Campo50” que serão exibidos ou não conforme o

resulta-do da consulta gerada. Para o layout ser ajustável a essa

estrutura é necessário definir sua propriedade “Modo

Pa-drão” como “folha de dados”. Apesar da disposição dos

campos na estrutura do formulário parecer irrelevante, ela

facilita a montagem da ordem de tabulação dos controles

que define o posicionamento dos campos na visualização

“folha de dados” do subformulário.

Dentro do formulário, como dito antes, há uma única função,

“AlteraRefCz”. Ela realiza todo o trabalho de formatação dos

campos, consulta aos dados e totalizações.

Option Compare Database Option Explicit

Function AlteraRefCz()

On Error GoTo AlteraRefCz_Err

'analisa, define e formata o formulário com os campos da consulta de ref. cruzada

Dim i As Integer 'contador para campos

Dim nmCampo As String 'variável para nome de campo Dim strSQL As String 'string SQL

Dim vlTotal As String 'identifica o valor desejado para a consulta Dim totPrd As String 'string para acumulado dos períodos

'esconde colunas For i = 1 To 50 With Me("Campo" & i) .ControlSource = "" .ColumnHidden = True End With

Me("Legenda" & i).Caption = "" Next

'esconde/exibe formulário If (Me.Parent!strPeriodo = "") Then Me.Visible = False

(4)

Application.Echo True Exit Function Else Me.Visible = True End If Me.RecordSource = ""

Select Case Me.Parent!grpOrient Case 1: vlTotal = "Nz([dblPreco],0)" Case 2: vlTotal = "Nz([dblQuantidade],0)"

Case 3: vlTotal = "Nz([dblPreco],0)*Nz([dblQuantidade],0)" End Select

vlTotal = "Sum(" & vlTotal & ")"

strSQL = "TRANSFORM " & IIf(Me.Parent!grpOrient = 3, vlTotal, 0) & " AS Total " & _

"SELECT tbl_Grupo.nomGrupo AS Grupo, 1 AS TipoRegistro, tbl_Grupo.nomGrupo AS Produto " & _

"FROM tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN qun_Periodos ON tbl_Produto.codProduto = qun_Periodos.codProduto) LEFT JOIN tbl_Consumo ON (qun_Periodos.datPeriodo = tbl_Consumo.datPeriodo) AND (qun_Periodos.codProduto = tbl_Consumo.codProduto)) LEFT JOIN tbl_Preco ON (qun_Periodos.datPeriodo = tbl_Preco.datPeriodo) AND (qun_Periodos.codProduto = tbl_Preco.codProduto)) ON tbl_Grupo.codGrupo = tbl_Produto.codGrupo " & _

"WHERE tbl_Grupo.codGrupo In (" & Me.Parent!strGrupo & ") " & _ "GROUP BY tbl_Grupo.nomGrupo, 1, tbl_Grupo.nomGrupo " & _ "ORDER BY tbl_Grupo.nomGrupo, tbl_Grupo.nomGrupo " & _ "PIVOT Format([qun_Periodos].[datPeriodo],'mm/yyyy') In (" & Me.Parent!strPeriodo & ");"

With CurrentDb.QueryDefs("qrc_Evolucao_grupos") .SQL = strSQL

.Close End With

strSQL = "TRANSFORM " & vlTotal & " AS Total " & _

"SELECT tbl_Grupo.nomGrupo AS Grupo, 2 AS TipoRegistro, Space(4) & tbl_Produto.nomProduto AS Produto " & _

"FROM tbl_Grupo INNER JOIN (((tbl_Produto INNER JOIN qun_Periodos ON tbl_Produto.codProduto = qun_Periodos.codProduto) LEFT JOIN tbl_Consumo ON (qun_Periodos.datPeriodo = tbl_Consumo.datPeriodo) AND (qun_Periodos.codProduto = tbl_Consumo.codProduto)) LEFT JOIN tbl_Preco ON (qun_Periodos.datPeriodo = tbl_Preco.datPeriodo) AND (qun_Periodos.codProduto = tbl_Preco.codProduto)) ON tbl_Grupo.codGrupo = tbl_Produto.codGrupo " & _

"WHERE tbl_Grupo.codGrupo In (" & Me.Parent!strGrupo & ") " & _ "GROUP BY tbl_Grupo.nomGrupo, 2, Space(4) &

tbl_Produto.nomProduto " & _

"ORDER BY tbl_Grupo.nomGrupo, Space(4) & tbl_Produto.nomProduto " & _

"PIVOT Format([qun_Periodos].[datPeriodo],'mm/yyyy') In (" & Me.Parent!strPeriodo & ");" With CurrentDb.QueryDefs("qrc_Evolucao_dados") .SQL = strSQL .Close End With Me.RecordSource = "qun_Evolucao"

'atribui todos os campos da consulta ao formulário (campo e legenda) For i = 1 To Me.RecordsetClone.Fields.Count

With Me("Campo" & i)

nmCampo = Me.RecordsetClone.Fields(i - 1).Name .ColumnHidden = False .ColumnOrder = i Select Case i Case 1, 2 .ColumnHidden = True .ControlSource = nmCampo Case 3 .TextAlign = 1 'esquerda .ColumnWidth = 2000 .ControlSource = nmCampo Case Is >= 4 .TextAlign = 3 'direita .ColumnWidth = 1500

.ControlSource = "=IIf((Campo1=Campo3) And (Parent!grpOrient <> 3), Null, Format([" & nmCampo & "],'Standard'))"

totPrd = totPrd & "CDbl(Nz([" & nmCampo & "],0))+" End Select

.FormatConditions.Delete

.FormatConditions.Add acExpression, , "Campo1=Campo3" .FormatConditions(0).FontBold = True

End With

Me("Legenda" & i).Caption = nmCampo Next

'monta coluna de acumulado de produto totPrd = Mid(totPrd, 1, Len(totPrd) - 1)

With Me("Campo" & i)

.ControlSource = "=IIf((Campo1=Campo3) And (Parent!grpOrient <> 3), Null, Format(" & totPrd & ",'Standard'))"

.ColumnHidden = False .ColumnOrder = i + 50 .ColumnWidth = 1500 .TextAlign = 3 'direita

(5)

.FormatConditions.Add acExpression, , "Campo1=Campo3" .FormatConditions(0).FontBold = True

End With

Me("Legenda" & i).Caption = "Acumulado" Exit Function

AlteraRefCz_Err:

Application.Echo True

MsgBox "Ocorreu um erro:" & vbCrLf & _

Err.Number & " - " & Err.Description, vbCritical, "Atenção!"

End Function

No rodapé temos também o botão “Visualizar Relatórios”, que

chama o relatório de dados ou de gráfico conforme o item

visualizado no formulário e o botão “Congelar Colunas” que

realiza a ação de congelar a primeira coluna visualizada na

pes-quisa de dados a fim de facilitar a rolagem horizontal da tela.

Private Sub cmdCongelar_AfterUpdate()

Dim ncol As Integer 'quantidade de colunas a congelar

ncol = 1

If Me.frm_Evolucaod_sub.SourceObject <> "frm_Evolucao_dados" Then Me.cmdCongelar = 0

Exit Sub End If

If Me.cmdCongelar = True Then

With Me.frm_Evolucaod_sub .SetFocus CongelaColuna (ncol) End With With Me.frm_Evolucaot_sub If Me.grpOrient <= 2 Then .Visible = False Else .SetFocus CongelaColuna (ncol) End If End With Else With Me.frm_Evolucaod_sub .SetFocus DoCmd.RunCommand acCmdUnfreezeAllColumns End With If Me.grpOrient > 2 Then With Me.frm_Evolucaot_sub .SetFocus DoCmd.RunCommand acCmdUnfreezeAllColumns End With End If End If End Sub

Sub CongelaColuna(qtcol As Byte)

'congela a quantidade de colunas informada para _ facilitar rolagem horizontal por vários períodos

On Error Resume Next

Select Case qtcol Case Is <= 0: Exit Sub Case 1:

DoCmd.RunCommand acCmdUnfreezeAllColumns SendKeys "{TAB}", True

SendKeys "{HOME}", True SendKeys "^{ }", True

DoCmd.RunCommand acCmdFreezeColumn SendKeys "{RIGHT}", True

Case Else

DoCmd.RunCommand acCmdUnfreezeAllColumns SendKeys "{TAB}", True

SendKeys "{HOME}", True SendKeys "^{ }", True SendKeys "+{RIGHT}", True

DoCmd.RunCommand acCmdFreezeColumn SendKeys "{RIGHT 2}", True

End Select

End Sub

SOBRE RELATÓRIOS E SEUS CONTROLES

A estrutura do relatório segue o mesmo princípio dos

for-mulários de dados. Ele é todo desvinculado, e no evento

Open ocorre toda a carga e formatação de campos. Perceba

que também aplicamos os recursos de Formatação

Condi-cional para ressalvar as linhas com os dados de Grupos de

Produtos.

(6)

Private Sub Report_Open(Cancel As Integer)

Dim rstD As Recordset, rstT As Recordset Dim i As Integer 'contador

Dim lim As Integer 'limite de períodos pra exibição Dim nmCampo As String 'variável para nome de campo Dim totPrd As String 'string para acumulado do produto

If Forms!frm_Evolucao!strPeriodo = "" Then Cancel = True Exit Sub End If Me.RecordSource = Forms!frm_Evolucao!frm_Evolucaod_sub.Form.RecordSource Set rstD = Forms!frm_Evolucao!frm_Evolucaod_sub.Form.RecordsetClone If Forms!frm_Evolucao!grpOrient = 3 Then Me.ReportFooter.Visible = True Set rstT = Forms!frm_Evolucao!frm_Evolucaot_sub.Form.RecordsetClone Else Me.ReportFooter.Visible = False End If With Me.Produto .FormatConditions.Delete

.FormatConditions.Add acExpression, , "Grupo=Produto" .FormatConditions(0).FontBold = True

End With

'monta colunas de dados With rstD

lim = .Fields.Count - 3 If lim > 12 Then lim = 12

For i = 1 To lim

nmCampo = .Fields(i + 2).Name Me("txtMes" & i).Caption = nmCampo With Me("valMes" & i)

.ControlSource = "=IIf((Grupo=Produto) And

(Forms!frm_Evolucao!grpOrient <> 3), Null, Format([" & nmCampo & "],'Standard'))"

End With

totPrd = totPrd & "CDbl(Nz([" & nmCampo & "],0))+" Next

End With

'monta Acumulado

totPrd = Mid(totPrd, 1, Len(totPrd) - 1) Me.txtAcum.Caption = "Acumulado"

With Me.valAcum

.ControlSource = "=IIf((Grupo=Produto) And

(Forms!frm_Evolucao!grpOrient <> 3), Null, Format(" & totPrd & ",'Standard'))" .FormatConditions.Delete

End With

'monta colunas de totais

If Forms!frm_Evolucao!grpOrient = 3 Then totPrd = ""

With rstT

lim = .Fields.Count - 3 If lim > 12 Then lim = 12

For i = 1 To lim

nmCampo = .Fields(i + 2).Value

Me("totMes" & i).ControlSource = "=Format(" & nmCampo & ",'Standard')"

totPrd = totPrd & "CDbl(Nz(" & nmCampo & ",0))+" Next

End With

'monta Acumulado

totPrd = Mid(totPrd, 1, Len(totPrd) - 1)

Me.totAcum.ControlSource = "=Format(" & totPrd & ",'Standard')" End If

End Sub

Se algum dos gráficos estiver sendo visualizado, o sistema

cha-mará o relatório do gráfico correspondente, porém, para eles, a

definição dos dados para o gráfico não pode ser feita da mesma

forma (via evento Open), por isso a rotina do botão “Visualizar

Relatórios” do formulário tem mais ações além do OpenReport

que imaginamos. Veja em seguida:

(7)

Private Sub cmdVisualizar_Click()

Dim i As Integer 'contador para campos Dim matPeriodos As Variant 'matriz dos períodos Dim strPeriodos As String 'string dos períodos

Select Case Me.frm_Evolucaod_sub.SourceObject

Case "frm_Evolucao_dados"

DoCmd.OpenReport "rpt_Evolucao", acViewPreview

Case "frm_Evolucao_grafBarra"

'abro a estrutura do relatório e altero a consulta do gráfico antes de visualizar os dados

Application.Echo False

DoCmd.OpenReport "rpt_Evolucao_grafBarra", acViewDesign matPeriodos = Split(Forms!frm_Evolucao!strPeriodo, ",") For i = 0 To UBound(matPeriodos)

strPeriodos = strPeriodos & "[" & matPeriodos(i) & "]," Next

strPeriodos = Left(strPeriodos, Len(strPeriodos) - 1)

Reports!rpt_Evolucao_grafBarra!grfProdutos.RowSource = "SELECT Trim(qrc_Evolucao_dados.Produto) AS Produto, " & strPeriodos & _ "FROM

qrc_Evolucao_dados;" With DoCmd .SetWarnings False

.Close acReport, "rpt_Evolucao_grafBarra" .SetWarnings True

.OpenReport "rpt_Evolucao_grafBarra", acViewPreview End With

Case "frm_Evolucao_grafPizza"

'abro a estrutura do relatório e altero a consulta do gráfico antes de visualizar os dados

Application.Echo False

DoCmd.OpenReport "rpt_Evolucao_grafPizza", acViewDesign matPeriodos = Split(Forms!frm_Evolucao!strPeriodo, ",") For i = 0 To UBound(matPeriodos)

strPeriodos = strPeriodos & "[" & matPeriodos(i) & "]+" Next

strPeriodos = Left(strPeriodos, Len(strPeriodos) - 1)

Reports!rpt_Evolucao_grafPizza!grfProdutos.RowSource =

"SELECT Trim(qrc_Evolucao_dados.Produto) AS Produto, " & strPeriodos & " AS Período " & _

"FROM qrc_Evolucao_dados;" With DoCmd

.SetWarnings False

.Close acReport, "rpt_Evolucao_grafPizza" .SetWarnings True

.OpenReport "rpt_Evolucao_grafPizza", acViewPreview End With

End Select

End Sub

EXPORTANDO OS DADOS

Através da propriedade “Barra de menus” dos relatórios,

chamamos um menu personalizado construído através de

macros de menu. Geralmente utilizo esse recurso para

disponibilizar diversas opções para qualquer relatório de

um sistema.

Aqui temos comandos como: Configurar Impressora, a

im-pressão propriamente dita, envio do relatório para o Excel

e o Word (através dos métodos TransferText e Transfer

SpreadSheet), visualização do relatório com 1 ou 2

pági-nas e o comando mais interessante desse menu chamado

“Interface Excel”.

Esse comando chama uma rotina “rstExcel”, onde, através do

método CopyFromRecordset do Excel, passo o RecordSource

do Relatório aberto para o Excel e realizo diversas formatações

automatizadas via VBA. Mas para utilizar esses recursos é

ne-cessário adicionar a referência para os objetos do Excel, como

mostro na tela seguinte:

(8)

A utilização do método CopyFromRecordset é um exemplo das

opções disponíveis para tratamento de dados no Excel e pode

ser vista na função listada a seguir:

Function rstExcel()

'transfere o recordset do relatório ativo para o Excel _ utilizando o método CopyFromRecordset

Dim rst As Recordset Dim intMaxCol As Integer Dim intMaxRow As Integer Dim appExcel As Excel.Application Dim i As Integer Set rst = CurrentDb.OpenRecordset(Reports(0).RecordSource, dbOpenSnapshot) intMaxCol = rst.Fields.Count If rst.RecordCount > 0 Then rst.MoveLast: rst.MoveFirst intMaxRow = rst.RecordCount + 1 Set appExcel = New Excel.Application With appExcel

.Visible = True

ReDim vaTmp(1 To rst.Fields.Count) For i = 1 To rst.Fields.Count vaTmp(i) = rst.Fields(i - 1).Name Next

.Workbooks.Add

.Cells(1, 1).Resize(1, rst.Fields.Count) = vaTmp .Range("A2").Select

.Range(.Cells(2, 1), .Cells(intMaxRow, intMaxCol)).CopyFromRecordset rst

'formatação conforme conteúdo do campo For i = 1 To rst.Fields.Count - 1

Select Case rst.Fields(i).Type

Case dbDate: .Columns(i + 1).NumberFormat = "dd/mm/ yyyy"

Case dbByte: .Columns(i + 1).NumberFormat = "General" Case dbDouble: .Columns(i + 1).NumberFormat = "#,##0.00"

End Select Next

'exclusão de colunas e campos zerados .Columns("A:B").Delete

.Range("A1").Select

.Range(.Selection, .Selection.End(xlToRight)).Select .Range(.Selection, .Selection.End(xlDown)).Select .DisplayAlerts = False

.Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False .DisplayAlerts = True 'formatação da planilha .Range("B1").Select .Range(.Selection, .Selection.End(xlToRight)).Select .Selection.NumberFormat = "mm/yyyy" .Rows(1).Font.Bold = True .Range("A2").Select .ActiveWindow.FreezePanes = True .Cells.Select .Cells.EntireColumn.AutoFit .Range("A2").Select End With End If End Function

GRÁFICOS

Como dito antes, os botões de gráficos do sistema

permi-tem chamar os subformulários preparados para essa

estru-tura.

Os dados também são atualizados pela seleção de

parâmetros do formulário, acessando a função “AlteraRefCz”

do subformulário de gráfico. Com poucos ajustes, a rotina

segue as mesmas estruturas de geração de consultas e

atu-alização de dados. A formatação do gráfico foi feita

direta-mente no objeto.

(9)

CONCLUSÃO

O aplicativo original, que deu a idéia para essa matéria, está

em uso desde 2002 com os usuários muito satisfeitos com

as possibilidades geradas e os resultados desejados

supe-rados. As consultas e gráficos disponíveis já auxiliaram

di-versas decisões da diretoria e o acompanhamento evolutivo

dos dados permitiu alertar os usuários sobre quaisquer

os-cilações da utilização de recursos da área de TI.

Com uma estrutura simples, rápida e eficiente, o aplicativo

gerencia atualmente uma base de dados de

aproximadamen-te 60.000 registros por mês.

Espero que essa matéria ilumine o caminho de diversos

pro-Figura 8 - Gráfico de barras

Figura 9 - Gráficos do tipo pizza

jetos semelhantes para serem aprimorados com as técnicas

aqui apresentadas. Fiquem à vontade para mandar idéias,

sugestões e críticas.

Bom estudo a todos!

* Paulo Sergio Sarraino (pss@sarraino.com.br) é analista é sócio da Sarraino

Systems. Possui graduação de Tecnologia em Processamento de Dados pela FASP (1997) e pós-graduação em Análise de Sistemas c/ Ênfase em Gerência de Projetos pela FIAP (2000). Atua como consultor em Análise e Desenvolvi-mento de Sistemas em ambiente MS Office e Internet (especialista em Access, VBA e ASP) há 10 anos.