I'm new to using excel at a presentation tool for data
stored in a SQL Server database. I set up a data source
locally on my development machine between my development
SQL Server and Excel. Everything works fine and I am able
to make my graphs, etc. Now I'd like to distribute the
Excel workbook to management and change the data source to
look at the production SQL Server. I can edit the query,
but I can't get to anything that allows me to point to a
different data source, change users for security purposes,
passwords or databases. There has to be something there
I'm just not seeing. I can't imaging having to recreate
every Excel file just because the server gets renamed, add
or change users, etc. Thanks for taking your time to
answering my question.

in the querytable's output range
rightclick,

select
Edit Query

via VBA here's a rough beginning..

Sub tst()

Dim qt As QueryTable
Dim sNew As String


For Each qt In ActiveSheet.QueryTables
sNew = InputBox("Edit the string:", , qt.Connection)
If sNew < "" And sNew < qt.Connection Then
qt.Connection = sNew
End If

Next
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

I'm new to using excel at a presentation tool for data
stored in a SQL Server database. I set up a data source
locally on my development machine between my development
SQL Server and Excel. Everything works fine and I am able
to make my graphs, etc. Now I'd like to distribute the
Excel workbook to management and change the data source to
look at the production SQL Server. I can edit the query,
but I can't get to anything that allows me to point to a
different data source, change users for security purposes,
passwords or databases. There has to be something there
I'm just not seeing. I can't imaging having to recreate
every Excel file just because the server gets renamed, add
or change users, etc. Thanks for taking your time to
answering my question.

Thanks for the reply. I've been though every part of the
Edit Query wizard and options. There is nothing there to
edit my connection string. But that is OK because VBA will
be faster than a GUI anyway.

Now I've got some questions about the VBA code. I modified
your code to simply view the existing connection string.

Public Function ConnectionString()

Dim qt As QueryTable

For Each qt In ActiveSheet.QueryTables
Debug.Print qt.Connection
Next

End Function

Results:
ODBC;DRIVER=SQL
Server;SERVER=DAVE;UID=sa;PWD=********;APP=Microso ft®
Query;WSID=DAVE;DATABASE=Agr

First off, I notice this is using ODBC. I thought that was
old stuff. I'm using Office 2000 - which is old as it is.
I'm use to using OLE DB: PROVIDER=SQLOLEDB.1;DATA
SOURCE=DAVE;INITIAL CATALOG=Agr;USER ID=sa;PASSWORD=*****

Secondly, in the code I notice you are using ActiveSheet
and then looping through each QueryTables. I have multiple
sheets. How can I loop through each of my sheets. I need
something like Excel.Sheet1.SetFocus, but nothing like
that exists. Again sorry, I am new to Excel programming.
I'm use to programming in Access and VB and haven't yet
figured out the whole heirarchy to the Excel object model.

-----Original Message-----

in the querytable's output range
rightclick,

select
Edit Query

via VBA here's a rough beginning..

Sub tst()

Dim qt As QueryTable
Dim sNew As String


For Each qt In ActiveSheet.QueryTables
sNew = InputBox("Edit the string:", , qt.Connection)
If sNew < "" And sNew < qt.Connection Then
qt.Connection = sNew
End If

Next
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

I'm new to using excel at a presentation tool for data
stored in a SQL Server database. I set up a data source
locally on my development machine between my
development
SQL Server and Excel. Everything works fine and I am
able
to make my graphs, etc. Now I'd like to distribute the
Excel workbook to management and change the data source
to
look at the production SQL Server. I can edit the
query,
but I can't get to anything that allows me to point to
a
different data source, change users for security
purposes,
passwords or databases. There has to be something there
I'm just not seeing. I can't imaging having to recreate
every Excel file just because the server gets renamed,
add
or change users, etc. Thanks for taking your time to
answering my question.


.

the basic hierarchy is in vba help
excel relies heavily on simple collections
what you're looking for is
workbooks
worksheets
cells (or range)


else just use help to highlight querytables..
follow help hyperlinks for applies to
or review the locals window on a breakpoint


dim wb as workbook
dim ws as worksheet
dim qt as querytable

For each wb in workbooks
for each ws in wb.worksheets
for each qt in ws.querytables
stop 'open local windows and review the variables
next
next
next

'---
re queries:
connections can be saved as as udl. (uniform data locator)
these can be easily edited as they are simple xml's


queries can be saves as odc files
(xml containing all query properties)

while you're queries show ODBC, Excel can just as easily handle ADO,
that just depends at what datasource (and DRIVER!!!) you select when you
create it.

hmm... you've got a bit of reading to do :)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

Thanks for the reply. I've been though every part of the
Edit Query wizard and options. There is nothing there to
edit my connection string. But that is OK because VBA will
be faster than a GUI anyway.

Now I've got some questions about the VBA code. I modified
your code to simply view the existing connection string.

Public Function ConnectionString()

Dim qt As QueryTable

For Each qt In ActiveSheet.QueryTables
Debug.Print qt.Connection
Next

End Function

