Changing the connection string of QueryTable objects in excel

[theday99]

Hello All,

I have a workbook with several spreadsheets that have reports on them. Im attempting to iterate through each worksheet, each querytable on the worksheet, and replace the database connection info with the info that i get from the user by prompting the user for the information on startup. I havent written vb in forever, so i apoligize in advance. Heres my basic code:

oConnection.ConnectionString = "Provider=sqloledb;" & _
"Data Source=" & txtServer.Value & ";" & _
"Initial Catalog=" & txtDataBase.Value & ";" & _
"User Id=" & txtUserName.Value & ";" & _
"Password=" & txtPassword.Value


Do While x < ThisWorkbook.Worksheets.Count
Set oWorkSheet = ThisWorkbook.Worksheets(x)
Do While y < oWorkSheet.QueryTables.Count
Set oQueryTable = oWorkSheet.QueryTables
oQueryTable.Connection = oConnection
y = y + 1
Loop
x = x + 1
Loop

It consistently bombs out on this line:
oQueryTable.Connection = oConnection
I did try using the set keyword. Any help would be greatly appreciated.

Thanks,
Ryan

 

[PHV]

Perhaps something like this ?
For Each oWorkSheet In ThisWorkbook.Worksheets
For Each oQueryTable In oWorkSheet.QueryTables
oQueryTable.Connection = oConnection.ConnectionString
Next
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

how about...

sConnection = "Provider=sqloledb;" & _
"Data Source=" & txtServer.Value & ";" & _
"Initial Catalog=" & txtDataBase.Value & ";" & _
"User Id=" & txtUserName.Value & ";" & _
"Password=" & txtPassword.Value

For Each oWorksheet In ThisWorkbook.Worksheets
    For Each oQueryTable In oWorksheet
        oQueryTable.Connection = sConnection
    Next
Next

oQueryTable.Connection

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SkipVought]

oops...

sConnection = "Provider=sqloledb;" & _
"Data Source=" & txtServer.Value & ";" & _
"Initial Catalog=" & txtDataBase.Value & ";" & _
"User Id=" & txtUserName.Value & ";" & _
"Password=" & txtPassword.Value

For Each oWorksheet In ThisWorkbook.Worksheets
    For Each oQueryTable In oWorksheet.QueryTables
        oQueryTable.Connection = sConnection
    Next
Next

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[theday99]

I gave that a shot and still no luck. Its odd, using the immediate window I can verify that the querytable connection im trying to change, and the new connection that im trying to change it to both hold connection values, but it wont let me set the querytable.connection object to anything or allow me to set a empty connection object equal to the querytable.connection object. Any other suggestions?

Ryan

 

[theday99]

Hey Skip,

I gave you're idea a shot and ended up with a VBAProject: 1004 Application-defined or object defined error. Thanks for the suggestion, any others?

Ryan

 

[SkipVought]

Then your connection string is formatted improperly, cuz, I do something similar to this regularly.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[theday99]

Hey Skip,

I thought the connection string may have been formatted inproperly so I copied your connection string from your post, verified that the parameters were be loading properly, here is the value of the connection string:
Provider=sqloledb;Data Source=Igloo;Initial Catalog=DataHub;User Id=sa;Password=

I still get that 1004 error whenever I attempt to assign the new string to the connection object, should I actually be assigning it to the connection.connectionstring object?

I reallly appreciate the help,
Ryan

 

[SkipVought]

Right out of HELP
[tt]
This example supplies new ODBC connection information for the first query table on the first worksheet.

Worksheets(1).QueryTables(1) _
.Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"
[/tt]
Maybe you need a terminator...

sConnection = "Provider=sqloledb;" & _
"Data Source=" & txtServer.Value & ";" & _
"Initial Catalog=" & txtDataBase.Value & ";" & _
"User Id=" & txtUserName.Value & ";" & _
"Password=" & txtPassword.Value [b][red]& ";"[/red][/b]

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}