Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from SQL Server into Excel
I am trying to pull data out of an SQL database from within my Excel workbook (VBA). The Help section advises that through References command (Tool menu), I establish a reference, which I do by checking the ActiveX Data Objects 2.7 library. However, this does not give me access to the functions that I require, namely SQLOpen, SQLExecQuery, SQLRetrieve, etc. Can anyone help me get access to these functions? Thank you.
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from SQL Server into Excel
Sophea,
It won't, it will give you access to ADO, which ia MS's proprietary (universal) data access layer. This works in conjunction with vendor data providers to give a uniform access layer. Wht you do is setup a connection string, connect to the data source, and then issue an SQL command. As an example Dim oConn As ADODB.Connection Set oConn = New Connection oConn.Open "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sophea" wrote in message ... I am trying to pull data out of an SQL database from within my Excel workbook (VBA). The Help section advises that through References command (Tool menu), I establish a reference, which I do by checking the ActiveX Data Objects 2.7 library. However, this does not give me access to the functions that I require, namely SQLOpen, SQLExecQuery, SQLRetrieve, etc. Can anyone help me get access to these functions? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from SQL Server into Excel
Should have added this to get the data
Dim oRS As ADODB.RecordSet Set oRS = New RecordSet SQLString = "Select * From myTable" Set oRS = oConn.Execute(SQLString) aryRecordSet = oRS.GetRows() ' puts rows into an array or Range("A2").CopyFromRecordset oRS 'put to worksheet range -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bob Phillips" wrote in message ... Sophea, It won't, it will give you access to ADO, which ia MS's proprietary (universal) data access layer. This works in conjunction with vendor data providers to give a uniform access layer. Wht you do is setup a connection string, connect to the data source, and then issue an SQL command. As an example Dim oConn As ADODB.Connection Set oConn = New Connection oConn.Open "Provider=sqloledb;" & _ "Data Source=(local);" & _ "Initial Catalog=myDatabaseName;" & _ "User ID=myUsername;" & _ "Password=myPassword" -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sophea" wrote in message ... I am trying to pull data out of an SQL database from within my Excel workbook (VBA). The Help section advises that through References command (Tool menu), I establish a reference, which I do by checking the ActiveX Data Objects 2.7 library. However, this does not give me access to the functions that I require, namely SQLOpen, SQLExecQuery, SQLRetrieve, etc. Can anyone help me get access to these functions? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from SQL Server into Excel
Bob, To my amazement, it worked! Thanks very much for your help.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting data from SQL Server into Excel
Why?<vbg
Is Sophea Richard? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Sophea" wrote in message ... Bob, To my amazement, it worked! Thanks very much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import data into SQL Server from Excel | Excel Discussion (Misc queries) | |||
Data Upload from Excel to SQL Server | Excel Discussion (Misc queries) | |||
Data Upload from Excel to SQL Server | Links and Linking in Excel | |||
SQL Server -- Bulk Insert from Excel to SQL Server | Excel Discussion (Misc queries) | |||
Excel 2003 Error Cannot Locate the Internet Server or Proxy Server | Excel Discussion (Misc queries) |