ActiveSheet.QueryTables.Add using a File DSN

[Brian Howenstein]

I have the following subroutine that runs on the activation of several
different worksheets in different workbooks. I would like to be able
to just reference the DSN file for login information rather than
explicitly set the server, username, and password in every single
workbook and worksheet. It would make it much easier to distribute
the workbook to several different users if all they had to do is edit
one file with a text editor.

Private Sub Worksheet_Activate()

Sheet3.Activate
Columns("A").Select
Selection.ClearContents

useDate = Range("H35").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=SQL_SERVER_1;UID=username;PWD=password;APP=Microsoft
Office XP" _
, Destination:=Range("A1"))
.CommandText = Array("exec stored_proc")
.Name = "TESTQUERY"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

How would I set it up so that it would connect to the database just by
referencing the file at "C:\Program Files\Common Files\ODBC\Data
Sources\testquery.dsn"?

Thank you for any help.

Brian

 

[onedaywhen]

Untested, but you could try:

ODBC;FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\testquery.dsn