Changing a QueryTable data source

I've set up a QueryTable using "import external data". Works fine, except I
want to change my datasource. When you import, it pulls in your machine
name, hard coded database name, etc. I don't want that stuff. I want this
much

"ODBC;DSN=MyDB;Trusted_Connection=Yes"

Is there a way to change this from the UI? If not, a way to do it
programmatically?

Thanks

Mike

You can change the Connection and CommandText properties of the QueryTable
object. I've never really done it from the import side, just the standard
query table so it may be slightly different. I'll be it's real similar to
this

http://www.*****-clicks.com/excel/Ex....htm#ChangeSQL

****

Mike Jones wrote:
> I've set up a QueryTable using "import external data". Works fine,
> except I want to change my datasource. When you import, it pulls in
> your machine name, hard coded database name, etc. I don't want that
> stuff. I want this much
>
> "ODBC;DSN=MyDB;Trusted_Connection=Yes"
>
> Is there a way to change this from the UI? If not, a way to do it
> programmatically?
>
> Thanks

What I'm seeing is that I can change it programmatically like this, but when
I save my XL spreadsheet, then reopen, its back to how it originally was;
with all the extra, unwanted stuff.

Strange.

"**** Kusleika" <dkusleika@gmail.com> wrote in message
news:%23hJapzOUFHA.3448@TK2MSFTNGP10.phx.gbl...
> Mike
>
> You can change the Connection and CommandText properties of the QueryTable
> object. I've never really done it from the import side, just the standard
> query table so it may be slightly different. I'll be it's real similar to
> this
>
> http://www.*****-clicks.com/excel/Ex....htm#ChangeSQL
>
> ****
>
> Mike Jones wrote:
>> I've set up a QueryTable using "import external data". Works fine,
>> except I want to change my datasource. When you import, it pulls in
>> your machine name, hard coded database name, etc. I don't want that
>> stuff. I want this much
>>
>> "ODBC;DSN=MyDB;Trusted_Connection=Yes"
>>
>> Is there a way to change this from the UI? If not, a way to do it
>> programmatically?
>>
>> Thanks
>
>

Mike

Oh, I see. I don't think you can prevent Excel from doing what it wants to
your strings. If you describe your situation and why you want to do this,
maybe someone will have another way to accomplish it.

--
**** Kusleika
Excel MVP
Daily Dose of Excel
www.*****-blog.com

Mike Jones wrote:
> What I'm seeing is that I can change it programmatically like this,
> but when I save my XL spreadsheet, then reopen, its back to how it
> originally was; with all the extra, unwanted stuff.
>
> Strange.
>
> "**** Kusleika" <dkusleika@gmail.com> wrote in message
> news:%23hJapzOUFHA.3448@TK2MSFTNGP10.phx.gbl...
>> Mike
>>
>> You can change the Connection and CommandText properties of the
>> QueryTable object. I've never really done it from the import side,
>> just the standard query table so it may be slightly different. I'll
>> be it's real similar to this
>>
>> http://www.*****-clicks.com/excel/Ex....htm#ChangeSQL
>>
>> ****
>>
>> Mike Jones wrote:
>>> I've set up a QueryTable using "import external data". Works fine,
>>> except I want to change my datasource. When you import, it pulls in
>>> your machine name, hard coded database name, etc. I don't want that
>>> stuff. I want this much
>>>
>>> "ODBC;DSN=MyDB;Trusted_Connection=Yes"
>>>
>>> Is there a way to change this from the UI? If not, a way to do it
>>> programmatically?
>>>
>>> Thanks