sorry everyone for the late reply! i am not getting my notifications! i was starting to think nobody replied to my post...
the PC's are not currently with me but the setup is Server/Client.
Operators use the client station and run FTView SE client. My approach to this was code the VB in FTView where the Date picker is in one display and then a button to print the file...this file is an excel template where the values get updated everytime the print button is clicked.
i am close to zero in VB and Sql
I am testing a code to display the value of tag a_sql with a specific query (see attached pic)
So far this is my working code (I got this off some of the forums and really just trying to make it work).. its working alright but i am quite certain there is a much better way!
Private Sub Button1_Released()
On Error GoTo errHandler
'Declare variables
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Value1 As Double
Dim command As String
Dim dateandtime
Dim ObjExcelApp As Object
Dim FName As String
If MyTagGroup Is Nothing Then 'Checks for existence of tag group
Set MyTagGroup = Application.CreateTagGroup(Me.AreaName)
End If
'Open Connection
Const sConnString As String = "Provider=SQLOLEDB;Data Source=PLANTPAX-PASS\SQLACM;" & "Initial Catalog=agus5;" & "Integrated Security=SSPI;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
' Open the connection and execute.
conn.Open sConnString
command = "select * from agus5.dbo.FloatTable where TagIndex=0 and DateAndTime between '12/15/2020 00:00:00' and '12/15/2020 00:00:59'"
Set rs = conn.Execute(command)
'Copy Data to Tags
Value1 = rs("Val")
FName = "C:\Reports\Template.xlsx"
Set ObjExcelApp = CreateObject("Excel.Application")
ObjExcelApp.Visible = True
ObjExcelApp.Workbooks.Open (FName)
ObjExcelApp.Worksheets("SWITCHBOARD").Cells(10, "B").Value = Value1
ObjExcelApp.ActiveWorkbook.Save
ObjExcelApp.Workbooks.Close
ObjExcelApp.Quit
Set ObjExcelApp = Nothing
'Close Connection
rs.Close
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
errHandler:
LogDiagnosticsMessage ("ReadFromSqlServer error " & Conversion.Hex(Err.Number) & " " & Err.Description)
End Sub
my next step is to figure out how to arrange the data in the cells by row and columns. sorry if my reply is too long and messy, i am testing my code as i type.
Appreciate everyone's input and am studying how to apply them to my code now.
PS. your inputs are highly valued and appreciated damica1! hope to keep reading your inputs on this forum