使用VBA将Excel中的连接刷新到SQL Server(使用密码) [英] Refresh a connection in Excel to SQL Server (with password) using VBA

问题描述

我有10个通过OLEDB连接到SQL Server的Excel文件，我需要每天手动一次刷新它们.刷新它们时，我需要输入密码.我试图创建一个宏来自动执行此过程，但是没有成功.我执行了此功能以使我的工作簿之一自动化:

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.

这篇关于使用VBA将Excel中的连接刷新到SQL Server(使用密码)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！