excel e macro, cria e controla um banco de dados[RESOLVIDO]

Edcronos

27-06-2015 18:15:21

será que alguem tem um exemplo pronto de excel e macro que cria e controla um banco de dados

eu já tentei mas não consegui, se bem que estou achando que tinha faltado era as referencias no vba

no caso o baco de dados não vei ser fixo,
quero criar referencias de pesquisas e comparações de dados que se faz na planilha

estou achando que postei no lugar errado, mas...
vou editar o tópico até conseguir ou alguém responder...
tbm como um memorando.


numa pesquisa aqui achei uma pagina que tem alguns detalhes importantes, além de falar de pontos chaves de vários bancos de dados
http://www.codeproject.com/Articles/586 ... PostgreSQL


o assunto da escolha é complicado...
https://www.scriptcase.com.br/forum/ind ... 354.0.html
http://pt.stackoverflow.com/questions/8 ... postgresql

-------------------------------------------------------------------
como eu tinha que começar de algum lado...

escolhi o PostgreSQL
mas está dificil

estou tentando ler algumas coisas a respeito mas o fato de não saber ingles e o tradutor as vezes mais atrapalhar, torna tudo mais dificil
http://stackoverflow.com/questions/5509 ... xcel-sheet
http://blog.mclaughlinsoftware.com/micr ... xcel-2007/
https://support.office.com/en-ie/articl ... n-IE&ad=IE


Parece que encontrei um tutorial útil.
http://talendbrasil.com.br/2011/02/14/s ... lha-excel/

Talvez a parte pura do banco de dados ajude na interação e no uso das funções
http://jquerydicas.blogspot.com.br/2014 ... dados.html
o blog só fala de bancos de dados

---------------------------------------------------------------
algo a respeito de Java excel e banco de dados "meio antigo"
pode ser uma boa para quem gosta de mexer com planilha, sabe um pouco de vba e quer iniciar em outra liguagem de programação
http://www.guj.com.br/java/31858-ler-de ... o-de-dados

http://stackoverflow.com/questions/3640 ... g-prompted
http://www.mrexcel.com/forum/excel-ques ... excel.html
http://www.devmedia.com.br/lendo-e-escr ... rte-i/7328
http://com4j.kohsuke.org/
http://obba.info/

Desculpa misturar as coisas, é que eu estou tentando conciliar as ideias e definir um rumo mais sólido


fui dar uma olhada em um forum de access
http://www.maximoaccess.com/
e fiquei totalmente perdido,
não entendi o pq tudo ser tão diferente do excel apesar do conceito ser o mesmo

Mikel Silveira Fraga

30-06-2015 16:31:23

Edcronos, boa tarde e seja bem vindo ao fórum.

Cara, se você é iniciante no mundo de VBA, já chegar trabalhando com banco de dados vai ser muito complicado, mas não impossível.

Não estou tendo muito tempo, mas vou ver se consigo ir editando algumas dicas neste tópico, para poder lhe ajudar.


A primeira coisa que acredito ser fundamental para se iniciar um trabalho com o Banco de Dados é, saber exatamente o que é um banco de dados e como funciona. Uma coisa boa de se trabalhar com BD´s atualmente é que, independente que você escolha utilizar PostGree SQL, MySQL, Mongo BD, Maria BD, Firebird, Oracle, MS SQL Server, MS Access, etc, é a padronização da Linguagem SQL.

A linguagem SQL é utilizada para realizar consultas e manipulação de todos esses banco de dados citados acima, tendo como diferença apenas alguns parâmetros discretos. Então, antes de tentar iniciar um projeto integrando MS Excel com um Banco de Dados, aconselho que aprenda antes o Conceito de Modelagem Relacional para BD´s e a Linguagem SQL.

Seguem duas dicas abaixo, para começar (sites em português):
- Curso de SQL Completo da Softblue;
- SQL para Iniciantes do RBTech.info.

Todos os dois cursos utilizam o MySQL, mas como eu disse, o aprendizado de um não irá interferir em ser aplicado no outro.

Pessoalmente falando, não conheço o curso do RBTech, mas estou tentando arrumar um tempo para avaliá-lo, pois o autor do site é muito bom e gosto sempre de acompanhar seus Vídeos/Artigos.

Em relação ao Curso da Softblue, fiz quando estava aprendendo a trabalhar com SQL e gostei muito do curso.

Acho que vale a pena dar uma olhada nos dois cursos, para começar.

Como disse, vou postando algumas coisas de pouco a pouco. Dê uma olhada nesse acima e me dê sua opinião.

Abraços e bons estudos.

Edcronos

30-06-2015 22:55:01

obrigado pelas dicas vou dar uma olhada

eu falo que sou iniciante pq sempre estou aprendendo algo novo
minha curva de aprendizado é meio conturbada
para conseguir entender eu acabo misturando um monte de coisas

numa mistura danada e numa tentativa de interagir o excel com algo mais entrei num forum de access "pensei que funcionava como banco de dados"
isso pq a unica interação que eu tinha conseguido até hoje foi com paginas da internet

para tentar entender o conceito de logica de programação do access fiz uma planilha para listar arquivos de uma pasta e listar as tabelas para escolher e importar para o excel
http://www.maximoaccess.com/t23337-list ... tipo-accdb
parece que o pessoal não lida muito bem com interação do access a partir de outros programas

Mikel Silveira Fraga

01-07-2015 03:53:51

Edcronos, boa noite.

Li seu tópico no MaximoAccess e entendo sua situação, mas na verdade acredito que você esteja fazendo uma pequena confusão.

