Disconnecting from excel 2007 data source

Question

Hi!

I have and excel file which is connected to an external xml file and uses it as data source (every time I use "refresh all" it reads the xml file and updates the sheet with the data).

I want to disconnect from the data source (so that the current data will be copied to the sheet, and pressing "refresh all" will do nothing).

I tried to delete the connection from the connections list, but this DO cause an error on the next refresh (the excel is still trying to find the xml file).

I wanted to do it programmatically (in a macro), but right now I didn't even find a good way to do it in the UI.

Creating the connection differently (so that it will only copy the values in the first place) is not a good solution for me, cause I do need to refresh the data from the file before disconnecting the xml).

Thanks!!!

Answer 1

In Excel 2003, to disconnect the query you will need to uncheck the "Save Query Definition" checkbox option. Under the "Data Range Properties..." when you right click your imported data. I'm sure it would be similar for Excel 2007.

This code will disconnect the querytable as well if your looking to do it programatically:

Sub DisconnectQuery(sheetName As String, qryName As String)

    Dim myQry As QueryTable

    Set myQry = Sheets(sheetName).QueryTables(qryName)

    myQry.Delete 'disconnects the querytable, data still remains
End Sub

Sub test()

    DisconnectQuery "YourQuery" 'the name is found under "Data Range Properties..."
End Sub