使用 VBA 在 Excel 中刷新与 SQL Server 的连接(使用密码)

2022-01-22 00:00:00 vba connection refresh passwords sql-server

我有 10 个 Excel 文件通过 OLEDB 连接到 SQL Server，我需要每天手动刷新它们.当我刷新它们时，我需要插入密码.我试图制作一个宏来自动化这个过程，但我没有成功.我做了这个功能来自动化我的一个工作簿:

I have 10 Excel files that are connected to a SQL Server by OLEDB and I need to refresh them everyday one by one manually. When I refresh them I need to insert a password. I tried to make a macro to automate this process, but I didn't succeed. I did this function to automate one of my workbooks:

Sub updateABC()
  Workbooks("Teste").Connections("SQL Server_Azure1").OLEDBConnection.Refresh
End Sub

Excel 要求输入 SQL Server 登录密码.有没有办法使用 vba 自动输入这个密码?

And the Excel ask for the password to SQL Server logon. Is there any way to put this password automatically using vba?

谢谢.

推荐答案

Excel中有一种方法不用VBA:

There is a way in Excel without VBA:

  1. 您可以转到数据然后连接
  2. 单击顶部连接上的属性
  3. 点击定义选项卡
  4. 大约一半的地方有一个保存密码"复选框，您将收到有关密码存储未加密的提示.
  5. 对此表示同意
  6. 点击确定

对所有连接执行相同操作

Do the same for all of your connections

然后您可以刷新所有连接，系统将提示您输入每个连接的密码.完成此操作并保存文档后，它不会再次要求您输入密码.

You can then refresh all connections and you will be prompted to enter the password for each connection. Once you do this and save the document it will not ask you for your passwords again.

您还可以在使用"选项卡上勾选打开文件时刷新数据"，以在打开工作簿时刷新连接.

You can also tick "Refresh data when opening the file" on the Usage tab to have the connections refreshed when the workbook is opened.

相关文章