Share via

Connect to Netezza / SQL through Excel VBA

  • Article

Scope

This Article is about how to automate data transfer from Netezza database to Microsoft excel

Solution

Below is a quick tip for you to automat the process
For this you need to know, your Netezza Driver name, server name, port name, database name
Connecting to Netezza throught 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 -  referance

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 reterive 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.