How to fetch data from SnowFlake using Excel VBA?

The easiest way to fetch data from databases to Excel is using the Get Data option available under the Data Tab, Excel creates a query using the ODBC connection and fetches the data without the hassle of coding. But coding makes the process more flexible and is very dynamic.

Here goes the details...

1) Download and install the snowflake ODBC driver either 32 or 64 bit depending on your MS Office Installation & OS from the SnowFlake portal, available under the Help menu.

2) Create a DSN with the correct connection properties.

3) Add the below reference if required in the visual basic editor and also change the DSN name in the VBA code.

VBA Code...

 '-----------------------------------------------------------------------------------------------------------------------------------------

Sub VBA_SnowFlake_Connect()

Dim sSQLQry As String

Dim ReturnArray

Dim Conn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim DBPath As String, sconnect As String

Worksheets("VBA").Activate

ActiveSheet.Cells.Clear

DBPath = ThisWorkbook.FullName

Set Conn = New ADODB.Connection

'You can provide the full path of your external file as shown below

'DBPath ="C:\InputData.xlsx"

'---------------------------------- Connection String

'Change the DSN name and snowflake credentials

sconnect = "Provider=MSDASQL.1;DSN=My Snowflake;" & DBPath & ";HDR=Yes';Password=*****1;Warehouse=*****"

Conn.Open sconnect

'dbname = "SIS_REPORTING"

'schema = "PUBLIC"

'---------------------------------- SnowFlake Table Name

tblname = "MyEnrollmentDetails"

Set rs = New ADODB.Recordset

rs.ActiveConnection = Conn

'---------------------------------- Your SQL Statement

sSQLString = "Use warehouse MyTest"

sSQLString1 = "select * from SIS_REPORTING.PUBLIC." & Chr(34) & tblname & Chr(34)

   

   

rs.Open sSQLString

rs.Open sSQLString1

'---------------------------------- Output Sheet

Worksheets("VBA").Range("A2").CopyFromRecordset rs

       

Worksheets("VBA").Range("A1").Select

'---------------------------------- Getting Column Names

For intFieldIndex = 0 To rs.Fields.Count - 1

    Worksheets("VBA").Cells(1, intFieldIndex + 1).Value = rs.Fields(intFieldIndex).Name

Next intFieldIndex

Worksheets("VBA").Cells.EntireColumn.AutoFit

'---------------------------------- Close Recordset

rs.Close

'---------------------------------- Close Connection

Conn.Close

End Sub

'----------------------------------------------------------------------------------------------------------------------------------------------