修改microsoft excel宏中的嵌入式连接字符串 [英] Modify an embedded Connection String in microsoft excel macro

问题描述

我有一个Excel文档，它有一个宏，运行时会修改该连接的 CommandText ，以传递Excel电子表格中的参数，如下所示：

  Sub RefreshData（）
 ActiveWorkbook.Connections（Job_Cost_Code_Transaction_Summary）
 .OLEDBConnection.CommandText =Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate ='&工作表（成本完成）。范围（MonthEndDate）。 '，@job ='&工作表（成本完成）范围（工作）。 '
 ActiveWorkbook.Connections（Job_Cost_Code_Transaction_Summary）。刷新
 End Sub 
  

我希望刷新不仅修改连接命令，还可以修改连接，因为我也想使用不同的数据库：

就像宏替换命令参数一样使用电子表格中的值，我希望它也可以从电子表格中的值替换数据库服务器名称和数据库名称。

不需要完整的实现，只需要代码修改与表中的值的连接就足够了，我应该可以从那里得到它的工作。

我试图这样做：

  ActiveWorkbook 
 .Connections（Job_Cost_Code_Transaction_Summary）
 .OLEDBConnection.Connection =new connection str 
  

但这不行。谢谢。

解决方案

我的问题的答案如下。

所有其他答案大多是正确的，并且专注于修改当前的连接，但是我想只想知道如何在连接上设置连接字符串。

错误来了这个如果你看我的屏幕截图，你会看到连接字符串是：

  Provider = SQLOLEDB.1; Integrated Security = SSPI; Persist Security Info = True;初始目录= ADCData_Doric;数据源= doric-server5;使用过程Prepare = 1;自动翻译= True;数据包大小= 4096;工作站ID = LHOLDER-VM;使用加密数据=使用列排序可能= False 
  

我试图用 ActiveWorkbook.Connections（Job_Cost_Code_Transaction_Summary）。OLEDBConnection.Connection =连接字符串

当我只是试图将完整的字符串分配给连接。我能够将MsgBox的当前连接字符串与该属性相关联，但不会将连接字符串设置回来而不会收到错误。

我发现连接字符串需要有

所以现在这个工作!!!

  ActiveWorkbook.Connections（Job_Cost_Code_Transaction_Summary）。OLEDBConnection.Connection =OLEDB; Provider = SQLOLEDB.1;集成安全= SSPI; Persist Security Info = True;初始目录= ADCData_Doric;数据源= doric-server5;使用过程Prepare = 1;自动翻译= True;数据包大小= 4096;工作站ID = LHOLDER-VM;对Data = False使用加密;可能时使用列排序的标签= False 
  

非常微妙但那是错误！

I have an Excel document that has a macro which when run will modify a CommandText of that connection to pass in parameters from the Excel spreadsheet, like so:

Sub RefreshData()
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary")
  .OLEDBConnection.CommandText = "Job_Cost_Code_Transaction_Summary_Percentage_Pending @monthEndDate='" & Worksheets("Cost to Complete").Range("MonthEndDate").Value & "', @job ='" & Worksheets("Cost to Complete").Range("Job").Value & "'"
ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").Refresh
End Sub

I would like the refresh to not only modify the connection command but also modify the connection as I would like to use it with a different database also:

Just like the macro replaces the command parameters with values from the spreadsheet I would like it to also replace the database server name and database name from values from the spreadsheet.

A complete implementation is not required, just the code to modify the connection with values from the sheet will be sufficient, I should be able to get it working from there.

I tried to do something like this:

 ActiveWorkbook
 .Connections("Job_Cost_Code_Transaction_Summary")
 .OLEDBConnection.Connection = "new connection string"

but that does not work. Thanks.

解决方案

The answer to my question is below.

All of the other answers are mostly correct and focus on modifying the current connection, but I want just wanting to know how to set the connection string on the connection.

The bug came down to this. If you look at my screenshot you will see that the connection string was:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False

I was trying to set that string with ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "connection string"

I was getting an error when i was simply trying to assign the full string to the Connection. I was able to MsgBox the current connection string with that property but not set the connection string back without getting the error.

I have since found that the connection string needs to have OLEDB; prepended to the string.

so this now works!!!

ActiveWorkbook.Connections("Job_Cost_Code_Transaction_Summary").OLEDBConnection.Connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=ADCData_Doric;Data Source=doric-server5;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LHOLDER-VM;Use Encryption for Data=False;Tag with column collation when possible=False"

very subtle but that was the bug!

这篇关于修改microsoft excel宏中的嵌入式连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！