delete external data query

[Mark C.]

Hi -
My spreadsheet has a link to a SQL Server View. Created by Data/Import
External Data/ New Database Query.
I want to change the spreadsheet to access a different View. I have gone to
Edit Query and tried to find a way to either remove the link to the View or
access the new View but I can't seem to find what I need to do. I appreciate
any help from someone more knowledgeable that I.
Thank you.

 

[Andy Smith]

You have to change the query's Connection property. To find out what it is,
add a line in your code to display it in the Debug window:

Debug.Print <querytable_object>.Connection

Make that line a breakpoint, run your code, and hit F8 when it break. Then
open the Immediate window (Ctrl-G in the VBE), and somewhere you'll see the
name of the view you originally used.

To get data from another view, you'll probably need code like this:

With <querytable_object>
.Connection = Replace$(.Connection, "OldViewName", "NewViewName")
.Refresh
End With