Excel, How do I change Servers??? 1

[hneal98]

I have an excel spreadsheet that imports data from SQL Server via the Import External Data query. I want to change the server, but can't find anything on how to do this.

Anyone have any ideas?

Thanks,

harvard

 

[SkipVought]

Hi,

I do this often. However, it takes VBA code.

Turn on your macro recorder.

Edit the query. In the QBE grid, just Edit/Return data to Excel. Turn off the recorder.

alt+F11 to toggle between the VB Editor and the active sheet.

In the VB Editor observe your recorded code. You will see a Connection parameter with text that is assigned. This is where your server is specified. Change accordingly.

If you need help customizing your code, please post back with your code, in VBA Visual Basic for Applications (Microsoft) Forum707.

Skip,

_[red][/red]

 

[hneal98]

This is pretty cool. I never thought of doing it this way. Now, do I need to do anything else or just leave it as is?

Thanks again for your help.

 

[SkipVought]

As a matter of practice, I code almost all my MS Query queries this way. There's always a chance that a server will change. If others run the query, they often have servers mapped to different drives. You can assure that the server connection be drive-independent, via the connect string.

But the short answer is, no, there is nothing else that needs to be done.

Skip,

_[red][/red]

 

[hneal98]

Again, this is great. Thanks for the tip.