保存数据库连接登录和密码

Question

我有一个excel文件连接到sql数据库，每次我打开文件时，询问我登录ID和密码，有什么方法可以存储登录信息。

谢谢

Answer 1

如果您在工作簿中使用OLEDB连接，则可以将用户名和密码存储在宏中：

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=SQLOLEDB.1;Persist Security Info=True;User ID='Id Here'; Password=''Password Here; Data Source= 'DB Name here' ;Use Procedure for Prepare=1" _
     , _
    ";Auto Translate=True;Packet Size=4096;Workstation ID="your workstationid";Use Encryption for Data=False;Tag with column collation when p" _
    , "ossible=False;Initial Catalog=your catalog"), Destination:=Range("where you want data")). _
    QueryTable
    .CommandType = xlCmdSql
    .CommandText = 'your string or query'
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=True
End With