Apresenta aqui um curso rápido e introdutório ao conceitos de tabela dinâmica no Excel. Esta introdução é hospedada no site MS Office Gurus em inglês e deve ser acessado no seguinte link: http://www.msofficegurus.com/uploads/SCFXL03_TD_VID_SAMPLES/T21/T21_training.htm



Esta primeira parte faz parte do curso completo de tabelas dinâmicas disponibilizado em nossa loja: http://loja.msofficegurus.com/como-fazer-excel-2003-treinamento-interativo-tabelas-dinamicas.html

Não deixe de fazer os nossos test drives!

Neste tópico veremos alguns dos objetos importantes do ADO tais como “Connection” e “Recordset” assim como propriedades e métodos destes objetos os quais utilizamos para efetuar tarefas em nosso banco de dados.

Inicio pela conexão, pois nós precisamos de uma conexão aberta para abrir conjuntos de registros (recordsets) e efetuar operações em registros e tabelas como inserção, exclusão e edição de registros.

Vejamos então como abrir uma conexão em ADO.

Via de regra, para abrir um conexão ADO nós utilizamos o método Open de um objeto Connection. A parte importante aqui é a string de conexão. Conforme já explicado, podemos obter tal string diretamente do banco de dados através da propriedade Connection do objeto CurrentProject. Feito isso, basta copiar a string para utilização no seu projeto Excel.

Uma outra alternativa é utilizar um Data Link. Data Link será discutido mais adiante no curso.

Se a fonte de dados muda, então uma string é mais apropriada, pois não está ligada a uma fonte específica, bastando apenas adaptar conforme necessário (ou criar uma função para retornar a string dado certos parâmetros). De qualquer modo, a decisão final ficará a cargo mesmo de quem programa.

Para abrir a conexão podemos utilizar o seguinte código:

Sub conexao()

    Dim cn      As ADODB.Connection

    Dim strCn   As String

    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"

    strCn = strCn & "Data Source=C:\Northwind.mdb;"

    Set cn = New ADODB.Connection

    cn.Open strCn

End Sub

Note que acima, primeiramente declaramos o objeto, em seguida o instanciamos e finalmente o abrimos utilizando o método Open  do objeto Connection para abrir a conexão. Uma alternativa a isso é simplesmente declarar e instanciar ao meu tempo e logo a seguir abrir a conexão:

Sub conexao()

    Dim cn      As New ADODB.Connection

    Dim strCn   As String

    strCn = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;"

    strCn = strCn & "Data Source=C:\Northwind.mdb;"

    cn.Open strCn

End Sub

Uma vez que a conexão esteja aberta e tenhamos terminado o seu uso, nós precisamos fazer duas coisas com ela:

1.Fechá-la

2.Limpá-la da memória

Para fechar uma conexão nós utilizamos o método Close do objeto. Para limpar o objeto da memória nós o setamos como sendo Nothing (nada):

cn.Close

Set cn = Nothing

Portanto, o processo completo entre declaração, abertura, fechamento e limpeza da conexão ficará:

Sub conexao()

    Dim cn      As New ADODB.Connection

    cn.Open strCn

'   Executaremos algo neste espaço

    cn.Close

    Set cn = Nothing

End Sub

Agora que o leitor já sabe como efetuar os passos acima, vejamos como abrir um recordset (conjunto de registros) o qual depende de uma conexão aberta para ser viável.

Bem-vindo ao Curso Excel 2003 online totalmente grátis sobre o ADO (ActiveX Data Objects) e ADOX. Com esta séria você poderá montar a sua apostila de Excel 2003 sobre ADO/ADOX. Caso o leitor não queira imprimir o material, objetivando a economia de papel, o meio ambiente agradecerá a grande ajuda.

Um ponto importante sobre este curso é que ele não virá com as planilhas Excel prontas. O leitor terá que construir as planilhas a partir do curso. Caso o leitor queira, ele pode obter o curso completo em nossa loja no seguinte endereço: http://loja.msofficegurus.com/como-fazer-excel-2003-programac-o-ado-e-adox.htm

Focando em nosso curso, se alguma vez o leitor programou acesso de dados no Access a partir do Excel você certamente já utilizou a biblioteca do Data Access Objects (DAO) ou do ActiveX Data Objects (ADO). Aqui, irei discutir somente o ADO e o DAO ficará para um módulo em separado (veja no site como obter o outro módulo  sobre DAO) para que os conceitos sejam compreendidos separadamente.

