Execute SQL SELECT statement against a range in current workbook? 1

[VBAjedi]

Does anyone know if it is possible to use VBA to execute a SQL statement (SELECT) against a range in a workbook, then write the result to another range in the same workbook?

For example, I'd like to say (pseudo-code):
SELECT *
FROM Range("A3:H22&quot
WHERE Category = 'Parts', SubCategory = 'Misc', Available = 'Now' or 'Soon', and Price < 100

and write the results to Range &quot;A40&quot;

Am I asking too much?



Thanks!

VBAjedi

 

[SkipVought]

Hey VBA...

Just answered one just like this one.

I believe that the answer is NO!

But, don't give up...

1. Have one table per sheet
2. Name the table with sheet name
3. start table in A1
4. table must be contiguous
5. in the Worksheet_Change event, call this procedure

Sub ToggleObject()
    Application.DisplayAlerts = False
    With cells(1,1).CurrentRegion
        ActiveWorkbook.Names.Add _
            Name:="ActiveSheet.Name", _
            RefersTo:="=" & ActiveSheet.Name & "!" & .Address
        .CreateNames _
            Top:=True, _
            Left:=False, _
            Bottom:=False, _
            Right:=False
    End With
End Sub

6. On a separate sheet, record a new query
7. Modify the query so that the.connect, .SQL or .CommandText statement includes your variables. and

With Querydef(1)
   .Connect = YourConnectString
   .SQL = YourQuery
   .Refresh
End With

8. Change data in table
9. save workbook
10. run query

VOLA! Skip,
SkipAndMary1017@mindspring.com

 

[VBAjedi]

Clever! I'm not sure that I will use that approach for this particular application, but I will definitely file that one for future use.

Thanks!

VBAjedi

 

[DaleWatson123321]

VBAjedi,

Because it appears you're still looking for an &quot;SQL-equivalent&quot; solution to extracting data from one worksheet to another, I'd like to demonstrate the power of Excel's &quot;database functionality&quot;. This includes both database functions such as =DSUM, =DCOUNTA, etc, Plus, it includes being able to selectively extract data from one &quot;database&quot; worksheet to another worksheet.

The best way of demonstrating this, would be for you to email me a copy of your file, or if you prefer, a &quot;scaled down&quot; version with enough data for me to work with. If required, replace any sensitive data with fictitious data that still reflects the type of data you're working with. I'll only need a small number of records.

I'll then modify the file and return it to you.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca