MANIPULA?ÃO DE DADOS EM EXCEL VIA SQL
Prezados amigos, boa tarde!
Peço perdão pela minha ignorância, mas estou quebrando a cabeça com uma questão que não consigo resolver.
Estou desenvolvendo um sistema para gestão de treinamento de laboratório, já tenho uma versão funcionando onde criei todos os formulários no VBA do Excel e faço a manipulação dos dados nas planilhas da pasta de trabalho, usando Application.Visible = False para o usuário não ver a planilha em Excel. Desse jeito está dando muito problema, primeiro pq todas as outras planilha que estão abertas tbm ficam invisÃveis, segundo pq enquanto os fomulários do sistema estão abertos eu não consigo abrir outras planilha em Excel e terceiro pq o sistema só pode ser acessado por mais de um usuário de cada vez. Minha ideia para resolver todos esses problemas seria migrar toda a aplicação do sistema para o PowerPoint e usar o Excel como banco de dados e conectar os dois via ADODB.Connection.
Minha dúvida é a seguinte, é possÃvel inserir deletar e atualizar dados de planilhas de Excel com instruções SQL utilizando o Microsoft.ACE.OLEDB.12.0?
Se alguém tiver alguma ideia de solução mais simples para esses problemas seria de grande ajuda.
Muito obrigado!
Abraços!!
Peço perdão pela minha ignorância, mas estou quebrando a cabeça com uma questão que não consigo resolver.
Estou desenvolvendo um sistema para gestão de treinamento de laboratório, já tenho uma versão funcionando onde criei todos os formulários no VBA do Excel e faço a manipulação dos dados nas planilhas da pasta de trabalho, usando Application.Visible = False para o usuário não ver a planilha em Excel. Desse jeito está dando muito problema, primeiro pq todas as outras planilha que estão abertas tbm ficam invisÃveis, segundo pq enquanto os fomulários do sistema estão abertos eu não consigo abrir outras planilha em Excel e terceiro pq o sistema só pode ser acessado por mais de um usuário de cada vez. Minha ideia para resolver todos esses problemas seria migrar toda a aplicação do sistema para o PowerPoint e usar o Excel como banco de dados e conectar os dois via ADODB.Connection.
Minha dúvida é a seguinte, é possÃvel inserir deletar e atualizar dados de planilhas de Excel com instruções SQL utilizando o Microsoft.ACE.OLEDB.12.0?
Se alguém tiver alguma ideia de solução mais simples para esses problemas seria de grande ajuda.
Muito obrigado!
Abraços!!
Vamos fazer um comparativo do que você quer:
Você tem uma bicicleta e colocou um motorzinho nela. Daà você viu que não atendia bem o que você quer. Você tem também a grana para comprar um carro, mas prefere usar essa grana para comprar uma bicicleta de três rodas e colocar o mesmo motorzinho nela.
Ao invés de colocar sua aplicação no Power Point e usar o Excel como banco de dados, porque não usar logo o Visual Basic mesmo, e melhor ainda, o .NET com um banco de dados de verdade?
Você tem uma bicicleta e colocou um motorzinho nela. Daà você viu que não atendia bem o que você quer. Você tem também a grana para comprar um carro, mas prefere usar essa grana para comprar uma bicicleta de três rodas e colocar o mesmo motorzinho nela.
Ao invés de colocar sua aplicação no Power Point e usar o Excel como banco de dados, porque não usar logo o Visual Basic mesmo, e melhor ainda, o .NET com um banco de dados de verdade?
Citação::
Vamos fazer um comparativo do que você quer:
Você tem uma bicicleta e colocou um motorzinho nela. Daà você viu que não atendia bem o que você quer. Você tem também a grana para comprar um carro, mas prefere usar essa grana para comprar uma bicicleta de três rodas e colocar o mesmo motorzinho nela.
Ao invés de colocar sua aplicação no Power Point e usar o Excel como banco de dados, porque não usar logo o Visual Basic mesmo, e melhor ainda, o .NET com um banco de dados de verdade?
Obrigado pela resposta! Vc tem toda razão, mas estou desenvolvendo esse programa para uso próprio aqui do meu laboratório e dentro da empresa o recurso que eu tenho para otimizar meus controle é somente o pacote Office, então infelizmente acabo tento que ficar motorizando minha bicicleta kkkk.
Eu achei que seria fácil mudar a aplicação para o Power Point, mas até o momento só consegui fazer consultas nas planilhas do Excel, o Update ainda não consegui fazer funcionar, segue o código, se alguém puder me ajudar:
[ô]_________________________________________________________________________________________________________________
[ô]CONEXÃO COM AS TABELAS
[ô]_________________________________________________________________________________________________________________
Dim conStrBanco, conStrUpdate As String, pathBanco, pathUpdate As String, strSQLUpdate, strSQLBanco As String
Dim rsBanco, rsUpdate As ADODB.Recordset
Dim cnBanco, cnUpdate As ADODB.Connection
Dim enderecoUpdate As String
enderecoUpdate = TextBox1.Value + [Ô]\[Ô] + TextBox2.Value
[ô]CAMINHO COMPLETO DA PLANILHA (no diretório onde ela se encontra)
endereco = ActivePresentation.Slides(2).Shapes.Title.TextFrame.TextRange.Text
pathBanco = endereco & [Ô]\SGTLabBD01.xlsx[Ô]
pathUpdate = enderecoUpdate
[ô]String de conexão para a planilha. Estou usando o MS Office 2007
conStrBanco = [Ô]Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[ô][Ô] & pathBanco & [Ô][ô];[Ô] & _
[Ô]Extended Properties=[Ô][Ô]Excel 12.0;HDR=YES;IMEX=1;[Ô][Ô];[Ô]
conStrUpdate = [Ô]Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[ô][Ô] & pathUpdate & [Ô][ô];[Ô] & _
[Ô]Extended Properties=[Ô][Ô]Excel 12.0;HDR=YES;IMEX=1;[Ô][Ô];[Ô]
[ô]Instrução SQL
strSQLBanco = [Ô]SELECT Categoria, Identificador, Titulo, Revisao, Emissao, Tipo FROM [documentacao$A:F][Ô]
strSQLUpdate = [Ô]SELECT Categoria, Identificador, TÃtulo, Revisão, Emissão, TIPO FROM [[Ô] & TextBox3.Value & [Ô]$B:G][Ô]
[ô]Instanciamos objeto connection na variável declarada acima
Set cnBanco = New ADODB.Connection
Set cnUpdate = New ADODB.Connection
[ô]Abrimos a conexão
cnBanco.Open conStrBanco
cnUpdate.Open conStrUpdate
[ô]Instanciamos objeto recordset na variável declarada acima
Set rsBanco = New ADODB.Recordset
Set rsUpdate = New ADODB.Recordset
[ô]Abrimos recordset utilizando conexão aberta acima e instrução SQL
rsBanco.Open strSQLBanco, cnBanco, adOpenStatic, adLockOptimistic, adCmdText
rsUpdate.Open strSQLUpdate, cnUpdate, adOpenStatic, adLockOptimistic, adCmdText
[ô]___________________________________________________________________________________________________________________
[ô]INICIA LOOP NA PLANILHA DE ORIGEM
[ô]___________________________________________________________________________________________________________________
Dim Categoria, identificador, Titulo, Revisao, Emissao, Tipo, strSQLAtualizar As String
Dim teste, i As Integer
Do Until rsUpdate.EOF = True
teste = 0
Debug.Print rsUpdate!Categoria, rsUpdate!identificador, rsUpdate!TÃtulo, rsUpdate!Revisão, rsUpdate!Emissão, rsUpdate!Tipo
Do Until rsBanco.EOF = True
Debug.Print rsBanco!Categoria, rsBanco!identificador, rsBanco!Titulo, rsBanco!Revisao, rsBanco!Emissao, rsBanco!Tipo
If rsUpdate!identificador = rsBanco!identificador Then
If rsBanco!Revisao <> rsUpdate!Revisão Then
Dim recSet As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
cmdCommand.ActiveConnection = cnBanco
[ô]atualizar revisão no banco
strSQLAtualizar = [Ô]UPDATE documentacao SET Revisao=[Ô] & rsUpdate!Revisão & [Ô] WHERE Identificador =[Ô] & rsUpdate!identificador & [Ô][Ô]
[ô]cn.Open
[ô]cn.Execute strSQLAtualizar
[ô]Set recSet = Db.OpenRecordset(strSQLAtualizar)
[ô]recSet.AddNew
cmdCommand.CommandText = strSQLAtualizar
cmdCommand.CommandType = adCmdText
Set recSet = cmdCommand.Execute
MsgBox [Ô]pronto[Ô]
End If
teste = 1
End If
rsBanco.MoveNext
Loop
If teste = 0 Then
[ô]Iserir documento no banco
End If
rsUpdate.MoveNext
Loop
If Not rsBanco Is Nothing Then
rsBanco.Close
Set rsBanco = Nothing
End If
If Not rsUpdate Is Nothing Then
rsUpdate.Close
Set rsUpdate = Nothing
End If
If Not cnBanco Is Nothing Then
cnBanco.Close
Set cnBanco = Nothing
End If
If Not cnUpdate Is Nothing Then
cnUpdate.Close
Set cnUpdate = Nothing
End If
End Sub
Bem, quanto à recursos, se você se refere à finanças, fique sossegado, o Visual Studio é gratuito, incluindo para uso comercial. Baixe uma cópia e comece a desenvolver nele. Você vai perder muito menos tempo e evitar uma série gigantesca de dores de cabeça.
Está acontecendo algum erro? Ou só não funciona? Tem certeza que a variável enderecoUpdate está com o caminho correto?
Edit: talvez tu precise utilizar [documentacao$A:F] no UPDATE.
Edit: talvez tu precise utilizar [documentacao$A:F] no UPDATE.
Citação::
Está acontecendo algum erro? Ou só não funciona? Tem certeza que a variável enderecoUpdate está com o caminho correto?
Edit: talvez tu precise utilizar [documentacao$A:F] no UPDATE.
Está dando erro, [Ô]Nenhum valor foi fornecido para um ou mais parâmetro necessários[Ô]. Ele para na linha Set recSet = cmdCommand.Execute, onde recSet retorna [Ô]A variável do objeto ou a variável do bloco [ô]With[ô] não foi definida[Ô].
Eu já testei com [documentacao$A:F] no UPDATE e também não funcionou, o endereço está certo pq o rsUpdate está carregando corretamente.
Citação::
Bem, quanto à recursos, se você se refere à finanças, fique sossegado, o Visual Studio é gratuito, incluindo para uso comercial. Baixe uma cópia e comece a desenvolver nele. Você vai perder muito menos tempo e evitar uma série gigantesca de dores de cabeça.
Eu vou pensar nessa possibilidade, seria uma ótima saÃda, mais ainda assim gostaria de entender pq não está funcionando no Excel.
Obrigado.
Cara, sem querer desencorajar você, mas o que você ta fazendo é totalmente fora dos padrões, é claro que se você tem tempo pra lidar com todos os problemas que essa solução vai apresentar ótimo.
Eu nunca vi, pode ser que exista, mas usar uma planilha do excel como banco de dados, e o power point como interface do usuário, é realmente novo pra mim, nunca vi isso, eu entendo que as planilhas tem linhas e colunas, mas nunca vi sendo usado como banco de dados.
Conforme comentado acima use o visual studio, é grátis e vai oferecer a você muito mais do que de fato você precisa, e o tempo que você vai perder, você aproveita para aprimorar seus conhecimentos em uma ferramenta muito melhor que é o visual studio.
Sua solução pode até ser que funcione, mas vai te dar tanto trabalho que nem vale apena investir o tempo nisso, e você ainda fica preso a uma versão antiga do office.
O seu codigo pode ser aproveitado usando ADO.NET, é só mudar a string de conexão e algumas adaptações e criar as tabelas no banco de dados.
E se quiser mais facilidade ainda, use o EntityFramework, ai você verá que sua dor será bem menor.
Abraços
Eu nunca vi, pode ser que exista, mas usar uma planilha do excel como banco de dados, e o power point como interface do usuário, é realmente novo pra mim, nunca vi isso, eu entendo que as planilhas tem linhas e colunas, mas nunca vi sendo usado como banco de dados.
Conforme comentado acima use o visual studio, é grátis e vai oferecer a você muito mais do que de fato você precisa, e o tempo que você vai perder, você aproveita para aprimorar seus conhecimentos em uma ferramenta muito melhor que é o visual studio.
Sua solução pode até ser que funcione, mas vai te dar tanto trabalho que nem vale apena investir o tempo nisso, e você ainda fica preso a uma versão antiga do office.
O seu codigo pode ser aproveitado usando ADO.NET, é só mudar a string de conexão e algumas adaptações e criar as tabelas no banco de dados.
E se quiser mais facilidade ainda, use o EntityFramework, ai você verá que sua dor será bem menor.
Abraços
Citação::
Cara, sem querer desencorajar você, mas o que você ta fazendo é totalmente fora dos padrões, é claro que se você tem tempo pra lidar com todos os problemas que essa solução vai apresentar ótimo.
Eu nunca vi, pode ser que exista, mas usar uma planilha do excel como banco de dados, e o power point como interface do usuário, é realmente novo pra mim, nunca vi isso, eu entendo que as planilhas tem linhas e colunas, mas nunca vi sendo usado como banco de dados.
Conforme comentado acima use o visual studio, é grátis e vai oferecer a você muito mais do que de fato você precisa, e o tempo que você vai perder, você aproveita para aprimorar seus conhecimentos em uma ferramenta muito melhor que é o visual studio.
Sua solução pode até ser que funcione, mas vai te dar tanto trabalho que nem vale apena investir o tempo nisso, e você ainda fica preso a uma versão antiga do office.
O seu codigo pode ser aproveitado usando ADO.NET, é só mudar a string de conexão e algumas adaptações e criar as tabelas no banco de dados.
E se quiser mais facilidade ainda, use o EntityFramework, ai você verá que sua dor será bem menor.
Abraços
Amigos, vcs me convenceram! Já estou baixando o Visual Studio kkkk Vamos fazer a coisa certa né?
Obrigado pelas dicas!
Abraços!
Só pela curiosidade... teria como postar a planilha pra eu reproduzir o erro? Mesmo tu indo pro Visual Studio, seria bom pra aprender o que deu.
Cara, eu sei exatamente o que você quer dizer com recursos hahahaha
Sempre programei, mas por força do destino acabei fazendo engenharia e fui parar numa multinacional pronto pra criar vários sistemas boladões para gerenciamento de dados.
Pensei [Ô]quando os caras verem o que eu faço, vou aplicar cálculo numérico ali pra otimização, fazer um sisteminha usando mÃnimos quadrados pra poder fazer previsão de demanda[Ô]. Tudo que sempre sonhei!
Quebrei a cara. Instalaram um Office 2003 (Sim, não digitei errado... e isso não tem nem 2 anos). E me mandaram fazer tudo com Excel hahaha
Briguei com a galera do TI pra deixarem eu instalar meu Visual Studio Express e eles não liberaram. Falaram que não podia liberar isso. A pergunta é: [Ô]Se é de graça, por que não posso instalar?[Ô]. Simplesmente cagaram pra mim.
Aà beleza. Contrataram uma empresa JAPONESA pra criarem um sistema de gestão de defeitos lá. Aà pensei [Ô]Poxa, o negócio deve ser do caralho[Ô]. Quando foi ver, o sistema não atendia e era apenas de Input. Ou seja, os operadores colocavam os dados lá... E depois pra poder fazer um relatório, só tinha um botão de [Ô]Exportar csv[Ô]. Tinha que exportar tudo para o Excel e montar tudo lá e montar os relatórios.
Numa reunião, reclamei disso. Que era trabalho a toa, que podÃamos ter uma análise muito mais rápida e apurada. Pedi pro pessoal do TI liberar um usuário no SQL Server pra mim com permissão Somente Leitura. Pra poder fazer minhas queries de boa. Falaram que iria dar muito problema de segurança ¬¬ Que usuários Somente Consulta poderiam criar [Ô]Consultas hackers pra danificar o banco de dados e parar o sistema[Ô] (Desculpem galera do TI, mas tem muito nego incompetente na área de vocês).
Resumindo, a solução foi fazer umas gambiarras cruéis usando Excel como banco de dados, macros de importação e relatórios com VBA na veia. Quando automatizei tudo, meu contrato de estágio acabou e não me efetivaram hahaha Como me arrependo de ter feito isso...
Mas choros a parte... Vamos ao seu problema:
Se quer apenas atualizar seu banco de dados, faça só isso:
Se você ainda quiser usar o SelectCommand, mude essa linha:
Set recSet = cmdCommand.Execute
Por esta:
cmdCommand.Execute
Se não me engano, o método Execute retorna a quantidade de linhas que foram afetadas pela sua Query.
Ao contrário do VB.NET, o VB6 tem o conceito de Atribuição de dados e Atribuição de Objetos. Tem essa diferença.
Quando você usa o Set, automaticamente está falando que está atribuindo um valor de um objeto. Só que sua função retorna um dado primitivo, e não um objeto. Que usando a nomenclatura do VB6, seria tratado como Let.
Mas como o pessoal falou, dá uma olhada no .NET também. O problema todo, é quando a empresa tem algumas burocracias imbecis que impedem o trabalhador diferenciado de mostrar seu talento (pra quem não entendeu, o trabalhador diferenciado sou eu hahahah)
Espero ter ajudado!
Sempre programei, mas por força do destino acabei fazendo engenharia e fui parar numa multinacional pronto pra criar vários sistemas boladões para gerenciamento de dados.
Pensei [Ô]quando os caras verem o que eu faço, vou aplicar cálculo numérico ali pra otimização, fazer um sisteminha usando mÃnimos quadrados pra poder fazer previsão de demanda[Ô]. Tudo que sempre sonhei!
Quebrei a cara. Instalaram um Office 2003 (Sim, não digitei errado... e isso não tem nem 2 anos). E me mandaram fazer tudo com Excel hahaha
Briguei com a galera do TI pra deixarem eu instalar meu Visual Studio Express e eles não liberaram. Falaram que não podia liberar isso. A pergunta é: [Ô]Se é de graça, por que não posso instalar?[Ô]. Simplesmente cagaram pra mim.
Aà beleza. Contrataram uma empresa JAPONESA pra criarem um sistema de gestão de defeitos lá. Aà pensei [Ô]Poxa, o negócio deve ser do caralho[Ô]. Quando foi ver, o sistema não atendia e era apenas de Input. Ou seja, os operadores colocavam os dados lá... E depois pra poder fazer um relatório, só tinha um botão de [Ô]Exportar csv[Ô]. Tinha que exportar tudo para o Excel e montar tudo lá e montar os relatórios.
Numa reunião, reclamei disso. Que era trabalho a toa, que podÃamos ter uma análise muito mais rápida e apurada. Pedi pro pessoal do TI liberar um usuário no SQL Server pra mim com permissão Somente Leitura. Pra poder fazer minhas queries de boa. Falaram que iria dar muito problema de segurança ¬¬ Que usuários Somente Consulta poderiam criar [Ô]Consultas hackers pra danificar o banco de dados e parar o sistema[Ô] (Desculpem galera do TI, mas tem muito nego incompetente na área de vocês).
Resumindo, a solução foi fazer umas gambiarras cruéis usando Excel como banco de dados, macros de importação e relatórios com VBA na veia. Quando automatizei tudo, meu contrato de estágio acabou e não me efetivaram hahaha Como me arrependo de ter feito isso...
Mas choros a parte... Vamos ao seu problema:
Dim recSet As New ADODB.Recordset
Dim cmdCommand As New ADODB.Command
cmdCommand.ActiveConnection = cnBanco
[ô]atualizar revisão no banco
strSQLAtualizar = [Ô]UPDATE documentacao SET Revisao=[Ô] & rsUpdate!Revisão & [Ô] WHERE Identificador =[Ô] & rsUpdate!identificador & [Ô][Ô]
[ô]cn.Open
[ô]cn.Execute strSQLAtualizar
[ô]Set recSet = Db.OpenRecordset(strSQLAtualizar)
[ô]recSet.AddNew
cmdCommand.CommandText = strSQLAtualizar
cmdCommand.CommandType = adCmdText
Set recSet = cmdCommand.ExecuteSe quer apenas atualizar seu banco de dados, faça só isso:
strSQLAtualizar = [Ô]UPDATE documentacao SET Revisao=[Ô] & rsUpdate!Revisão & [Ô] WHERE Identificador =[Ô] & rsUpdate!identificador & [Ô][Ô]
cnBanco.Execute strSQLAtualizar Se você ainda quiser usar o SelectCommand, mude essa linha:
Set recSet = cmdCommand.Execute
Por esta:
cmdCommand.Execute
Se não me engano, o método Execute retorna a quantidade de linhas que foram afetadas pela sua Query.
Ao contrário do VB.NET, o VB6 tem o conceito de Atribuição de dados e Atribuição de Objetos. Tem essa diferença.
Quando você usa o Set, automaticamente está falando que está atribuindo um valor de um objeto. Só que sua função retorna um dado primitivo, e não um objeto. Que usando a nomenclatura do VB6, seria tratado como Let.
Mas como o pessoal falou, dá uma olhada no .NET também. O problema todo, é quando a empresa tem algumas burocracias imbecis que impedem o trabalhador diferenciado de mostrar seu talento (pra quem não entendeu, o trabalhador diferenciado sou eu hahahah)
Espero ter ajudado!
Tópico encerrado , respostas não são mais permitidas