On Jan 13, 6:32*pm, Jac Tremblay wrote:
Hi,
I want to know if it is possible to configure an ODBC connection with all
the required parameters including user name and password

yes, absolutely. I use a lot of pivot tables that are based on ODBC
connections. When you create the PT you can tell Excel to save the
password - then when you refresh, it just goes out and does it.

I also have a lot of PTs that I inherited from a predessor - I have to
form with a few buttons and a text box. One button reads the
connection string

Private Sub cmdGetString_Click()
' Show source Query
Dim pt As PivotTable, sMsg As String
On Error GoTo err_Handler

Set pt = ActiveCell.PivotTable
sMsg = pt.PivotCache.Connection

txtCmdTxt.Value = sMsg
Exit Sub

Another lets you reset it:

Private Sub cmdSetString_Click()
'Set the connection string to the value of the text box
Dim pt As PivotTable
On Error Resume Next

If msgbox("Update connection string to list", vbOKCancel, "Confirm
update") = vbOK Then
Set pt = ActiveCell.PivotTable

pt.PivotCache.CommandText = Me.txtCmdTxt.Value
pt.RefreshTable
End If
End Sub

It's pretty crude code, but it works. Hope that gives you an idea

and use it from
where it is stored on the drive.


I don't quite understand that question, sorry.

hth,

Tim