Share via

How to Connect to Netezza / SQL through Excel VBA

  • Article

Scope

This article outlines steps to move data from Netezza database to Microsoft excel.

Solution

There is always a requirement of pulling up regular data from your Netezza server and then updating the same in excel. Here is a quick tip for you to automate the process

For this you need to know, your Netezza Driver name, server name, port name, database name

Connecting to Netezza through VBA excel

Create a excel file :

Below are the reference you need to check in your file

Go to  VBA end i.e Alt+F11 then tools -  reference

Check in  -

1. Visual basic for application
**
2. Excel 15.0 object library

  1. Microsoft ActiveC Data objectx 6.1 Library(any version which is there in your list)

4.Microsoft DAO 3.6 object library (any version which is there in your list)**

once your check in below is the code :

which will help your connect to netezza / SQl database and retrieve the data in your excel sheet.

The Sql query can be placed in an range and you can refer the range to the code.

Sub Demo() 
Dim cmd As New ADODB.Command, Sto As New ADODB.Recordset, rsDAO As DAO.Recordset 
Dim i As Integer 
Dim t2 As Variant 
  
  
 cmd.ActiveConnection = "Driver={Drivername};servername=servername;port=portadress;database=*****; username=****;Password=;" 
   cmd.ActiveConnection.CursorLocation = aduseclient 
   cmd.CommandTimeout = 600 
   cmd.CommandType = adCmdText 
   t2 = Sheets("AA").Range("T3").Value 
  
   cmd.CommandText = t2 
     
     
   Set sto = cmd.Execute 
     
   Sheets("AA").Range("A1").CopyFromRecordset sto 
        
   cmd.ActiveConnection.Close 
     
End Sub

Conclusion

The above query will help you to get the data directly to your excel where you can code it to format and send.