Results:
ODBC;DRIVER=SQL
Server;SERVER=DAVE;UID=sa;PWD=********;APP=Microso ft®
Query;WSID=DAVE;DATABASE=Agr

First off, I notice this is using ODBC. I thought that was
old stuff. I'm using Office 2000 - which is old as it is.
I'm use to using OLE DB: PROVIDER=SQLOLEDB.1;DATA
SOURCE=DAVE;INITIAL CATALOG=Agr;USER ID=sa;PASSWORD=*****

Secondly, in the code I notice you are using ActiveSheet
and then looping through each QueryTables. I have multiple
sheets. How can I loop through each of my sheets. I need
something like Excel.Sheet1.SetFocus, but nothing like
that exists. Again sorry, I am new to Excel programming.
I'm use to programming in Access and VB and haven't yet
figured out the whole heirarchy to the Excel object model.

-----Original Message-----

in the querytable's output range rightclick,

select
Edit Query

via VBA here's a rough beginning..

Sub tst()

Dim qt As QueryTable
Dim sNew As String


For Each qt In ActiveSheet.QueryTables
sNew = InputBox("Edit the string:", , qt.Connection)
If sNew < "" And sNew < qt.Connection Then
qt.Connection = sNew
End If

Next
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

I'm new to using excel at a presentation tool for data
stored in a SQL Server database. I set up a data source locally on
my development machine between my development SQL Server and Excel.
Everything works fine and I am able to make my graphs, etc. Now I'd
like to distribute the Excel workbook to management and change the
data source to look at the production SQL Server. I can edit the
query, but I can't get to anything that allows me to point to a
different data source, change users for security purposes,
passwords or databases. There has to be something there I'm just not
seeing. I can't imaging having to recreate every Excel file just
because the server gets renamed, add or change users, etc. Thanks
for taking your time to answering my question.


.

Thanks! That helps me out. I was looking for Sheets not
Worksheets. And yes, I have a bit of reading and exploring
to do in this new model. Thanks again.

-----Original Message-----

the basic hierarchy is in vba help
excel relies heavily on simple collections
what you're looking for is
workbooks
worksheets
cells (or range)


else just use help to highlight querytables..
follow help hyperlinks for applies to
or review the locals window on a breakpoint


dim wb as workbook
dim ws as worksheet
dim qt as querytable

For each wb in workbooks
for each ws in wb.worksheets
for each qt in ws.querytables
stop 'open local windows and review the variables
next
next
next

'---
re queries:
connections can be saved as as udl. (uniform data locator)
these can be easily edited as they are simple xml's


queries can be saves as odc files
(xml containing all query properties)

while you're queries show ODBC, Excel can just as easily
handle ADO,
that just depends at what datasource (and DRIVER!!!) you
select when you
create it.

hmm... you've got a bit of reading to do :)





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

Thanks for the reply. I've been though every part of
the
Edit Query wizard and options. There is nothing there
to
edit my connection string. But that is OK because VBA
will
be faster than a GUI anyway.

Now I've got some questions about the VBA code. I
modified
your code to simply view the existing connection string.

Public Function ConnectionString()

Dim qt As QueryTable

For Each qt In ActiveSheet.QueryTables
Debug.Print qt.Connection
Next

End Function

Results:
ODBC;DRIVER=SQL
Server;SERVER=DAVE;UID=sa;PWD=********;APP=Microso ft®
Query;WSID=DAVE;DATABASE=Agr

First off, I notice this is using ODBC. I thought that
was
old stuff. I'm using Office 2000 - which is old as it
is.
I'm use to using OLE DB: PROVIDER=SQLOLEDB.1;DATA
SOURCE=DAVE;INITIAL CATALOG=Agr;USER
ID=sa;PASSWORD=*****

Secondly, in the code I notice you are using
ActiveSheet
and then looping through each QueryTables. I have
multiple
sheets. How can I loop through each of my sheets. I
need
something like Excel.Sheet1.SetFocus, but nothing like
that exists. Again sorry, I am new to Excel
programming.
I'm use to programming in Access and VB and haven't yet
figured out the whole heirarchy to the Excel object
model.

-----Original Message-----

in the querytable's output range rightclick,

select
Edit Query

via VBA here's a rough beginning..

Sub tst()

Dim qt As QueryTable
Dim sNew As String


For Each qt In ActiveSheet.QueryTables
sNew = InputBox("Edit the string:", , qt.Connection)
If sNew < "" And sNew < qt.Connection Then
qt.Connection = sNew
End If

Next
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Dave" wrote:

I'm new to using excel at a presentation tool for
data
stored in a SQL Server database. I set up a data
source locally on
my development machine between my development SQL
Server and Excel.
Everything works fine and I am able to make my
graphs, etc. Now I'd
like to distribute the Excel workbook to management
and change the
data source to look at the production SQL Server. I
can edit the
query, but I can't get to anything that allows me to
point to a
different data source, change users for security
purposes,
passwords or databases. There has to be something
there I'm just not
seeing. I can't imaging having to recreate every
Excel file just
because the server gets renamed, add or change
users, etc. Thanks
for taking your time to answering my question.


.



.