O usuário comum do Access provavelmente não se interessará pela utilização de VBA para efetuar o seu trabalho. Contudo, se o seu trabalho envolve o desenvolvimento de bancos de dados, então, conhecer o VBA e os métodos de acesso e manipulação de dados será crucial.

Como toda e qualquer biblioteca, a biblioteca ADO possui uma hierarquia. Dentro desta hierarquia existem objetos que são, de modo geral, mais importantes que outros.

A biblioteca ADO, assim como qualquer outra biblioteca, contém um conjunto de objetos que pertence a um mesmo grupo. Desta forma, fica relativamente fácil encontrar um objeto dentro de um grupo e, consequentemente, isolar as propriedades e métodos de tal objeto[1].

No decorrer deste módulo irei apresentar cada um deles individualmente com exemplos de como utilizá-los.

ADO pode ser utilizado para um número extenso de tarefas. Olharemos tais tarefas no decorrer do curso e como as mesmas podem nos ser úteis.

1.1.     Um pouco da história do ADO

A grande vantagem da utilização de DAO sobre ADO é que DAO é especificamente designado para os Jet Databases. Não obstante, como DAO funciona em cima do Jet Database Engine quando o assunto é conexão remota ADO superado DAO.

Muitos websites, mesmo utilizando um banco de dados Access, por exemplo, tendem a utilizar ADO na conexão de dados em detrimento ao DAO, pois ADO foi desenvolvido com a principal intenção de se conectar com as mais variadas fontes de dados.

ADO faz parte do esforço da Microsoft para criar uma biblioteca universal de acesso de dados (chamada de UDA – Universal Data Access).

1.2.     “Strings” de conexão

O provedor para uma conexão ADO é o Microsoft. Jet.OLEDB.4.0. Como a conexão em si possui vários parâmetros o leitor deve estar se questionando como lembrar isso tudo de cor. A verdade é que não precisamos.

Quando chegar a hora de determinar a string de conexão, nós podemos utilizar um artifício para obter tal informação. Obtido os detalhes o que fazemos é modificar onde é necessário. Para obter a string de conexão abra o projeto Access para o qual você precisa da string de conexão. Abra o VBE (Alt+F11) e na janela de verificação imediata digite:

?Currentproject.Connection

Na própria janela de verificação imediata, obteremos a string completa como segue (a string está quebrada para facilitar a leitura. Cada quebra ocorre onde há o ponto-e-vírgula):

Provider=Microsoft.Jet.OLEDB.4.0;

User ID=Admin;

Data Source=E:\Robert\mdbTeste.mdb;

Mode=Share Deny None;

Extended Properties="";

Jet OLEDB:System database=C:\dbsTrampo\System.mdw;

Jet OLEDB:Registry Path=Software\Microsoft\Office\11.0\Access\Jet\4.0;

Jet OLEDB:Database Password="";

Jet OLEDB:Engine Type=5;

Jet OLEDB:Database Locking Mode=1;

Jet OLEDB:Global Partial Bulk Ops=2;

Jet OLEDB:Global Bulk Transactions=1;

Jet OLEDB:New Database Password="";

Jet OLEDB:Create System Database=False;

Jet OLEDB:Encrypt Database=False;

Jet OLEDB:Don't Copy Locale on Compact=False;

Jet OLEDB:Compact Without Replica Repair=False;

Jet OLEDB:SFP=False.

Não é necessário utilizar todas as partes da conexão conforme mostrado acima. Neste módulo, nós estamos interessados em:

·         Provider

·         User

·         Data Source

·         Password (se houver uma senha no banco de dados)

O restante não será relevante para desenvolvermos o curso.

1.3.     Declaração de variáveis: evitando ambigüidades

Como DAO e ADO são bibliotecas destinadas à conexão de dados é de se imaginar que ambos possuirão objetos os quais são os mesmos (embora possuam diferentes propriedades e métodos). Para evitar ambigüidade no seu código, procure sempre declarar explicitamente qual biblioteca você estará usando.

