Monday, September 4, 2006

Excel VBA - Code for importing a SQL Server table

Excel VBA - Code for importing a SQL Server table

Sub GetTableDataFromSQLServer()

' Declare the QueryTable object
Dim qt As QueryTable

' Set up the SQL Statement
sqlstring = 'select column1, column2, column3 from table'

' Set up the connection string, reference an ODBC connection
' Leave the name and password blank for NT authentication

connstring = 'ODBC;DSN=DataSouirceName;UID=;PWD=;Database=DatabaseName'

' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1

With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range('A1'), Sql:=sqlstring)

.Refresh

End With
End Sub

No comments: