Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do you query a querytable in Excel? 1

Status
Not open for further replies.

snowmantle

Programmer
Jun 20, 2005
70
GB
Hi,

I have written the below sub to insert Access table data into my Excel file.

Code:
Sub InsertAccessTable(tableName As String)
'
    Dim MyDB As String, connStr As String, sqlStr As String
    MyDB = ThisWorkbook.path & "\" & "master.mdb"
    
    connStr = "ODBC;DSN=MS Access Database;DBQ=" & MyDB & ";DefaultDir=" & _
    ThisWorkbook.path & "\" & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"
    
    sqlStr = "SELECT * FROM " & tableName
    
    'check if a worksheet already exists, if it does then remove it
    If CommonMod.SheetExists(tableName) Then
        ActiveWorkbook.Worksheets(tableName).Delete
    End If
    
    'add a new sheet
    ActiveWorkbook.Worksheets.Add
    
    'loop through and check the desired name is not taken
    'if it is then delete it so the name can be added again
    Set nms = ActiveWorkbook.Names
    For i = 1 To nms.Count
        If nms(i).Name = tableName Then
            ActiveSheet.Name(tableName).Delete
        End If
    Next
    
    'set the name of the new sheet to the same as the table name
    ActiveSheet.Name = tableName
    
    'adds a new table of data to the new sheet
    'using the sql statement provided above
    With ActiveSheet.QueryTables.Add(Connection:=connStr, Destination:=Range("A1")) ', SQL:=sqlStr
        .CommandText = sqlStr
        .Name = tableName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I want to then be able to write a SQL statement to retrieve a specific recordset from this querytable, then put the result into a two dimensional array and loop through a range in a raw data worksheet doing a find and replace for the warehouse_name.

The SQL to query the new querytable would be something like the below
Code:
"SELECT warehouse_name from Warehouse where parent_fkey > 0

Does anyone have any examples on how this could be done?

I was hoping not to have to connect to the Access database everytime I need to query for new data because the file may not be accessible when this sub is being run, but if someone could recommend a better alternative than the above then I am all ears.

Links to better information on how to use QueryTables would be much appreciated, all I can seem to find is how they can be added to workbooks.. they seem fairly limited :(

 



Hi,

I was hoping not to have to connect to the Access database everytime I need to query for new data...

That's what a querytable does: it CONNECTS to the database in order to get new data.

First off, I would not code ADDING a querytable to your sheet. Every time that you run this it will add ANOTHER QueryTable to your sheet. You only need ONE. Just REFRESH it and you get new data.

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Ok.

The above InsertAccessTable sub removes any existing worksheet with the same name as the table and also the defined range name it requires..

As you mention this is intended only to be run once and then the data is refreshed by linking to the Access database.

What I want to do though is the below:

Example warehouse table data
Code:
warehouse_key	parent_fkey	businessgroup_fkey	country_fkey	warehouse_name
1	0	1	1	WarehouseA
2	0	2	19	Warehouse B
3	0	2	19	Warehouse C
4	2	0	0	Waaaahouse Beta

Raw data I want to look in and replace any incorrect warehouse names
Code:
warehouse_name
WarehouseA
WarehouseA
Warehouse B
WarehouseA
Warehouse C
Waaaahouse Beta
Warehouse D
Warehouse F
Waaaahouse Beta
Waaaahouse Beta
Warehouse C
Warehouse D
Warehouse F
WarehouseA

The plan is the parent_fkey links the incorrect warehouse name to the correct one, so that "Waaaahouse Beta" can be found and replaced with "Warehouse B".

This also involves having to write another function that will find warehouses such as "Warehouse F", that are not in the list above.. and then flag them up to the user so they can check if they are errors or should be new warehouse names that need to be imported into the Access [Warehouse] table before running the above filtering.

Hope the above makes a bit more sense.

Thanks
 



Why does this SQL not work?
Code:
sqlStr = "SELECT warehouse_name from Warehouse where parent_fkey > 0"

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Sorry I'm confusing matters, Im not sure how to use it.. I dont want to use the commandText property cos I dont want to change the querytable data.. I just want some information from it.

Want to do the below really now that I have thought about it:

Code:
SELECT w.warehouse_name
FROM Warehouse w1
WHERE w1.warehouse_key = (SELECT w2.parent_fkey from Warehouse w2 WHERE w2.warehouse_name = "Waaaahouse Beta")
 



OK, then do another query, on another sheet, with your querytable as the source data, using the SQL in question.

faq68-5829


Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Thanks for that,I kept getting an issue with the Excel file.. it couldn't find any tables in my workbook when using "Data > Import External Data > New Database Query" but it found some using "Import Data" in this same section however the SQL I couldnt get working.

I will have a look at that, for now I have it working by just creating a new QueryTable instead.

Is there a way to have the result returned in a variable for use in VBA?

Thanks
 


it couldn't find any tables in my workbook when using "Data > Import External Data > New Database Query

In Add Tables Table options, check ALL boxes.

Skip,
[glasses]Don't let the Diatribe...
talk you to death![tongue]

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top