Vou tentar explicar, pegando como base a Interação Excel e Access.

Como você mesmo mencionou, seu problema não é com Excel, pois você disse que conhece bem a ferramenta. Analisando as bibliotecas selecionadas na referência, você não esta sabendo quando utilizar referência X ou Y. Bem, de uma olhada nos links abaixo:
- Criando Banco de Dados e tabelas - Site Macoratti.net;
- ADO, ADOX - Manual de Sobrevivência ... - Site Macoratti.net

Estes links darão uma explicação sobre quais bibliotecas utilizar em determinado momento. Carregando todas estas bibliotecas, você irá gerar um arquivo pesado, sem necessidade.

Outro ponto, não acho que seja necessário conhecimento aprofundado em Access para realizar a interação. Procure aprender a estruturar uma tabela de dados no Access, que já será o suficiente. Claro que, se você estiver com disposição para aprender a trabalhar com Access, sempre é bem vindo. Eu mesmo tenho aprofundado um pouco mais em Access, mas apenas com o objetivo de agregar conhecimento. Tanto para realizar Exportação do Access x Excel, quanto o caminho oposto, o ponto de partida (desenvolvimento VBA) pode ser feito completamente pelo Excel, sem necessidade de se conhecer Macros/VBA do Access.
Edcronos escreveu:minha curva de aprendizado é meio conturbada
Sobre esse comentário, sempre falo para as pessoas que me perguntam sobre VBA a importância de se adquirir um conhecimento básico com solidez. Como você, também aprendi as coisas conforme minha necessidade. No ano passado, comprei alguns cursos do Prof. Julio Battist, os quais me ajudaram muito a compreender conceitos padrões da própria Linguagem VBA.

Outra dica que lhe dou sobre interação com Excel VBA e Banco de Dados, de modo geral, é conhecer um pouco da linguagem antecessora do VBA, no caso a plataforma Visual Basic 6. Atualmente, eu a tenho estudado muito para aprofundar nas possibilidades do VBA, como também tenho buscado conhecimento com VBScript, direcionado para administração de redes. Os dois conhecimentos (VB6 e VBScript) tem agregado muito as possibilidades de uso do VBA.

De uma olhada nos dois links acima, sobre as bibliotecas. Eles vão lhe auxiliar muito nesse processo de compreensão da interação entre Excel VBA e Banco de Dados.
Para finalizar, segue um link de um outro tópico do fórum, com um projeto da faculdade, onde desenvolvi uma ferramenta para PCP Industrial, utilizando Excel VBA e MySQL. Caso tenha interesse em dar uma olhada, é só baixar o instalador no link do tópico e testar. Lembrando que ele funciona apenas com Windows 32 bits e Office 32 bits.

Vamos conversando. Abraços e bons estudos.

Edcronos

01-07-2015 15:33:42

ola
suas dicas são muito boas e bem vidas
até video aula que eu odeio
a sua indicação foi muito bem vinda,
e só no exemplo já deu para ver que é muito bem feito e sem enrolação

por enquanto não pretendo aprender access profundamente,
mas na interação com o vba tem que se saber como usar pelo menos o basico

tipo, para excel, se quiser pegar uma range se usa range(), para celulas Cells(),para uma aba Sheets() e por aí vai
então para pegar um valor de uma celula e aba especifica se tem que saber sobre sheet e cells pelo menos, mesmo se for fazer isso a partir do access

para access tabelas seria como sheets e tem os campos
então tem que saber as características para pegar qualquer coisa e para isso apenas alguem que saiba mexer com access independente se a pessoa conhece o excel ou não

para saber o nome da aba ativa no excel se usa ActiveSheet.Name
eu não posso usar o mesmo comando no acces, mesmo usando a macro a partir do excel

para listar os nomes das abas em um combo box eu uso essa macro:

Código: Selecionar todos

Sub ComboBoxPLanilha(NomeCombobox As String, Optional FiltrarSemSetores As Byte)

    With Me.Controls(NomeCombobox)
        On Error Resume Next
        Do While .ListCount > 0: .RemoveItem (0): Loop    'limpa Combobox
        .AddItem "PLanAtiva"
        For Each sheetos In Worksheets
            aa = sheetos.Name
            If FiltrarSemSetores = 1 Then
                N = 1
                If PlanExiste(aa) = 1 Then If Worksheets(aa).Range("P12") <> "Nome setor" Then N = 0
                If N = 1 Then .AddItem aa
                N = 1
            Else
                .AddItem aa
            End If
        Next
    End With
End Sub
"repara a bagunça não"

no access para as tabelas

Código: Selecionar todos

Private Sub LIST_tabel_DropButtonClick()

If List_Arq.value = "" Then Exit Sub

    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Set cn = New ADODB.Connection: 
Set rs = New ADODB.Recordset

    prov1 = "Provider=Microsoft.ACE.OLEDB.12.0;"    ' para .accdb

    With Me.LIST_tabel
        Do While .ListCount > 0: .RemoveItem (0): Loop
        N_Aquiv = List_Arq.value
        Aquiv = "Data Source=" & LocArq & N_Aquiv & ";"

        prov = prov1
        cn.Open prov & Aquiv

'Daqui para baixo é para interagir com o arquivo aberto e usar os comando suportados pelo access

        Set rs = cn.OpenSchema(adSchemaTables)
        While Not rs.EOF
            NTab = rs!TABLE_NAME
            If Not (NTab Like "MSys*" Or NTab Like "~*") Then .AddItem NTab
            rs.MoveNext
        Wend
    End With

  rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub
como pode ver para se listar as tabelas se usa comandos referente ao ado ou dao , eu pelo que eu entendi é uma particularidade do access assim como nos bancos de dados

eu consegui fazer oq queria a parti de exemplos puramente access sem interação com outras ferramentas

Código: Selecionar todos

 Set rs = cn.OpenSchema(adSchemaTables)
        While Not rs.EOF
            NTab = rs!TABLE_NAME
            If Not (NTab Like "MSys*" Or NTab Like "~*") Then .AddItem NTab
            rs.MoveNext
        Wend
como pode ver não importa o conhecimento em vba excel se a pessoa quer interagir com o access mesmo pelo proprio excel, é importante ter em mãos a sintaxe de programação do próprio access

a planilha que fiz foi apenas para aprender algo

vc tem razão estou muito perdido sobre as referencias, e não apenas com ado e dao
mas pelo que eu já li as bibliotecas não ficam no arquivo e apenas são "referenciadas" quando iniciadas , não pesando ,
tanto é que se for rodar o arquivo em um pc que não tenha as dll e afins já instalado dá todo tipo de erro
a planilha apenas ganha o poder de chamar e ter posse de um dicionario

tipo ado
Dim cn As ADODB.Connection
dim rs As ADODB.Recordset

quando se declara a variavel o tradutor do vba sabe que tem que referenciar aquela variavel diretamente na biblioteca ado

mas bem,
pelo que eu entendi não se pode separar as coisas quando se fala de interação, tem que saber sobre os 2 lados

o pessoal do forum do access simplesmente ignorou o fato de que para interagir com o access tem que usar as ferramentas, comandos e sintaxe de programação do access , independente se vou usar pelo excel ou não

Sobre:
minha curva de aprendizado é meio conturbada
é pq não aprendo facilmente e não consigo decorar nada
eu vou lendo, fazendo, testando até dar certo e assimilando a logica
por isso mesmo uso mais programação de baixo nivel
tipo, uso laço for next com if then em vez de usar o comando find,
mas claro que isso só é valido se se tiver acesso aos dados segmentados

Edcronos

01-07-2015 16:51:37

Caso não queira le o livro de cima...
e desculpa se falei alguma baboseira.

oq vc achou das macros que fiz e em que eu errei fora as referencias?

eu tenho que acertar a parte de passa a tabela para o Array
Coluno = rs.GetRows

fiz um teste com uma tabela de ruas e cep de 709.440 linhas e 4 colunas
separando os principais e tentando saber o tempo de cada parte da rotina
Sheets("Plan2").Cells(3, 1) = "passar tabela para array = " & Time - StartTime:
StartTime = Time

os tempos ficaram assim
  • 1/7/15 13:11:23
    abrir e listar tabela =00:00:01
    passar tabela para array = 00:00:02
    tranpor array = 00:00:02
    passa para range e fechar = 00:00:06
nem sei se está ruim
minha configuração é i3 3.4 Ghz, 8gb, win7 64b, office 2010 32b

para seguir com os entendimentos
tenho que aprender agora como :

varrer os campos
saber se a tabela tem dependências
filtrar
e pegar valores já filtrados

primeiro vou fazer essa planilha capaz de buscar, filtrar e pegar dados do access , depois vou fazer ela criar e modificar arquivos do access
isso é para assimilar já que não decoro nada

Basole

01-07-2015 17:19:24

Edcronos, boa tarde.

Pelo que entendi, voce está querendo criar uma planilha que abra qualquer bd access e que possa selecionar a tabela desejada.

Veja um exemplo que fiz que faz o que voce precisa.

* Mas no meu exemplo eu usei o componente OWC http://www.microsoft.com/pt-br/download ... px?id=9468 para inserir os dados no proprio userform.
Veja se consegue adaptar a sua necessidade.

Mikel Silveira Fraga

01-07-2015 19:58:51

Basole, boa tarde e seja bem vindo ao fórum.

Excelente exemplo desenvolvido por você.

Não testei o funcionamento, pois não estou com o componente OWC (Office Web Component) instalado. mas seguindo a sequência dos códigos, da pra compreender o funcionamento.

Meus parabéns.

Mikel Silveira Fraga

01-07-2015 21:06:47

Edcronos, boa noite.

Cara, não se preocupe em relação as livros escritos, não me importo em ler.

O exemplo postado pelo Basole faz exatamente o que você precisa. Como mencionado acima, um excelente exemplo.

Caso tenha alguma dúvida, é só falar.

Abraços!!!

Edcronos

01-07-2015 21:36:10

Basole
obrigado pela planilha de exemplo vou tentar tirar proveito dela
mais uma que não conhecia "Office Web Component"
mas pelo que vi foi feito para o office xp e tem certas vulnerabilidades


eu estou fazendo a planilha para aprender, e talvez um planilha para importação e criação de tabelas
eu já consigo pegar tabelas e importar para a plan
como pode ver pela planilha que postei
download/file.php?id=2530

Edcronos

01-07-2015 21:43:52

estou tentando rodar para ver oq tem de diferente de funcionamento da minha fora mostrar na propria form e aceitar tbm "MDB",
mas está dando metodo não encontrado
Me.Spreadsheet

Edcronos

02-07-2015 01:55:11

Estava pensando aqui
eu adoro mexer em planilha com vba

e adoraria ganhar algum dinheiro com isso
tenho alguns bons projetos, mas não vale a pena disponibilizar pq o excel tem nota zero para a proteção

