Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing ODBC connection to Excel Macro 1

Status
Not open for further replies.
RedMoo

RedMoo

Technical User
Apr 27, 2005
22
IE
Hi,
I have multiple worksheets which each call a separate MSQUERY statement. I recorded a Macro and I have placed a refresh button in the spreadsheet to call this macro to automatically refresh the document but it asks me to enter my username and password to the SQL Server connection for every worksheet that calls a MSQuery.

An example of the calls in the macro would be.

Sheets("Accomodation Items").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Consumables").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Other Services").Select
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("B31").Select


I wish to be able to pass the connection details to the ODBC in here so that I do not have to log into every worksheet to get a result.

Regards,
Redmoo
 
Sort by date Sort by votes
Hi,

First, find the connection strings
Code: 
Sub FindConnection()
    Dim ws As Worksheet, qt As QueryTable
    
    For Each ws In Worksheets
        For Each qt In ws.QueryTables
            Debug.Print _
                qt.Parent.Name & "!" & _
                qt.ResultRange.Address & ": " & _
                qt.Connection
        Next
    Next
'observe the connection string via View > Immediate Window (ctrl+g)
End Sub
Past back as to how to use this information in your code.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Please post your connection strings as well.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Hi Skip,
I tried to run this Code but I got a Run Time Error of 1004.

When I go to debug it highlights the following text:

Debug.Print _
qt.Parent.Name & "!" & _
qt.ResultRange.Address & ": " & _
qt.Connection

If I hover the pointer over qt.ResultRange.Address & ": " & _ it gives me
"qt.ResultRange.Address = Applicatio-defined or object-defined error"

If I hover the pointer over qt.connection it will give me the ODBC seetings that I use to connect to the MSQUERY.
"qt.Connection= ODBC; DSN= reports; Description = reports; UID = User;......
 
Upvote 0 Downvote
Strange, it runs in my workbook.

Try just deleting that line to result in...
Code: 
Debug.Print _
                qt.Parent.Name & "!" & _
                 qt.Connection


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Hi Skip,
That returned the ODBC settings for each of the Worksheets as follows.

Accomodation Items!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Consumables!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Other Services!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Pathology!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Pathology Sendouts!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Pharmacy!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Procedures!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Professional fees!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Radiology!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter
Theatre_Ward Packs!ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;UID=reporter;;APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter

Regards,
Redmoo
 
Upvote 0 Downvote
You have to add the password to the connection strings.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
Code: 
Sub RefreshAllQueryTables()
    Dim ws As Worksheet, qt As QueryTable, ans
    
    ans = InputBox("PW")
    
    For Each ws In Worksheets
        For Each qt In ws.QueryTables
            qt.Connection = "ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;" & _
                "UID=reporter;" & _
                ans & ";" & _
                "APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter"
            qt.Refresh False
        Next
    Next
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote

You might need the PWD parameter name...
Code: 
Sub RefreshAllQueryTables()
    Dim ws As Worksheet, qt As QueryTable, ans
    
    ans = InputBox("PW")
    
    For Each ws In Worksheets
        For Each qt In ws.QueryTables
            qt.Connection = "ODBC;DSN=Cork_Reporter;Description=Cork_Reporter;" & _
                "UID=reporter;" & _[b]
                "PWD=" & [/b]ans & ";" & _
                "APP=Microsoft Office 2003;WSID=CITRIXBLADE31;DATABASE=pims_reporter"
            qt.Refresh False
        Next
    Next
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Upvote 0 Downvote
Hi PHV,
That is my problem I need to embed this connection string into my macro as it was one I recorded.

The VBA behind my Macro is as follows.

Sub Refresh()
'
' Refresh Macro
'

'
Sheets("Accomodation Items").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Consumables").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Other Services").Select
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("B31").Select
Sheets("Pathology").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=-12
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Pathology Sendouts").Select
ActiveWindow.SmallScroll Down:=-15
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Pharmacy").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=-6
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Procedures").Select
ActiveWindow.LargeScroll ToRight:=-1
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.ScrollRow = 1912
ActiveWindow.ScrollRow = 1908
ActiveWindow.ScrollRow = 1904
ActiveWindow.ScrollRow = 1900
ActiveWindow.ScrollRow = 1884
ActiveWindow.ScrollRow = 1864
ActiveWindow.ScrollRow = 1836
ActiveWindow.ScrollRow = 1808
ActiveWindow.ScrollRow = 1792
ActiveWindow.ScrollRow = 1752
ActiveWindow.ScrollRow = 1700
ActiveWindow.ScrollRow = 1672
ActiveWindow.ScrollRow = 1620
ActiveWindow.ScrollRow = 1560
ActiveWindow.ScrollRow = 1532
ActiveWindow.ScrollRow = 1480
ActiveWindow.ScrollRow = 1428
ActiveWindow.ScrollRow = 1400
ActiveWindow.ScrollRow = 1372
ActiveWindow.ScrollRow = 1344
ActiveWindow.ScrollRow = 1328
ActiveWindow.ScrollRow = 1288
ActiveWindow.ScrollRow = 1248
ActiveWindow.ScrollRow = 1236
ActiveWindow.ScrollRow = 1196
ActiveWindow.ScrollRow = 1156
ActiveWindow.ScrollRow = 1140
ActiveWindow.ScrollRow = 1100
ActiveWindow.ScrollRow = 1048
ActiveWindow.ScrollRow = 1020
ActiveWindow.ScrollRow = 957
ActiveWindow.ScrollRow = 941
ActiveWindow.ScrollRow = 889
ActiveWindow.ScrollRow = 861
ActiveWindow.ScrollRow = 809
ActiveWindow.ScrollRow = 769
ActiveWindow.ScrollRow = 753
ActiveWindow.ScrollRow = 713
ActiveWindow.ScrollRow = 673
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 605
ActiveWindow.ScrollRow = 553
ActiveWindow.ScrollRow = 501
ActiveWindow.ScrollRow = 473
ActiveWindow.ScrollRow = 421
ActiveWindow.ScrollRow = 381
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 297
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 241
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 133
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 53
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 1
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Professional fees").Select
Range("A3").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("C31:C32").Select
Sheets("Radiology").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Theatre_Ward Packs").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Contents").Select
End Sub
 
Upvote 0 Downvote
Hi Skip,
Thanks for your post above it has worked perfectly for me.

Thanks Again,
Redmoo
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
358
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
328
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
590
remeng
remeng
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
323
SkipVought
S
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
305
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top