Change Text in Query Table CommandText Property via Macro

Which version of Excel?

Regards,

Rory

XL 2007

Do all the querytables have their own connection, or do they use the same one (or few)?

they all have their own...

everything is the same in the connection...only differences are in the queries themselves

spattewar - if i understand correctly there is no way to loop through each query and do a find/replace in the commandtext. so, each query would have a separate sheet to store its own commandtext?

rorya - are u still with me on this one?

rorya - i have been attempting something similar. it would seem we are on the right track. were you able to get your code to work on an example? is stroldfield an actual field name used in the query (commandtext)? if not, how will the code know what field u are looking to change?

No, you would have to pass the old field name and the new field name to it - for example:

Sub ReplaceFields()

ChangeQueries "Field1", "Field2"

End Sub

I really appreciate you working on this w/me. The Code snippet below contains what I have for code so far. It runs, however, it doesn't look like anything is getting changed. Here is a simplified version of one of my queries:

Command Type:
SQL
Command Text:
SELECT RTRIM(PG_NAME) AS PGNAME
FROM X
WHERE YEAR = 2009

Sub ChangeQry(strOldField As String, strNewField As String)
    
    Dim myQT As QueryTable
    Dim qWkbk As Workbook
    Dim qSht As Worksheet
    
    Set qWkbk = ActiveWorkbook
    
    For Each qSht In qWkbk.Worksheets
        For Each myQT In qSht.QueryTables
            
            myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)
        
        Next myQT
    Next qSht
    
End Sub
Sub ChangeFld()
 
    ChangeQry "PG_Name", "PC_Name"
 
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:

What happens if you step through it (using f8 in the VB editor)?

I've just remembered that if the querytable is associated with a listobject (i.e. a Table) then it's not part of the Querytables collection. Try this adjustment to the main sub:

Sub ChangeQry(strOldField As String, strNewField As String)
    
    Dim myQT As QueryTable, objList as ListObject
    Dim qWkbk As Workbook
    Dim qSht As Worksheet
    
    Set qWkbk = ActiveWorkbook
    
    For Each qSht In qWkbk.Worksheets
        For Each myQT In qSht.QueryTables
            
            myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)
        
        Next myQT
        For each objList in qSht.Listobjects
           Set myQT = objList.Querytable
           If Not myQT Is Nothing then
              myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)
           End If
           Set myQT = Nothing
        Next objList
    Next qSht
    
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:

I made change...I stepped through with F8 and ran code...no errors...however, nothing is getting changed.

There is a Refresh missing!

Sub ChangeQry(strOldField As String, strNewField As String)
    
    Dim myQT As QueryTable, objList as ListObject
    Dim qWkbk As Workbook
    Dim qSht As Worksheet
    
    Set qWkbk = ActiveWorkbook
    
    For Each qSht In qWkbk.Worksheets
        For Each myQT In qSht.QueryTables
            
            myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)
            myQT.Refresh
        Next myQT
        For each objList in qSht.Listobjects
           Set myQT = objList.Querytable
           If Not myQT Is Nothing then
              myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)
              myQT.Refresh
           End If
           Set myQT = Nothing
        Next objList
    Next qSht
    
End Sub

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:

added refresh step...code runs, queries refresh...still no changes. when i step through query i can see that the line "myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)" is finding the commandtext and cycling through each query, but, for some reason the find/replace is not being performed.

I will see if I can find time to put together some samples for testing in 2007 (I don't use it at work). I should warn you that I am off on holiday for two weeks from Monday though!

ok..thanks for the heads-up and the effort. this will be a valuable function if we can get it to work. I will keep attempting different things and will post if i get it to work. if we do not touch base before, u have a great holiday!

replaced "myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField)" with "myQT.CommandText = Replace(myQT.CommandText, strOldField, strNewField, 1, -1, vbTextCompare)" and it looks like it worked...still some more testing to do, but, looks promising!!!! I will post full code and give u points a little later once i verify results etc.

Thanks!

A couple of quick question - what is the data source for these queries, and are you actually changing the underlying data field names, or just extracting different fields?

My final comment had final solution. i added small change to what rorya provided. points more than well deserved by rorya.