PIECE OF CAKE - Connecting to Oracle 12g with Excel VBA

Um aplicativo muito antigo ao qual presto suporte há vários anos carrega dados de planilhas do Excel em um banco de dados de relatórios. Essas planilhas do Excel sempre foram atualizadas manualmente por vários usuários. No entanto, como os dados que os usuários estão inserindo nessas planilhas são primeiro inseridos em outro banco de dados de aplicativos, esses usuários têm feito uma entrada dupla - um processo redundante que pode ser facilmente corrigido por vários meios.

Idealmente, a solução para este problema seria extrair os dados do banco de dados do aplicativo e carregá-lo no banco de dados de relatórios usando um pacote SSIS. Infelizmente, isso exigiria alguma readequação do aplicativo que carrega dados no banco de dados de relatórios, e eu (e o cliente) não possuímos largura de banda para isso. Então, encontrei uma solução rápida que deixou todos felizes - Usar um script VBA para preencher automaticamente as planilhas com dados quando os usuários as abrirem.

A parte complicada aqui foi fazer com que o Excel se conectasse ao Oracle, com um mínimo de trabalho a ser feito nos PCs dos usuários, que estão longe da minha localização.

Primeiro, não exigir que esses usuários tenham o SQL Plus ou quaisquer ferramentas de desenvolvimento para Oracle, o software de cliente completo era desnecessário.

Tenho o software Instant Client instalado nos PCs, então adicionei as informações de conexão do banco de dados necessárias ao arquivo tnsnames.ora .

Nota: no Instant Client (ou pelo menos na nossa configuração, usando a versão 11.2.0.4), o arquivo tnsnames está em 
C: \ oracle \ instantclient_11_2_0_4 em vez de em C: \ oracle \ product \ 11.2.0.4 \ client_1 \ NETWORK \ ADMIN como normalmente seria no cliente Oracle completo.


A conexão no VBA foi bastante simples, mas não imediatamente óbvia: note que a seqüência de conexão inclui Microsoft ODBC Driver for Oracle em vez de um driver Oracle; Mesmo que isso seja usado, nenhuma conexão ODBC precisa ser configurada no Administrador de Fonte de Dados ODBC. É imperativo apenas que as entradas apropriadas existam no arquivo tnsnames.ora e que a Biblioteca de Objetos de Dados Microsoft ActiveX esteja instalada e referenciada no Excel. (Adicionar Referências, navegando para Ferramentas -> Referências no editor VBA no Excel.)

Dim SQL_String As String
Dim dbConnectStr As String
Dim con As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim strUid As String
Dim strPwd As String
Dim strEnv As String
Dim strDSN As String
Dim iRow As Integer    


strEnv = "prod"
strUid = "username"
strPwd = "password"

If strEnv = "prod" Then
    Let strDSN = "(prod database net_service_name* from tnsnames)"

Else
    Let strDSN = "(dev database net_service_name* from tnsnames)"

End If
      
dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
        "Server=" & strDSN & ";" & _
        "uid=" & strUid & ";pwd=" & strPwd & ";"
   
con.ConnectionString = dbConnectStr    
con.Open   

Let SQL_String = "(insert SQL query here)"
       
recset.Open SQL_String, con

iRow = 0 
Do While Not recset.EOF
     'Have a loop here to go through all the fields
    Let Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query

    
    ' &c. ...

    Let iRow = iRow + 1
    recset.MoveNext
Loop

recset.Close
con.Close

#A&A #PIECEOFCAKE #POC #VBA #oracle #Connect

Consulte-nos

⬛◼◾▪ Social Media ▪◾◼⬛

• FACEBOOK • TWITTER • INSTAGRAM • TUMBLR • GOOGLE+ • LINKEDIN • PINTEREST

⬛◼◾▪ Blogs ▪◾◼⬛ 

• VBA Specialist® • VBA Excel Specialist®

• VBA Access Specialist® • VBA Dashboards Specialist®

• VBA Infografics Specialist® • VBA Pharma Specialist® 

⬛◼◾▪ CONTATO ▪◾◼⬛

brazilsalesforceeffectiveness@gmail.com