Help to build Union SQL statement Dynamically based on table values

[Luthius]

Guys
I would like assistance from you to develop a function that will create a dinamically SQL Union Statement

I have an excel table containing on column 'C' the Server Name and on Column 'D' the data base name.
The Column 'E' contains the current status of the Servers (0-Offline, 1-Online)

I would like that the code can read the values from the Excel table and put the Server Name and Data Base name so I can built Dynamically the UNION SQL Statement.

How can it be done???

[COLOR=#0000ff]Public Function [/COLOR]BuildUnionStatement() [COLOR=#0000ff]As String[/COLOR]

[COLOR=#0000ff]Dim[/COLOR] strSql [COLOR=#0000ff]As String[/COLOR]
[COLOR=#0000ff]Dim [/COLOR]strPattern [COLOR=#0000ff]As String[/COLOR]


[COLOR=#0000ff]Dim[/COLOR] tbl [COLOR=#0000ff]As[/COLOR] Range
[COLOR=#0000ff]Dim[/COLOR] aCell [COLOR=#0000ff]As[/COLOR] Range
[COLOR=#0000ff]Dim [/COLOR]intCounter [COLOR=#0000ff]As[/COLOR] Integer




strPattern = vbCrLf & "UNION ALL" & vbCrLf & _
            "SELECT Department.Comment1 AS [Vessel], " & _
            "COUNT_BIG(*) AS [Total], " & vbCrLf & _
            "FROM 'FLDNAME'" & vbCrLf & _
            "INNER JOIN 'FLDNAME'.Department ON compjob.DeptID=Department.DeptID" & vbCrLf & _
            "WHERE (compjob.Active = 1) AND compjob.NextDueDate < getdate()" & vbCrLf & _
            "GROUP BY Department.Comment1"


        
[COLOR=#0000ff]Set[/COLOR] tbl = ActiveSheet.ListObjects("Table1").DataBodyRange


intCounter = 1
[COLOR=#0000ff]For Each[/COLOR] aCell [COLOR=#0000ff]In[/COLOR] tbl.Cells


        [COLOR=#0000ff]If[/COLOR] aCell(intCounter, 4) = 1 [COLOR=#0000ff]Then[/COLOR]
            strSql = strSql & Replace(strPattern, _
                "FLDNAME", aCell(intCounter, 3).Value)
        [COLOR=#0000ff]End If[/COLOR]
        intCounter = intCounter + 1
    [COLOR=#0000ff]Next[/COLOR] aCell
    
    BuildUnionStatement = Mid(strSql, 14)


[COLOR=#0000ff]End Function[/COLOR]

 

[Luthius]

The problem is to add in the string the database name. Anyone please?

[COLOR=#333333]        [/COLOR][COLOR=#0000ff]If[/COLOR][COLOR=#333333] aCell(intCounter, 4) = 1 [/COLOR][COLOR=#0000ff]Then[/COLOR]            strSql = strSql & Replace(strPattern, _
                "FLDNAME", aCell(intCounter, 3).Value)
        [COLOR=#0000ff]End If[/COLOR]
        intCounter = intCounter + 1 [COLOR=#333333]    [/COLOR][COLOR=#0000ff]Next[/COLOR][COLOR=#333333] aCell[/COLOR]

 

[Luthius]

Any idea guys how to solve this?

 

[Luthius]

Anyone?