Query Table - Using a Cell Value

[clumzyfish]

Hi All,

I have a Query Table and would like to update the address used for the query, based on a cell value. Is this possible?

Here is my current VBA:

Sub Test()
'
' Test Macro
'

'
Application.ScreenUpdating = False
    Range("A4").Select
        Sheets("Data").Visible = True
    Sheets("Data").Select
    Range("A44").Select
    With Selection.ListObject.QueryTable
        .Connection = _
        "OLEDB;Provider=Microsoft.Office.List.OLEDB.2.0;Data Source="""";ApplicationName=Excel;Version=12.0.0.0"
        .CommandText = Array( _
        "<LIST><VIEWGUID>{23EE30A3-74DD-4FBE-8353-511EABD13B75}</VIEWGUID><LISTNAME>{D6D57BC4-E093-4F65-A273-0A8D72EA8B44}</" _
        , _
        "LISTNAME><LISTWEB>Insert real web address here</LISTWEB><LISTSUBWEB></LISTSUBWEB>" _
        , "<ROOTFOLDER>Insert real root folder here</ROOTFOLDER></LIST>")
        .Refresh BackgroundQuery:=False
    End With
    Sheets("Data").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Test Results").Select
Application.ScreenUpdating = True
End Sub

I guess I'm looking to change the .CommandText to display cell value A1 (which will have the replacement query or array, whichever can be done), if that's possible

Thanks!