Connect to MS SQL server through Excel VBA

lroca

New Member
Joined
Jul 17, 2009
Messages
6
I am confused on how to set up my credentials in order to connect and extract data from MS SQL Server using Excel VBA. I know this is possible with an ADO connection, I am not sure of how to setup my code to access my sql database.

Can someone please send me a simple code to access my SQL database and place the information in cell "A1"?

Here are my credentials and SQL statement:

Server Name: dol-sql\sqldatabase
Database: test
Username: john
Password: smith

SQL:
Select GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account,
Sum(GL_Balance.Year_Start_Value)
From GL_Balance
Group By GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account
Order By GL_Balance.Fiscal_Year, GL_Balance.Main_Account, GL_Balance.Sub_Account


Thanks!

 

jrikhraj

New Member
Joined
Apr 5, 2010
Messages
4
I understand that this is very late but maybe some one else can benefit from my reply

Have you tried using an ODBC Connection. I use it to pull in data from SQL DB into Excel without any problems. Attached is a code snipped that can get you started

'--------------------------------------------------------------------
'Setting the network ODBC connections
Dim sConn As String
Dim oQt As QueryTable
Dim sSql As String

'defining the connection string
sConn = "ODBC;DSN=<ODBC DSN NAME>;UID=<User ID>;PWD=<Password>;APP=Microsoft Office 2003;"
sConn = sConn & "WSID=<Workstation name>;DATABASE=<SQL database name>;Network=DBMSSOCN"

sSql = "Write your SQL query here"

Set oQt = Sheet1.QueryTables.Add( _
Connection:=sConn, _
Destination:=Sheet1.Range("A1"), _
Sql:=sSql)

With oQt
.Name = "Query from"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

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

Jaspreet
 
Last edited:

jrikhraj

New Member
Joined
Apr 5, 2010
Messages
4
The WSID setting should be something like this.. for some reason I am not able to edit my reply there
WSID=<Machine Name>
 
Last edited:

jrikhraj

New Member
Joined
Apr 5, 2010
Messages
4
Okay. For some reason I am not able to write "Machine Name" in the 'WSID' setting. Also, I am not able to delete my posts. Moderator, can you help?
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,790
Office Version
365
Platform
Windows
Hi

Try placing the string in code tags (see bottom of my signature).
 

lroca

New Member
Joined
Jul 17, 2009
Messages
6
I found an excellent code that enabled me to conect to MySQL database and paste the report on any cell.


Sub GetDataFromADO()

'Declare variables
Set objMyConn = New ADODB.Connection
Set objMyCmd = New ADODB.Command
Set objMyRecordset = New ADODB.Recordset

'Open Connection
objMyConn.ConnectionString = "Provider=SQLOLEDB.1;Password=1234;Persist Security Info=True;User ID=Test;Initial Catalog=TestData;Data Source=m:\testserver;"
objMyConn.Open

'Set and Excecute SQL Command
Set objMyCmd.ActiveConnection = objMyConn
objMyCmd.CommandText = "Select Account.Main_Account, Account.Sub_Account, Account.Name From Account"
objMyCmd.CommandType = adCmdText
objMyCmd.Execute

'Open Recordset
Set objMyRecordset.ActiveConnection = objMyConn
objMyRecordset.Open objMyCmd

'Copy Data to Excel
ActiveSheet.Range("A1").CopyFromRecordset (objMyRecordset)

'Close Connection
objMyConn.Close

End Sub


  • If you get a user defined error, make sure you have the correct refferences checked:

    Menu: Tools/References (Microsoft ActiveX Data Objects 2.8 Library)


 

Forum statistics

Threads
1,085,369
Messages
5,383,233
Members
401,818
Latest member
MrMisster

Some videos you may like

This Week's Hot Topics

Top