Removing external queries from spreadsheet

[BajanPOET]

I'm trying to get some external queries to work in the background without asking the user if they want to enable or disable automatic refresh.

I want the user to save the purchase order as a new file, but I don't want the new file to open and keep asking if the refresh should be enable or disabled. Nothing I've tried so far has worked - the external queries were not set to refresh automatically at first - I was trying to get them to refresh using the Windows_Open procedure, but I couldn't get it to work.

What I would like to try is removing the external queries programatically when after the file is saved with its unique name. Is there a way that I can delete the named ranges and their underlying external queries without destroying the data - as in, if I have already used the query to give me data x, y and z, can I then delete the named reference and keep x, y and z on the spreadsheet and save it so that when I reopen the data is still there?

What? Who? ME????

 

[PHV]

A starting point:
ActiveSheet.QueryTables("yourNamedRange").Delete

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

kewl... let's see where I get from there

Thanks PHV

What? Who? ME????

 

[PHV]

In the Workbook_BeforeSave event procedure:
If ActiveSheet.QueryTables.Count > 0 Then
ActiveSheet.QueryTables("yourNamedRange").Delete
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BajanPOET]

I had placed the deletes before the save, as the save was happening via a command button... but that works as well

What? Who? ME????