e como fica essa proteção no Access?
se colocar senha é facil acessar as macros?

estava vendo esse post
http://www.maximoaccess.com/t22400-encriptacao-sha1

e me veio a ideia de entrelaçar o excel que eu gosto de mexer com o access
aí se perguntam, mas eu já não estou fazendo isso? não...

nesse casso em questão eu passaria todas as macros para o access e deixaria apenas algunas de conexão e controle no excel
as macros do excel entrariam no access que verificaria a senha e acionaria as macros controlando o excel com as macro criadas para o excel

como minhas macros são por array e comandos simples a questão não seria tão complicada assim
Expl.

Código: Selecionar todos

Public Sub Espelhar(ByRef ArrayNome As Variant, ByVal C_ou1__L_ou2 As Variant, Optional ByVal Nun_C_L As Long)
    Dim L As Long, C As Long, x As Long, Pia As Long, Pfa As Long
    Dim Ar2() As Variant
    Pia = LBound(ArrayNome, 2) + 2: Pfa = UBound(ArrayNome, 2)
    lia = LBound(ArrayNome, 1): lfa = UBound(ArrayNome, 1)
    x = 0
    Ar2 = ArrayNome
    If C_ou1__L_ou2 = "c" Then C_ou1__L_ou2 = 1
    If C_ou1__L_ou2 = "l" Then C_ou1__L_ou2 = 2
    If Nun_C_L = 0 Then

        If C_ou1__L_ou2 = 1 Then    '-------( ColunaS )
            For L = lia To lfa    'Step 3
                x = 0
                For C = Pia To Pfa
                    ArrayNome(L, C) = Ar2(L, Pfa - x)
                    x = x + 1: Next: Next
        End If

        If C_ou1__L_ou2 = 2 Then    '--( LinhaS )
            For L = lia To lfa
                For C = Pia To Pfa
                    ArrayNome(L, C) = Ar2(lfa - x, C)
                Next: x = x + 1: Next
        End If

    Else    '------------(   Linha ou coluna especifica    )
        If C_ou1__L_ou2 = 1 Then
            For L = lia To lfa
                ArrayNome(L, Nun_C_L) = Ar2(lfa - x, Nun_C_L)
                x = x + 1: Next
        End If
        If C_ou1__L_ou2 = 2 Then
            For C = Pia To Pfa
                ArrayNome(Nun_C_L, C) = Ar2(Nun_C_L, Pfa - x)
                x = x + 1: Next
        End If
    End If
End Sub
se a proteção do acces for eficiente vale a pena aprender mais um pouco
oq vc´s acham

Basole

02-07-2015 02:07:18

Mikel Silveira Fraga escreveu:Basole, boa tarde e seja bem vindo ao fórum.

Excelente exemplo desenvolvido por você.

Não testei o funcionamento, pois não estou com o componente OWC (Office Web Component) instalado. mas seguindo a sequência dos códigos, da pra compreender o funcionamento.

Meus parabéns.
Muito obrigado Mikel eu aprendi muito com voces acompanhando este forum.

Só baixar o owc by Microsoft no link que postei e instalar pra testar pois acho que vale a pena conhecer este componente que tem quase todos os recursos da propria planilha.

Basole

02-07-2015 02:14:43

Edcronos escreveu:estou tentando rodar para ver oq tem de diferente de funcionamento da minha fora mostrar na propria form e aceitar tbm "MDB",
mas está dando metodo não encontrado
Me.Spreadsheet
Boa noite Edcronos,

Aparentemente, pelo erro apresentado me parece que o componente(owc) nao foi instalado ou não esta na pasta (diretorio) correto. A versão do seu win é 64?
Tente copiar e colar o componente (OWC10.DLL) para o seguinte path "C:\Program Files\Common Files\microsoft shared\Web Components\10" em seguida registra-lo com o comando [ Regsvr32 owc10.dll ]

Basole

02-07-2015 02:29:23

Segue a img da tela do userform:Imagem

Edcronos

02-07-2015 15:20:25

Então é basicamente a mesma coisa que a minha faz
só que a minha é algo bruto apenas para aprender
e a sua é um produto mais bem acabado

o componente(owc) parece se legal e bem mais facil de usar, mas ao que parece tem certas falhas e seu desenvolvimento ficou parado
tbm tem o adox
http://www.motobit.com/tips/detpg_listdb/
por enquanto eu vou me focar apenas no ado


as referencias são coisas que tem que se entender , pq pode trazer capacidades que seriam proveitosas

Edcronos

13-07-2015 17:31:35

Ola
andei sumido por uns problemas
mas seguindo com o aprendizado ...

como tenho dificuldades de entender os conceitos vou na pratica
montei uma maquina para servir de servidor, algo somente para estudos (AMD 5000 X2, 2GB memoria ddr2, HD por enquanto vai ser 80gb)"com peças que tinha aqui", tenho outros HD's, provavelmente faça diferentes instalações para teste

não sei se a melhor opção é o linux
para access seria o win
oq aconselham ?

Mikel Silveira Fraga

13-07-2015 18:06:15

Edcronos, boa tarde.

Cara, se sua vontade é trabalhar com isso profissionalmente, eu lhe aconselharia a trabalhar com algo mais estável e seguro.

Da uma olhada nesse tópico: Visual Basic 6.0.

