How can I programatically execude an SQL query against
sheet(1).range() and populate the result set into sheet
(2). I am currently using the "Import External
Data" "Database Query" method, but I don't like it, as as
soon as you start to mess with the result set it wrecks
the range, and the query is lost.
I would like to not have any named range created storing
the query, I plan to manipulate the results. I would like
the solution to be totally VBA code, so the query is
remembered, and once the query is run, the data is just
data, not a refreshable, or auto updateable range.

I had done something like this years ago, but forget the
sintax. I remember there was a way to code and ODBC
definition, without having to cread an ODBC datasource
from control pannel. I don't want users to have any
dependencies, the spreadsheet should be totally self
contained.

Hi Dave,

Here is some code

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range

ActiveWorkbook.Save
sFile = ActiveWorkbook.FullName

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
Worksheets("Sheet2").Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dave Laundry" wrote in message
...
How can I programatically execude an SQL query against
sheet(1).range() and populate the result set into sheet
(2). I am currently using the "Import External
Data" "Database Query" method, but I don't like it, as as
soon as you start to mess with the result set it wrecks
the range, and the query is lost.
I would like to not have any named range created storing
the query, I plan to manipulate the results. I would like
the solution to be totally VBA code, so the query is
remembered, and once the query is run, the data is just
data, not a refreshable, or auto updateable range.

I had done something like this years ago, but forget the
sintax. I remember there was a way to code and ODBC
definition, without having to cread an ODBC datasource
from control pannel. I don't want users to have any
dependencies, the spreadsheet should be totally self
contained.

I love news groups, and the people that participate it
them.
Thanks, that was just the snipet I needed.
I swapped out the sheet1$ for my named range, and all is
perfect in my world.

-----Original Message-----
Hi Dave,

Here is some code

Public Sub Query()
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim sFile As String
Dim oOrig As Worksheet
Dim osh As Worksheet
Dim oCell As Range

ActiveWorkbook.Save
sFile = ActiveWorkbook.FullName

' Create the connection string.
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0;"

' Query based on the worksheet name.
sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, 0, 1, 1

If Not oRS.EOF Then
Worksheets("Sheet2").Range
("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dave Laundry" wrote in message
...
How can I programatically execude an SQL query against
sheet(1).range() and populate the result set into sheet
(2). I am currently using the "Import External
Data" "Database Query" method, but I don't like it, as
as
soon as you start to mess with the result set it wrecks
the range, and the query is lost.
I would like to not have any named range created
storing
the query, I plan to manipulate the results. I would
like
the solution to be totally VBA code, so the query is
remembered, and once the query is run, the data is just
data, not a refreshable, or auto updateable range.

I had done something like this years ago, but forget
the
sintax. I remember there was a way to code and ODBC
definition, without having to cread an ODBC datasource
from control pannel. I don't want users to have any
dependencies, the spreadsheet should be totally self
contained.


.