如何在excel中动态设置SQL Server连接字符串？ [英] How do I dynamically set a SQL Server connectionstring in excel?
问题描述
我需要运行一个可以动态设置SQL Server连接字符串的vbscript，从工作表的excel单元格中获取服务器名称和数据库名称，并刷新文件的所有工作表中的表格。
我目前在设置表单上的刷新按钮上有这个脚本（从服务器和数据库名称开始）：
Sub Refresh_Click（）
Dim Sh As 工作表
Dim sServer As 字符串
Dim sDatabase As 字符串
Dim sTableName As 字符串
Dim vDestinationRg 作为 变体
Dim sQuery（ 1 24 ） 作为 字符串
Dim vQueryArray 作为 变体
Dim i 作为 整数
Dim j 作为 整数
Dim isSplit 作为 布尔
Dim sUsername As String
Dim sPassword As String
设置 Sh = ActiveSheet
j = 1
isSplit = True
vQueryArray = Application.Transpose（Sh.Range（ U1：U10））
对于 i = LBound（vQueryArray） UBound（vQueryArray）
如果 vQueryArray（i）<> 然后
isSplit = False
sQuery（j）= sQuery（j）&修剪（vQueryArray（i））& vbCrLf
ElseIf 不 isSplit 然后
isSplit = True
j = j + 1
结束 如果
下一步 i
sServer =表格（ 设置）。范围（< span class =code-string> F5）。值
sDatabase =表格（ 设置）。范围（ F7）。值
vDestinationRg =数组（ $ H $ 12）
sUsername = DSandeep
sPassword = halo @ 983124
For i = LBound（sQuery） To UBound（sQuery）
如果 sQuery（i）= 然后 退出 Sub
sTableName = Result_Table _&替换（替换（Sh.Name， ， ）， - ， ）& _& i
On 错误 简历 下一步
Sh.ListObjects（sTableName）。删除
开启 错误 GoTo 0
使用 Sh.ListObjects.Add（SourceType：= 0，Source：= Array（_
OLEDB; Provider = SQLOLEDB.1; User Id =& sUsername& ;密码=& sPassword& ;数据Source =& sServer& ; Initial Catalog =& sDatabase& ; ），目的地：= Sh.Range（vDestinationRg（i - 1 ）））。QueryTable
.CommandText = sQuery（i）
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = < span class =code-keyword> True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = sTab leName
.Refresh BackgroundQuery：= False
End
下一步
结束 Sub
我在设置表格的单元格U1中写入了一个选择查询，它创建了将表填充到从H12开始的目标范围。
< img alt =IMG示例src =C：\ PRATIP_BKP / lSetup.png/>
但是我不想在'Setup'表单上放置查询，而是想在不同的工作表上编写查询，这些工作表将填充相应工作表中的表，只有这一个刷新按钮单击设置表。
我该怎么办？我被告知可以在不编写Vbscript的情况下实现，但没有运气！我尝试将SQL服务器连接添加到工作簿，但无法从那里使其动态化。
请建议一种方法来执行此操作！在此先感谢！
H
12）
sUsername = DSandeep
sPassword = halo @ 983124
对于 i = LBound（sQuery）到 UBound（sQuery）
如果 sQuery（i）= 然后 退出 Sub
sTableName = Result_Table _&替换（替换（Sh.Name， ，< span class =code-string> ）， - ， ）& _& i
On 错误 简历 下一步
Sh.ListObjects（sTableName）。删除
开启 错误 GoTo 0
使用 Sh.ListObjects.Add（SourceType：= 0，Source：= Array（_
OLEDB; Provider = SQLOLEDB.1; User Id =& sUsername& ;密码=& sPassword& ;数据Source =& sServer& ; Initial Catalog =& sDatabase& ; ），目的地：= Sh.Range（vDestinationRg（i - 1 ）））。QueryTable
.CommandText = sQuery（i）
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = < span class =code-keyword> True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = sTab leName
.Refresh BackgroundQuery：= False
End
下一步
结束 Sub
我在设置表格的单元格U1中写入了一个选择查询，它创建了将表填充到从H12开始的目标范围。
< img alt =IMG示例src =C：\ PRATIP_BKP / lSetup.png/>
但是我不想在'Setup'表单上放置查询，而是想在不同的工作表上编写查询，这些工作表将填充相应工作表中的表，只有这一个刷新按钮单击设置表。
我该怎么办？我被告知可以在不编写Vbscript的情况下实现，但没有运气！我尝试将SQL服务器连接添加到工作簿，但无法从那里使其动态化。
请建议一种方法来执行此操作！提前致谢！
试试这些主题 -
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ab529613 -fc43-4ab1-847f-098c082f3b7e / help-can-use-dynamic-connection-string-with-excel [ ^ ] br />
http://www.sqlservercentral.com/Forums/Topic950440-147-1。 aspx [ ^ ]
I need to run a vbscript that can dynamically set a SQL Server connectionstring, taking server name and database name from excel cells of a sheet and refresh tables in all worksheets of the file.
I currently have this script against a 'Refresh' button on the 'Setup' sheet(from where it takes the server and database names):
Sub Refresh_Click()
Dim Sh As Worksheet
Dim sServer As String
Dim sDatabase As String
Dim sTableName As String
Dim vDestinationRg As Variant
Dim sQuery(1 To 24) As String
Dim vQueryArray As Variant
Dim i As Integer
Dim j As Integer
Dim isSplit As Boolean
Dim sUsername As String
Dim sPassword As String
Set Sh = ActiveSheet
j = 1
isSplit = True
vQueryArray = Application.Transpose(Sh.Range("U1:U10"))
For i = LBound(vQueryArray) To UBound(vQueryArray)
If vQueryArray(i) <> "" Then
isSplit = False
sQuery(j) = sQuery(j) & Trim(vQueryArray(i)) & vbCrLf
ElseIf Not isSplit Then
isSplit = True
j = j + 1
End If
Next i
sServer = Sheets("Setup").Range("F5").Value
sDatabase = Sheets("Setup").Range("F7").Value
vDestinationRg = Array("$H$12")
sUsername = "DSandeep"
sPassword = "halo@983124"
For i = LBound(sQuery) To UBound(sQuery)
If sQuery(i) = "" Then Exit Sub
sTableName = "Result_Table_" & Replace(Replace(Sh.Name, " ", ""), "-", "") & "_" & i
On Error Resume Next
Sh.ListObjects(sTableName).Delete
On Error GoTo 0
With Sh.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;User Id=" & sUsername & "; Password=" & sPassword & ";Data Source=" & sServer & ";Initial Catalog=" & sDatabase & ""), Destination:=Sh.Range(vDestinationRg(i - 1))).QueryTable
.CommandText = sQuery(i)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = sTableName
.Refresh BackgroundQuery:=False
End With
Next
End Sub
I have a select query written in cell "U1" of the 'Setup' sheet and it creates and populates the table into the destination range starting from "H12".
<img alt="IMG example" src="C:\PRATIP_BKP/lSetup.png" />
But instead of placing the query on the 'Setup' sheet I want to write queries on different worksheets which would populate tables in the respective worksheets, with only this one Refresh button click on the Setup sheet.
How can I do this? I have been told it can be achieved without writing Vbscript also, but no luck there! I tried adding SQL server connections to the workbook, but can't make it dynamic from there.
Kindly suggest a way to do this! Thanks in advance!
H
12") sUsername = "DSandeep" sPassword = "halo@983124" For i = LBound(sQuery) To UBound(sQuery) If sQuery(i) = "" Then Exit Sub sTableName = "Result_Table_" & Replace(Replace(Sh.Name, " ", ""), "-", "") & "_" & i On Error Resume Next Sh.ListObjects(sTableName).Delete On Error GoTo 0 With Sh.ListObjects.Add(SourceType:=0, Source:=Array( _ "OLEDB;Provider=SQLOLEDB.1;User Id=" & sUsername & "; Password=" & sPassword & ";Data Source=" & sServer & ";Initial Catalog=" & sDatabase & ""), Destination:=Sh.Range(vDestinationRg(i - 1))).QueryTable .CommandText = sQuery(i) .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = sTableName .Refresh BackgroundQuery:=False End With Next End Sub
I have a select query written in cell "U1" of the 'Setup' sheet and it creates and populates the table into the destination range starting from "H12".
<img alt="IMG example" src="C:\PRATIP_BKP/lSetup.png" />
But instead of placing the query on the 'Setup' sheet I want to write queries on different worksheets which would populate tables in the respective worksheets, with only this one Refresh button click on the Setup sheet.
How can I do this? I have been told it can be achieved without writing Vbscript also, but no luck there! I tried adding SQL server connections to the workbook, but can't make it dynamic from there.
Kindly suggest a way to do this! Thanks in advance!
Try these threads -
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ab529613-fc43-4ab1-847f-098c082f3b7e/help-cannot-use-dynamic-connection-string-with-excel[^]
http://www.sqlservercentral.com/Forums/Topic950440-147-1.aspx[^]
这篇关于如何在excel中动态设置SQL Server连接字符串？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
