Changing Data Source

D

Dave

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.
 
K

keepITcool

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 >
 
D

Dave

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=Microsoft®
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.
 
K

keepITcool

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 >
 
D

Dave

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.
 
Top