This is not a standard Excel function - it looks like
sombody made one for you.
Generally, you would use ADODB to open a connection - the
first part of your parameter list is the connection
string. Next, you would open a recordset to the database
passing the SQL query, the second part of your parameter
list, and the database would respond by populating the
recordset with data.
The following code I what I used - adapted for this
demo...
Add a standard module, set a reference (Tools/References)
to Microsoft Active Data Objects Library 2.7 (or 2.6) and
copy this:
Dim Conn As ADODB.Connection
Dim RST As ADODB.Recordset
Dim strConn As String
Dim SQL As String
Dim ws As Worksheet, wb As Workbook, cl As Long
strConn = "Provider=MSDASQL.1;Extended" _
& " Properties=DSN=Excel " & _
"Files;DBQ=C:\Test SQL\Test.xls"
Set Conn = New ADODB.Connection
Conn.Open strConn
Set RST = New ADODB.Recordset
SQL = "SELECT * FROM [TestRange]"
RST.Open SQL, Conn, adOpenStatic
If Not RST.EOF Then
Set wb = Workbooks.Add(xlWBATWorksheet)
Set ws = wb.ActiveSheet
For cl = 1 To RST.Fields.Count
ws.Cells(1, cl).Value = _
RST.Fields(cl - 1).Name
Next
ws.Range("A2").CopyFromRecordset RST
Set ws = Nothing
Set wb = Nothing
End If
RST.Close
Conn.Close
Set RST = Nothing
Set Conn = Nothing
End Sub
My Tesst.xls has several columns of data ( a table) which
I range named "TestRange" - this is rferenced in the SQL
query.
Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
I have typed the following string into a cell in Excel
=SQL.REQUEST("DSN=Excel Files;DBQ=C:\Test SQL\test.xls",
B1, 1, "Select * from sheet1", TRUE)
The cell shows "#N/A". I am expecting it to return the
values from the "Test" spreadsheet to the spreadsheet
where I typed the sql command. What am I doing wrong?
Also, is there somewhere to look for examples and tips
on this command and sql syntax?
