Change Excel data source

[tomkonec]

I would like to change the data source of an Excel spreadsheet to look to a different Microsoft SQL server location.

I have been given this macro however it does not seem to do anything when I run it:

------------------------------------------
Sub tom()
Dim qt As QueryTable
Dim sNew As String
Dim i As Integer
Dim j As Integer
For i = 1 To Sheets.Count
For j = 1 To SheetsCount - 1
For Each qt In Sheets(j).QueryTables
sNew = InputBox("Edit the String:", , qt.Connection)
If sNew <> "" And sNew <> qt.Connection Then
qt.Connection = sNew
End If
Next
Next j
Next i
End Sub
---------------------------------------------

I have also found a Microsoft K-base article 327572 however this only refers to Pivot Tables.

How can I do this?

 

[xlbo]

It will do something - you just won't see the results until after you refresh the data.

This may be easier to understand

dim CurrConnection as string, NewConnection as string
dim theAnswer
with activesheet.querytables(1)
    CurrConnection = .connection
    msgbox CurrConnection
    NewConnection = Inputbox("Please enter new connection string")
    .connection = NewConnection
    theAnswer = msgbox("Do you wish to run the query?,vbyesno)
    if theAnswer = vbyes then
        .refresh (false)
    else
    end if
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question