Sobre a estrutura que você mencionou acima, se esta com disposição, eu te aconselho a trabalhar da seguinte forma: SO Linux (qualquer distribuição que preferir), utilizando um DB com maior segurança (Firebird, MySQL, PostGree, etc.). Caso prefira trabalhar com servidor Windows, aconselho os mesmos DB´s, acrescentando o SQL Server.

Se for para um trabalho profissional, eu não aconselho trabalhar com o Access como DB, pois mesmo utilizando a criptografia do link mencionado acima, ele ainda é muito instável e não suporta uma enorme quantidade de informações.

Particularmente, eu curto muito usar o MySQL como DB, pois possui recursos que ajudam muito como Procedure´s, Trigger´s, entre outros.

Fica ai a dica. Abraços.

Edcronos

13-07-2015 21:06:44

não sei se entraria no mercado com as limitações que eu tenho "já pensou o furo de alguem perguntar algo e eu não lembrar o nome?"

primeiramente eu quero ver as possíveis integrações
depois quero estudar maneiras de transcrever as coisas que já tenho

na minha maquina oq eu fiz para excel é praticamente instantâneo, mas sei que em outras maquinas pela quantidade de dados pode ficar bem lento
sobre access é mais para aprender conceitos gerais

acho que postgresql tbm tem os comandos do MySQL
http://www.postgresql.org/docs/9.1/stat ... igger.html

infelizmente Visual Basic 6.0 envolve um custo adicional de licença

estava vendo uns tutoriais para servidores
http://www.hardware.com.br/livros/servi ... s-web.html

eita, é tanta coisa e tão pouco tempo...

Mikel Silveira Fraga

13-07-2015 23:04:27

Edcronos, boa noite.

Se formos olhar a questão de licenças, infelizmente no Brasil não da pra se manter uma licença original. Exemplo disso é o próprio Office. Quantas empresas no Brasil usam a ferramenta com licença crackeada!? São pouquíssimas as empresas que mantem uma licença atualizada para todos os pc´s disponíveis.

Claro que isso é algo realmente ruim de se dizer, mas pagar R$ 600,00 em uma licença do Office, somente se você realmente for ganhar dinheiro com isso.

O lance do VB6, acho que serve mais para aprendizado. Eu mesmo, depois que instalei o VB6 no meu pc e comecei a estudar os códigos dele, evolui muito no uso do VBA e as possibilidades foram só expandindo.

Sobre o MySQL, existe alguns artigos no próprio Site da Oracle, que vale a pena ler. Comercialmente, você precisaria pagar uma licença do MySQL. Mas, se você tiver uma hospedagem e colocar o banco com acesso remoto, no valor da hospedagem já é incluso o valor da licença do MySQL. Para os testes e uso pessoal, ele é gratuito.

O Postgree, até onde li, é totalmente gratuito, tanto para pessoal como comercial. Financeiramente falando, é uma boa jogada também.

Como você pode ver, são muitas possibilidades que temos para utilizar.

Agora, conceitos básicos que te aconselho a estudar, pra dar um norte:
- Lógica de Programação;
- Programação Orientada a Objetos;
- Modelagem de Dados Relacional;
- Linguagem SQL;
- Não é prioridade, mas aprender os conceitos de Xml, também é válido.

Cara, esses quatro primeiros tópicos são básicos. Outra coisa, depois que você aprender esses quatro e aplicar a uma Linguagem de Programação (VBA, por exemplo), mudar para outra linguagem é muito simples, pois a base é a mesma.

Olha, se ficar com receio de que o tempo é curto e é muita coisa pra estudar, você não vai sair do lugar. Aproveite todo o tempo que tiver e afunda a mente.

Vamos nos falando. Abraços e bom descanso por ai.

Edcronos

14-07-2015 01:41:58

Sobre o MySQL já tinha lido, e caso se queira usar a versão gratuita comercialmente tem que passar o codigo de seu projeto, assim como outros que vi
por isso mesmo a escolha pelo Postgree que tem um tipo de licença livre até para distribuição junto de projeto comercial

Lógica de Programação, provavelmente já tenho as bases

Programação Orientada a Objetos, esse para mim é o maior desafio,
pelo que vi o proprio ADO que é necessário para conectar ao banco de dados é em cima de disso, então não tem nem como fugir.
para mim o problema é lembrar os nomes das funções, normalmente eu faço as mesmas coisas usando projetos proprios e nomes que eu lembre

Modelagem de Dados Relacional já tinha lido a respeito
Linguagem SQL para banco de dados não tem nem como fugir

mas como falei é tanta coisa que se quer e tão pouco tempo, mas vou aprendendo aos poucos assim como foi com o vba

o pc que montei instalei um win em um hd e vou escolher um linux para instalar em outro por motivos de comparação
vou dar uma olhada no vb6, apesar de achar que é a mesma coisa do basic

Tenho que sair do vicio de macros de deslocamento, já fiz tantos tipos quue devo ter até algum que deita os dados e ainda coloca para dormir :P

Edcronos

31-07-2015 16:47:29

Ola
acabei dando uma parada no aprendizado
mas é pq estou tentando fazer uma adequação na minha planilha e adicionar outras coisas

agora estou agarrado com matemática básica,
fiz um filtro que pega valores específicos de qualquer parte da pasta de trabalho

mas não estou conseguindo fazer a totalização dos resultados,
e pior que não sei nem por onde começar

mas assim que resolver a questão volto para o banco de dados

até.

Edcronos

22-08-2015 05:09:53

estou tentando entender o funcionamento de arrays no postgresql para poder tentar migrar minhas aplicações

