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!

 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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)


 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top