Isso não somente melhora o desempenho do seu código como também lhe ajudará a identificar os objetos sendo declarados e usados. Por exemplo, para evitar ambigüidade entre os recordsets de uma e de outra biblioteca, faríamos:

Sub declarandoVariáveis()

    Dim daoRs  As DAO.Database

    Dim adoRs  As ADODB.Recordset

End Sub

O nome da variável é menos importante do que o objeto ao qual ele se refere. Por exemplo, eu geralmente declaro um recordset como sendo apenas um rs sem prefixá-lo com ado ou dao como fiz acima. Isso porque o objeto está claro logo a seguir, mas caso seja necessário é uma opção a ser considerada, pois reduz mais ainda as chances de confusão entre os objetos.

1.4.     Cursores “server-side” vs “client-side”

Quando usamos ADO podemos escolher entre os cursores “server-side” (lado do servidor) e “client-side” (lado do cliente). O cursor “server-side” é o padrão do ADO.

A grande diferença entre um cursor e outro diz respeito ao local onde os dados contidos em um recordset são “cached” (armazenados). No caso do “server-side” isso ocorre no servidor ao passo que o “client-side” ocorre no cliente.

Assim, se o cursor é “server-side”, quanto mais usuários estiverem conectados, mais estresse e colocado no servidor, pois mais recursos são exigidos dele para servir a todos os clientes. Por outro lado, no caso do “client-side” o armazenamento ocorre localmente o que reduz o estresse colocado no servidor. No caso do “client-side”, isso implica mais rapidez no acesso aos dados uma vez que os mesmos estejam armazenados localmente.

Testes de desempenho apontam para o uso de cursores “server-side” para instruções como INSERT, UPDATE e DELETE, pois liberam recursos do servidor após a execução. Por outro lado, instruções como SELECT e SELECT UNION são mais apropriadas para “client-side”, pois o armazenamento consome recursos do cliente e não do servidor.

Existem tipos de fonte de dados que não aceitam cursores “server-side” e nestes casos precisaríamos utilizam “client-side”, não obstante se estamos trabalhando com Access via Excel isso passa a ser irrelevante se o programa sendo desenvolvido é stand-alone.

1.5.     O que o leitor precisa fazer antes de continuar

Antes de continuar a leitura é importante que o leitor instale as referências ao ADO e ADOX. Para tanto, siga os passos abaixo:

·         Abra o VBE (Alt+F11);

·         Clique em Ferramentas à Referências;

·         Procure e selecione na lista as sequintes referências:

o   Microsoft ActiveX Data Objects X.x Library

o   Microsoft ADO Ext. X.x. for DLL and Security

O X.x indica a versão (para Office 2003 com as atualizações a versão é 2.8).

Algumas vezes, precisamos criar Ferramentas em VBA, nas quais é necessário listar os arquivos ou pastas em determinado drive da máquina.
Podemos realizar esta tarefa usando o Scripting RunTime, ou mesmo o Shell, porém existe um recurso nativo que permite obter o mesmo resultado.
No exemplo a seguir são retornados todos os arquivos Excel na pasta D:\Projetos

Sub ListarArquivos()

Dim Files As Variant

Files = Dir("D:\Projetos\*.xls*", vbArchive)

While Files <> ""

    Debug.Print Files

    Files = Dir(, vbArchive)

Wend

End Sub

Com ela também é possível retornar as pastas, basta usar vbDirectory, ao invés de vbArchive, ou usar filtros com o nome de arquivo. Por exemplo usando 

Files = Dir("D:\Projetos\B*.xls*", vbArchive)

Será retornado todos os arquivos Excel que começam com a letra B.

Bom é isto ai, até uma próxima!

Bruno Leite

Office developer

É de conhecimento de todos que desenvolvem que existem dois (ou ate mais)  componentes activex de calendário, o Microsoft Calendar Control e o Month View Activex Control são alguns dos exemplos, para uso em projetos VB/VBA.

Especificamento em projetos VBA, o uso desses controles implica em validações de segurança no uso de componentes externos por parte dos aplicativos Office, como o Excel por exemplo. Isso significa que não há como fugir de algumas caixas de dialogos de aviso (nem mesmo via VBA, e não ha como desativa-las facilmente).

Um exemplo tipicos dessas mensagens é a de que componentes externos estão sendo inicializados pelo Excel;

Para contornar esse tipo de situação especificamente para o MS Calendar, resolvi criar o meu proprio controle de calendário totalmente em VBA

Para usa-lo é bem simples:

1. Crie uma varável do tipo frmCalendar
2. Exiba o formulário (Show)
3. Aguarde a data que será clicada.

Segue abaixo um esboço do codigo

Clique aqui para Baixar o arquivo

Att. Adelson RM Silva

Sabe com renomear arquivos via VBA ?.

Existem algumas formas de fazer isso como por ex: via Shell Automation, WMI...

mais uma maneira bastante simples e direta, e totalmente VBA é usar a função Name

Ex:

Code:

Sub RenomearArquivo()

Name "c:\arquivo.txt" as "c:\novo_arquivo.txt"

End Sub

Se você utiliza o Windows 7 e precisa utilizar, por alguma razão, programas no Windows XP, uma ótima solução é utilizar uma máquina virtual.

O problema com a máquina virtual e que ela requeria um conhecimento específico para instalação e configuração da máquina bem como uma versão do sistema operacional devidamente registrada.

Com o Windows 7 isso é coisa do passado. Agora, podemos utilizar o Windows XP Mode o qual nos fornece tais ferramentas na ponta dos dedos.
Segue aqui os passos para instalação do Windows XP Mode:

1. Baixe e instale o Windows XP Mode e Windows Virtual PC no seguinte endereço http://www.microsoft.com/windows/virtual-pc/download.aspx
2. Assegure-se que a virtualização em sua BIOS esteja ativa
3. Instale o Virtual HD do Windows XP Mode que você baixou
4. Instale o Windows Virtual PC
5. No menu “Iniciar” (Start) do seu Windows 7, abra o Windows XP Mode
6. O processo de instalação será iniciado. Siga os passos e pronto!

No Excel 2003 não existe a função ALEATÓRIOENTRE() onde podemos gerar numeros aleatórios entre dois numero maior que 1, apenas por meio do Analises Tool Pack.
Ja no Excel 2007 a função foi incorporada na biblioteca de funções builtin.
No VBA, no entanto, esse função ainda não existe, embora possamos usar a função do Excel por meio do WorksheetFunction.
mas há como montar um função UDF para essa tarefa. é bem simples e útil.

	Código: Function ALEATORIO_ENTRE(NumInferior,NumSuperior) As Double ALEATORIO_ENTRE = Int((NumSuperior - NumInferior+ 1) * Rnd + NumInferior) End Function

Introdução:

Um dos principais recursos que a linguagem Visual Basic nos fornece é o suporte à tecnonogia COM, a qual podemos usar programas “dentro” de programas por meio de suas API.

Para quem quer saber mais sobre o assunto pode conferir um ótimo artigo na Wikipedia

Component Object Model (Wikipedia)

Muitas dessas API´s estão disponíveis para uso nos aplicativos do Office por meio do VBA. Outras, no entanto, não funcionam em VBA apesar de estarem listadas na caixa de dialogo Ferramentas > Referencias.

Apresento na lista a seguir algumas das bibliotecas de objetos disponiveis, as quais podemos usar.

Nos proximos artigos apresentarei alguns macetes de como aproveitar o poder desse recurso.

Até a proxima !

Att. Adelson RM Silva

1. Como o Excel interpreta o transcorrer do tempo?


Toda contagem de tempo é relativa. Contamos sempre o tempo transcorrido entre dois momentos, em outras palavras, avariamos a duração de um dado evento. Tendo isso em mente, foi estabelecido para o Excel que o momento t = 0, ocorreu exatamente à zero hora do dia 1º de janeiro de 1900 (00:00 01/01/1900). Cada dia contado a partir desta data representa uma unidade de tempo. Se digitarmos uma data no Excel, como, por exemplo, 13/12/2009 e modificarmos a formatação para número com separador de milhares (#.##0) veremos o valor 40.160. Isto significa que se passaram 40.160 dias desde o dia 01/01/1900 até o dia 13/12/2009. 

Compreendido então, que a unidade fundamental de marcação de tempo é o dia, deduzimos então que 1 hora é representada pela fração 1/24, 1 minuto pela fração (1/1.440) e 1 segundo, por (1/86.400)

LEIA TODO O ARTIGO CLICANDO AQUI