mas é bem confuso
como falei quero usar o banco de dados para otimizar e proteger o meu projeto
o escel seria apenas de interface o controle podendo conter dados recentes de comparação e informação
provavelmente o postgresql uma senha para uso e outra de administrador "nem sei ainda".

mas a abordagem do funcionamento das funções são um tanto confusas
ao mesmo tempo estou tentando entender os dados da bolsa para montar o banco de dados para testar a aplicação

Mikel Silveira Fraga

22-08-2015 23:09:26

Edcronos, boa noite.

Cara, bem interessante esse projeto que esta tentando fazer e é isso mesmo, tentando que se aprende.

Realmente nunca trabalhei com o Postgree SQL, mas a base de funcionamento é a mesma do MySQL e SQL Server.

Em relação as funções do Sql, eu já postei aqui e volto a indicar o Curso SQL Completo da Softblue. Fiz o curso e é excelente, além de ser gratuito.

Ainda quero pegar o Postgree SQL para testar. Fazer uma análise de desempenho dele (local, rede intranet e remoto/internet), velocidade de resposta e todo o resto. Quando comprar o Raspberry Pi, vou instalar junto do Linux e ver como se comporta.

Bem cara, tenha um excelente final de semana por ai e se cuida. Conversamos mais durante a semana.

Edcronos

23-08-2015 00:10:09

Cara o problema é conciliar o aprendizado de nova ferramenta e as varias ideias para aplicar na planilha
para ter ideia eu nunca tinha usado o listbox
e fui fazer um formulario para visualizar outras areas e outros dados da planilha
e como falei a planilha é toda dinâmica então eu tinha que ajustar o tamanho da userform de manira automática de acordo com a quantidade de colunas largura delas e se estava ou não visível
fiz mas achei o listbox muito chocho
pensei no listview mas como era somente para visualizar acabei usando caixa de imagem e captura de range e formatação condicional que já tem na planilha para melhorar o visual

e sabe como é, ideia puxa ideia e acabei adicionando uma busca de valores no setor escolhido e setas para navegar no setor com varredura por cod ou por valor, daí adicionei outra caixa de imagem para visualizar outro setor e area que esteja relacionado

Código: Selecionar todos


Private Sub Ti_Num_Change()
Inicio
    If Cod_Num.Value <> "" Then
        Call SetorL(sety_ory.Value, Plan_Ory.Value)
        ldd = (Cells(Rows.Count, Ti).End(xlUp).Row - Cells(Rows.Count, Ti).End(xlUp).Value) + Sort_Num.Value
        Cells(5, Cd).Value2 = Cells(ldd, Cd).Value2
        cwid = 120
        For c = Cells(1, Ti).Column To Cells(1, Fc).Column
            If Columns(c).Hidden = False Then cwid = cwid + (Columns(c).Width * 1.2)
        Next
          Final
        Call insereimagem(Me.Image1, Sheets(Plan_Ory.Value).Range(Ti & ldd - 3 & ":" & Fc & ldd + 3))
        If fsdfdsf.Value <> "" Then
        Inicio
            Call SetorL(fsdfdsf.Value, Plan_Ory.Value)

            For c = Cells(1, Ti).Column To Cells(1, Fc).Column
                If Columns(c).Hidden = False Then cwid = cwid + (Columns(c).Width * 1.2)
            Next
            Lista_Busca.Width = cwid '* 1.2
            Final
            Call insereimagem(Me.Image2, Sheets(Plan_Ory.Value).Range(Ti & ldd - 3 & ":" & Fc & ldd + 3))
            
        End If
        Me.Image2.Left = 114 + Me.Image1.Width + 3
    End If

End Sub
como a caixa de imagem tem a opção de se auto ajustar precisei apenas me preocupar com a userfor

e para colocar a range nas caixas de imagem fiz essa macro

Código: Selecionar todos

Private Sub insereimagem(Objet As Object, Rango As Range)

    Dim PLt As Worksheet
    Rango.CopyPicture xlScreen, xlBitmap
    Set PLt = Rango.Worksheet
    With PLt.ChartObjects.Add(0, 0, Rango.Width, Rango.Height)
        .Chart.Paste
        .Chart.Export ThisWorkbook.Path & "\imagew.gif"
        .Delete
    End With
    Objet.Picture = LoadPicture(ThisWorkbook.Path & "\imagew.gif")
End Sub
esse pode servir para outras pessoas já que funciona por referencia tanto da range como do objeto "caixa de imagem"
a busca de valores eu embuti nos controles de navegação

depois disso tudo achei muito lento e estou fazendo uma macro para subdividir a aba por linhas para listar os dados que se quer

Edcronos

23-08-2015 03:40:32

acho que não vou poder trabalhar com o banco de dados do jeito que eu quero
isso pq não entendi o funcionamento dos array's
e eu gosto de varrer a array do meu jeito
tipo essa macro que organiza os setores por valor crescente, decrescente ou desloca apenas as linha que tenha certo valor

Código: Selecionar todos


