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