ExcelBanter - How tp query a spreadsheet table?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How tp query a spreadsheet table? (https://www.excelbanter.com/excel-programming/296071-how-tp-query-spreadsheet-table.html)

Henrik[_5_]

How tp query a spreadsheet table?

Hi!

I would like to run a sql query by using vba on a table in the same
workbook as my vba code resides. I don't want to migrate my data to
Access, a text file etc. Basically what I want to do is:

1. Create a recordset.
2. Populate the recordset with the whole table
3. Query the recordset. Something like "Select * From XXX Where Nr = 1
Or Nr = 3"

4. Output the result in a new table.

Is there a way to do this.???

I am using XP, Excel 2000.

TIA
Henrik

onedaywhen

How tp query a spreadsheet table?

(Henrik) wrote in message . com...

I would like to run a sql query by using vba on a table in the same
workbook as my vba code resides. I don't want to migrate my data to
Access, a text file etc. Basically what I want to do is:

1. Create a recordset.
2. Populate the recordset with the whole table
3. Query the recordset. Something like "Select * From XXX Where Nr = 1
Or Nr = 3"

4. Output the result in a new table.

Is there a way to do this.???

I am using XP, Excel 2000.

You can use ADO to do this. However, due to a memory leak bug (MSDN
Q319998)
you should not query an open workbook. So a slight work around
involves copying the worksheet(s) you as querying to a temporary
workbook, querying the (closed) temp workbook and updating your open
workbook.

Below is some example code. Try it in a new module in a *copy* of your
workbook that contains the XXX sheet.

Note rather than open the whole 'table', I'm opening the recordset
based on your SELECT query. If you really must, you can open the
entire table (i.e. no WHERE clause) and specify the WHERE clause in
the recordset's Filter property.

Option Explicit

Sub Test()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim Target As Excel.Range
Dim Con As Object
Dim rs As Object
Dim strCon As String
Dim strPath As String
Dim strSql1 As String
Dim lngCounter As Long

' Amend the following constants to suit
Const FILENAME_XL_TEMP As String = "" & _
"delete_me.xls"
Const TABLE_NAME_CURRENT As String = "" & _
"XXX"
Const TABLE_NAME_NEW As String = "" & _
"MyNewTable"

' Do NOT amend the following constants
Const CONN_STRING_1 As String = "" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=<PATH<FILENAME;" & _
"Extended Properties='Excel 8.0;HDR=YES'"

' Build connection strings
strPath = ThisWorkbook.Path & _
Application.PathSeparator

strCon = CONN_STRING_1
strCon = Replace(strCon, _
"<PATH", strPath)
strCon = Replace(strCon, _
"<FILENAME", FILENAME_XL_TEMP)

' Build sql statement
strSql1 = ""
strSql1 = strSql1 & "SELECT * FROM "
strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]"
strSql1 = strSql1 & " WHERE nr=1 OR nr=3"

' Delete old instance of temp workbook
On Error Resume Next
Kill strPath & FILENAME_XL_TEMP
On Error GoTo 0

' Save copy of worksheet to temp workbook
Set wb = Excel.Application.Workbooks.Add()
With wb
ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _
Copy .Worksheets(1)
.SaveAs strPath & FILENAME_XL_TEMP
.Close
End With

' Open connection to temp workbook
Set Con = CreateObject("ADODB.Connection")
With Con
.ConnectionString = strCon
.Open
Set rs = .Execute(strSql1)
End With

Set ws = ThisWorkbook.Worksheets.Add
With ws
.Name = TABLE_NAME_NEW
Set Target = .Range("A1")
End With

With rs
For lngCounter = 1 To .fields.Count
Target(1, lngCounter).Value = _
.fields(lngCounter - 1).Name
Next
End With

Target(2, 1).CopyFromRecordset rs

Con.Close

End Sub

--

Henrik Ekeblad

How tp query a spreadsheet table?

Many thanks!

It seems to be what I have been looking for. Sorry to notice the memory
leak otherwise it had been av very neat solution.

I will try this one!

Best regards
Henrik

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

All times are GMT +1. The time now is 03:18 AM.