Sub OrdenaARRAY(ByVal cres1_decre2 As Byte, Optional ByVal Valor_vai_esqueda As Variant)
    Dim a As Variant, b As Variant, inC As Long, i As Long, Ci1 As Long, Cf1 As Long
    func = cres1_decre2
    If Limit = 1 Then Exit Sub
    If Valor_vai_esqueda <> "" Then X = Valor_vai_esqueda * 1

    Lfim = UBound(ColunO, 1): TCo = UBound(ColunO, 2)
    Ci1 = 4    ' coluna inicial de dados da array

    '-------------------------------------------------------------------------(inicio do loop )
    If func > 2 Then
        For L = 1 To Lfim
            If func = 3 Then    ' ---<<----( ORDEM POR VALOR X)---0
                Cx = Ci1
                For i = Ci1 To TCo
                    a = ColunO(L, i)
                    If a = X Then Cx = i
                Next
            End If
            If func = 4 Then    ' ---<<----( ORDEM CRECENTE )---0
                Y = ColunO(L, Ci1)
                Cx = Ci1
                For i = Ci1 + 1 To TCo
                    a = ColunO(L, i)
                    If a < Y Then Y = a: Cx = i
                Next
            End If

            If Cx > 4 Then
                For QL = 1 To Cx - Ci1
                    a = ColunO(L, Ci1)
                    For cl = Ci1 To TCo - 1
                        ColunO(L, cl) = ColunO(L, cl + 1)
                    Next
                    ColunO(L, TCo) = a
                Next
            End If
        Next
    End If
    '-------------------------------------------------------------------------------------------------

    If func = 1 Then    ' ---<<----( ordena valor Crescente)---0
        For lx = 1 To Lfim
            inC = Ci1:
            i = inC + 1
            Do
                a = ColunO(lx, inC): b = ColunO(lx, inC + 1)
                If a > b Then
                    ColunO(lx, inC) = b: c = a
                    ColunO(lx, inC + 1) = c
                    If inC > Ci1 Then inC = inC - 1
                Else
                    inC = i: i = i + 1
                End If
            Loop Until inC = TCo
        Next
    End If
    '----------------------------------------------------------------------------------------------------


End Sub
fora os filtros, junção e desmembramento de setores, comparações, deslocamentos ...

para mim varrer o array por linhas e colunas "Array(Linha,Coluna,Plano)"
é muito mais logico do que usar comandos já prontos

Edcronos

28-08-2015 23:12:17

busquei mas a maqueira que eu sei e gosto de trabalhar com arrays pelo visto não é possível,
isso pq acho que é tudo voltado a objetos
mas vou implementar o banco de dados e nele apenas colocar filtragem padrão para trazer os dados para a planilha
com o tempo vou pegando o jeito. "antes de morrer eu disponibilizo o projeto, ( isso se descobrir quando vai ser :P)"

Achei algo aqui que pode ser interessante para alguma pessoas
https://support.office.com/en-za/articl ... n-ZA&ad=ZA
https://geotecnologias.wordpress.com/20 ... -to-excel/

Mikel Silveira Fraga

29-08-2015 22:54:59

Boa noite Edcronos, blz.

O primeiro link que você postou é bem interessante, mas ele é limitado ao uso do Office 2013, já que o Power Query é uma ferramenta exclusivo desta versão.

Agora, o trabalho de conexão com o Postgree SQL, utilizando o Driver ODBC, é excelente pra qualquer tipo de situação, já que o processo padrão de conexão é muito parecido com demais bancos.

Cara, você comentou anteriormente sobre a dificuldade de entendimento das funções do SQL. Eu te digo uma coisa: a vantagem de se trabalhar com ele é otimização de comandos, tanto em termos de escrita de código, como desempenho e velocidade dos comandos.

Outra coisa que costumo a dizer, o ta o próprio Tomas como exemplo, esse desenvolvimento de Excel e VBA é um passo inicial para futuros programadores em outras áreas, seja VB.Net, C#/C++, Delphi, Asp.Net, Java, etc. SQL é padrão para ser utilizado com bancos de dados, e banco de dados são fundamentais para qualquer outra linguagem de programação.

Em resumo, foque não apenas em ser bom no VBA, mas foque em melhorar sempre seu conhecimento, por menor que pareça.

Excelente final de semana e bons estudos!!!!

Edcronos

29-08-2015 23:04:46

sofri tentando achar a maneira certa de conectar ao banco de dados
e pelos foruns afora cada um conseguiu de uma maneira diferente

primeiro segui os passos do site
https://geotecnologias.wordpress.com/20 ... -to-excel/
para instar o driver ODBC para PostgreSQL

o odbcad32.exe tem tanto no
C:\Windows\System32
como tem no
C:\Windows\SysWOW64
mas em SysWOW64 o odbcad32 lista outras coisas até do próprio office, isso leva a crer que é o local padrão
com isso o excel é capaz de se conectar ao banco de dados e importar as tabelas

mas no vba é que foi dificil, cada lugar informa uma maneira diferente
apesar de estar com um pc para servidor já montado como iniciante eu fui na configuração que mais se encontra pela net

eu tive que testar um por um e tentar minhas próprias maneiras até conseguir me conectar ao banco

para a instalação com configuração padrão do postgres
cn.Open "Driver={PostgreSQL Unicode};Dsn=my_system_dsn;Server=localhost;port=5432;
a parte em vermelho foi crucial e a mais difícil e cada lugar informa de maneira diferente tentei até usar referencia oledb

Código: Selecionar todos

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command, RS As ADODB.Recordset


Sub conecta_sql()
    Set cn = New ADODB.Connection
    cn.Open "Driver={PostgreSQL Unicode};Dsn=my_system_dsn;Server=localhost;port=5432;" & _
     "Database=postgres;" & _
     "Uid=postgres;" & _
     "Pwd=senha"
End Sub

Código: Selecionar todos

Sub desconecta_sql()
cn.Close
End Sub
Uid=postgres é o usuário padrão na instalação
Database=postgres é um banco de dados padrão criado na instalação do postgres , eu criei um com nome "teste" pelo pgAdmin do postgres e a conexão tbm foi legal

