execl用宏查询mysql,Excel宏来更改外部数据查询连接 - 例如从一个数据库到另一个数据库...

I'm looking for a macro/vbs to update all the external data query connections to point at a different server or database. This is a pain to do manually and in versions of Excel before 2007 it sometimes seems impossible to do manually.

Anyone have a sample? I see there are different types of connections 'OLEDB' and 'ODBC', so I guess I need to deal with different formats of connection strings?

解决方案

I ended up writing the following, which prompts for the connection details, creates a connection string, then updates all external data queries to use that connection string.

'''' Prompts for connection details and updates all the external data connections in the workbook accordingly.

'''' Changes all connections to use ODBC connections instead of OLEDB connections.

'''' Could be modified to use OLEDB if there's a need for that.

Sub PromptAndUpdateAllConnections()

Dim Server As String, Database As String, IntegratedSecurity As Boolean, UserId As String, Password As String, ApplicationName As String

Dim ConnectionString As String

Dim MsgTitle As String

MsgTitle = "Connection Update"

If vbOK = MsgBox("You will be asked for information to connect to the database, and this spreadsheet will be updated to connect using those details.", vbOKCancel, MsgTitle) Then

Server = InputBox("Database server or alias and instance name, e.g. 'LONDB01' or 'LONDB01\INST2'", MsgTitle)

If Server = "" Then GoTo Cancelled

Database = InputBox("Database name", MsgTitle, "a default value")

If Database = "" Then GoTo Cancelled

IntegratedSecurity = (vbYes = MsgBox("Integrated Security? (i.e. has your windows account been given access to connect to the database)", vbYesNo, MsgTitle))

If Not IntegratedSecurity Then

UserId = InputBox("User Id", MsgTitle)

If UserId = "" Then GoTo Cancelled

Password = InputBox("Password", MsgTitle)

If Password = "" Then GoTo Cancelled

End If

ApplicationName = "Excel Reporting"

ConnectionString = GetConnectionString(Server, Database, IntegratedSecurity, UserId, Password, ApplicationName)

UpdateAllQueryTableConnections ConnectionString

MsgBox "Spreadsheet Updated", vbOKOnly, MsgTitle

End If

Exit Sub

Cancelled:

MsgBox "Spreadsheet not updated", vbOKOnly, MsgTitle

End Sub

'''' Generates an ODBC connection string from the given details.

Function GetConnectionString(Server As String, Database As String, IntegratedSecurity As Boolean, _

UserId As String, Password As String, ApplicationName As String)

Dim result As String

If IntegratedSecurity Then

result = "ODBC;DRIVER=SQL Server;SERVER=" & Server & ";DATABASE=" & Database _

& ";Trusted_Connection=Yes;APP=" & ApplicationName & ";"

Else

result = "ODBC;DRIVER=SQL Server;SERVER=" & Server & ";DATABASE=" & Database _

& ";UID=" & UserId & ";PWD=" & Password & ";APP=" & ApplicationName & ";"

End If

RM_GetConnectionString = result

End Function

'''' Sets all external data connection strings to the given value (regardless of whether they're

'''' currently ODBC or OLEDB connections. Appears to change type successfully.

Sub UpdateAllQueryTableConnections(ConnectionString As String)

Dim w As Worksheet, qt As QueryTable

Dim cn As WorkbookConnection

Dim odbcCn As ODBCConnection, oledbCn As OLEDBConnection

For Each cn In ThisWorkbook.Connections

If cn.Type = xlConnectionTypeODBC Then

Set odbcCn = cn.ODBCConnection

odbcCn.SavePassword = True

odbcCn.Connection = ConnectionString

ElseIf cn.Type = xlConnectionTypeOLEDB Then

Set oledbCn = cn.OLEDBConnection

oledbCn.SavePassword = True

oledbCn.Connection = ConnectionString

End If

Next

End Sub