SQL.REQUEST #N/A error
 

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?

SQL.REQUEST #N/A error
 

This file might be a help:
http://www.bygsoftware.com/examples/...SqlRequest.zip
It's in the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

This workbook demonstrates how to get data direct from an MS Access table,
or from an open or closed MS Excel workbook using the workbook function
SQL.REQUEST.

Recently updated to show the use of SQL.REQUEST in the same workbook.

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Stuk" wrote in 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?

SQL.REQUEST #N/A error
 

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?
.

SQL.REQUEST #N/A error
 

Upto and including Excel 2002 SQL.REQUEST has been part of the XLODBC addin
supplied by Uncle Bill.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Patrick Molloy" wrote in message
...
This is not a standard Excel function - it looks like
sombody made one for you.

SQL.REQUEST #N/A error
 

Thanks - never used it. Recent MS updates have raised some issues with
OLEDB. We use ADO exclusively now...which my code also uses.
We found that an update about 2 weeks ago caused our pivot tables ( default
to OLEDB) were taking up to 45 minutes to refresh 300,000 rows from our
databases. Setting the pivot cash instead to an ADODB recordset got us back
to <2 minutes.

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
"Andy Wiggins" wrote in message
...
Upto and including Excel 2002 SQL.REQUEST has been part of the XLODBC
addin
supplied by Uncle Bill.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Patrick Molloy" wrote in message
...
This is not a standard Excel function - it looks like
sombody made one for you.

SQL.REQUEST #N/A error
 

The nice thing about SQL.REQUEST is that you can structure it to use
replacable filenames parameters (the "missing" feature of INDIRECT), and you
can also run UPDATE queries which will update data in closed Excel files.

The downside is that it's slow.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Patrick Molloy" wrote in message
...
Thanks - never used it. Recent MS updates have raised some issues with
OLEDB. We use ADO exclusively now...which my code also uses.
We found that an update about 2 weeks ago caused our pivot tables (
default
to OLEDB) were taking up to 45 minutes to refresh 300,000 rows from our
databases. Setting the pivot cash instead to an ADODB recordset got us
back
to <2 minutes.

--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
"Andy Wiggins" wrote in message
...
Upto and including Excel 2002 SQL.REQUEST has been part of the XLODBC
addin
supplied by Uncle Bill.

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Patrick Molloy" wrote in message
...
This is not a standard Excel function - it looks like
sombody made one for you.