How do I dynamically set a SQL Server connectionstring in excel?

See more:

VBScript

SQL-Server

MS-Excel

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!