não sei se esse tipo de drive e conexão vai permitir criar e editar o banco, mas agora que consegui pelo menos me conectar posso dar um passo adiante


Edit:
como sempre...
depois de uma luta danada tentando fazer funcionar e nada do que se acha dar certo
e depois conseguir por conta própria, vai e aparece um site com algo funcional...
e ainda com um extra para outros bancos de dados
http://pragmateek.com/using-an-access-m ... -from-vba/

Edcronos

30-08-2015 07:01:45

Depois de uns testes creio que o topico está resolvido

1º instalar o driver ODBC para PostgreSQL
é só seguir o tutorial da pagina
https://geotecnologias.wordpress.com/20 ... -to-excel/

para conectar algo assim

Código: Selecionar todos

Dim cn As ADODB.Connection


Sub Conecta_Banco(Optional ByVal Nome_banco As String, Optional ByVal Nome_usuario As String, Optional ByVal Senha_Usuario As String)
    If Nome_banco = "" Then Nome_banco = "postgres"
    If Nome_usuario = "" Then Nome_usuario = "postgres"
    If Senha_Usuario = "" Then Senha_Usuario = "a1b2c3"
    
    Set Cn = New ADODB.Connection
    dbConnectStr = "Driver={PostgreSQL Unicode};Dsn=my_system_dsn;Server=localhost;port=5432;" & _
            "Database=" & Nome_banco & _
            ";Uid=" & Nome_usuario & _
            ";Pwd=" & Senha_Usuario
    Cn.Open dbConnectStr
End Sub
para criar novo usuario e senha algo assim

Código: Selecionar todos

Sub Criar_Usuario_SQL(ByVal Nome_usuario As String, Optional ByVal Senha_Usuario As String)
    If Nome_usuario <> "" Then
        If Cn Is Nothing Then Conecta_Banco
        Cn.Execute "CREATE ROLE " & Nome_usuario & " LOGIN PASSWORD " & "'" & Senha_Usuario & "'" & ";"
    End If
End Sub
para criar novo banco algo assim

Código: Selecionar todos

Sub Criar_banco(Optional ByVal Nome_banco As String, Optional ByVal Nome_usuario As String, Optional ByVal Senha_Usuario As String)
    If Nome_banco = "" Then Exit Sub
    If Cn Is Nothing Then Conecta_Banco
    Cn.Execute "CREATE Database " & Nome_banco & " OWNER " & Nome_usuario
End Sub
como pode ver daí para frente é só usar os comandos do SQL,
para isso se tem varios tutoriais pela net
Mikel Silveira Fraga indicou ótimas video aulas "infelizmente para mim não serve (para mim somente escrito)"

claro que sem esquecer o esforço próprio e tbm da imaginação.

Att.

Mikel Silveira Fraga

30-08-2015 10:58:53

Edcronos, bom dia.

Excelente notícia. Finalmente conseguiu sucesso.

Estive vendo esse link das conexões através do Ado, muito interessante. Vai ajudar no desenvolvimento de uma ideia que estou tendo.

Se tiver outras dificuldades, é só mandar que estamos a disposição.

Abraços e bons estudos!!!!

Edcronos

31-08-2015 06:30:03

para falar a verdade eu nem estava tentando antes, e consegui no mesmo dia "Madrugada" que tentei fazer a conexão
como falei eu estava implantando outras coisas na planilha e tentando entender as arrays do sql

mas como não consegui nada com as arrays dei prosseguimento em cima do vba mesmo

agora eu tenho que ajustar as ideias e o projeto
tipo
correlação ,no caso da minha planilha a correlação pode ser feita na hora da filtragem com colunas dependentes entre setores
eu posso pegar um valor de um setor e trazer dados de outro setor na mesma data, ou até fazer relação pelos nomes
mas no banco isso é feito por indicação fixa de relação entre tabelas "vou passar a chamar os setores de tabelas :P "
então vou ter que estudar como relacionar os dados, ou escolher o relacionamento na hora de exportar os dados pela coluna prioritária.

mas antes vou ter que estudar bem a ordem de chamada das macros que vai controlar o banco de dados
por exemplo se eu listo os usuarios conectados ao banco e quero derrubar um deles eu acabo criando outra conexão por causa da ordem de chamada das macros,
eu poderia simplesmente verificar a conexão existe mas estou estudando a opção de testar mais de um banco simultaneamente pelo proprio sql,
e para isso criar varias conexões simultâneas, bem ainda não sei como vai ser já que o vba é puramente sequencial, mas quem sabe eu não consigo uma execução paralela para tarefas pesadas.
pelo que vi quando se faz um select os dados não carregam diretamente no vba apena fica no aguardo pelo sql, então se pode criar sequencias prioritárias já que pelo que percebi a conexão inicial tem um certo retardo.


bem, ainda planejando e estudando

estou trabalhando uma planilha para acompanhar a bolsa por esse índices online
criei uma tabela que pega e atualiza de tempos em tempos e adiciona dados de referencia como indice beta e outas coisas que achei pela net :P e algumas ideias proprias

e antes de atualizar manda para o banco cada empresa um banco separado, se o banco da empresa não existir ele cria um
fiz relacionamento pela data

agora estou estudando como fazer para que quando a ação de um setor subir ou descer muito ele filtre o banco e me liste qual o comportamento da bolsa quando isso acontece
seria uma conexão para atualizar o banco e outra para pesquisa
até
e cad