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???
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???
Code:
[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]