SQL to Excel help

[Toine]

I am under the impression that David and Phrog are actually saying the same thing. It might be just a choice of words that stirs some feathers both ways. The way I read it is that the data is already in a database and needs to go from there to Excel. No PLC involved for that step, which as far as I can tell you guys all agree on.



It is very well possible that the client is perfectly happy with that single Excel sheet for years to come. Or it could be the starting point for new requirements that keep adding to the complexity of reporting. We need the as of yet unexistent crystal ball ladder instruction to fill that one in with the limited information that we have. So let us not assume and remain friendly, as is the way of this place.

 

[drbitboy]

From my googling, it appears there is a way to load a database into a spreadsheet as a table using ODBC, without using VB or VBA. Some of the examples seem to have a few key components in an eXcel dialog menu and/or setup wizard:

• A radio button that says, more or less, "Retrieve the data as a table"
• A SQL statuement like "SELECT * FROM ..." that retrieves the data as a table
• The location of a cell in the sheet of the top-left element of the table
• Several columns and several rows of data, filling a rectangle of cells (i.e. table) in the sheet.

Give that, shouldn't it be possible to do the same thing, but return only one column (SELECT single_fieldname FROM ...) and one row (e.g. SELECT ... LIMIT 1), i.e. one value, and put that 1-by-1 "table" into that one cell?

 

[parky]

Yes you can extract data without VB, it gives you choices of the tables you wish to return & date/time so you can limit the amount of data returned, but this is not what the OP requires, he wants to automatically open a sheet get the required data, format it into a report without having to do it manually.

 

[drbitboy]

Yes you can extract data without VB, it gives you choices of the tables you wish to return & date/time so you can limit the amount of data returned, but this is not what the OP requires, he wants to automatically open a sheet get the required data, format it into a report without having to do it manually.

Wouldn't he only have to do it once i.e. associate the query with the cell, and then after that the SQL query would be run any time that cell is refreshed?


Or does that "wizard" not work that way, and is only a way to place values in cells, which values are static after running it?

 

[parky]

Actually, I'm getting confused, I received a pm from another person after he read this post about doing something else in excel so getting a bit mixed up here, I believe you can update from a DB I think the problem is formatting the report as for getting the correct fields in the tables without all of them is not something that would be easy without using VBA, Yes you can refresh connections to a table but formatting will still involve a lot of work and I have assumed he would like to save it as a report but without the active update link. But this is an assumption as the other one I'm currently looking at requires just that. perhaps if the OP would post the report layout DB table(s) format and what he wants to happen and if he wants this to be distributed to other users etc.
An example is one I did some years ago:
I created a excel template that could be used by anyone and protected it in various ways i.e. password for VBA code make it read only and sat on a server, the employees who had access to it was controlled by IT, and they had a link on the intranet, this meant that they could open the template, get the date range for a report and then save it as an xls spreadsheet without the code attached. That way they could not modify the original.
I replaced this some years later with a Dot Net web page on the intranet to appease the IT dept.

 

[bopol]

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

 

[parky]

As there will be more than one record and possibly a number of fields you will have to iterate through the record set
x = 0 'Pointer to rows in excel
Rs.MoveFirst 'Move to the start of the record set
While Not Rs.EOF
Cells(x,1).Value = Rs.Fields(0).Value 'Col 1
Cells(x,2).Value = Rs.Fields(1).Value ' Col 2
Cells(x,3).Value = Rs.Fields(2).Value ' Col 3
'Add more colums if required
x = x +1 'increment the pointer
Rs.MoveNext
WEND

This copies the record set into the cells, note as there might be more than one column and more than one record .

Example:
Date/Time My_Value 1 My_Value2 MyValue3
20/12/20 13:33:00 23 142 65
20/12/20 13:34:00 45 124 68
20/12/20 13:36:00 34 142 69

So depending how many columns you need to log each col is Rs(x) 0-how many columns
.MoveNext moves to the next record until the end of file is reached.

 

[bopol]

As there will be more than one record and possibly a number of fields you will have to iterate through the record set
x = 0 'Pointer to rows in excel
Rs.MoveFirst 'Move to the start of the record set
While Not Rs.EOF
Cells(x,1).Value = Rs.Fields(0).Value 'Col 1
Cells(x,2).Value = Rs.Fields(1).Value ' Col 2
Cells(x,3).Value = Rs.Fields(2).Value ' Col 3
'Add more colums if required
x = x +1 'increment the pointer
Rs.MoveNext
WEND

This copies the record set into the cells, note as there might be more than one column and more than one record .

Example:
Date/Time My_Value 1 My_Value2 MyValue3
20/12/20 13:33:00 23 142 65
20/12/20 13:34:00 45 124 68
20/12/20 13:36:00 34 142 69

So depending how many columns you need to log each col is Rs(x) 0-how many columns
.MoveNext moves to the next record until the end of file is reached.

this is exactly what I was looking for! if i wanted to write the next field value in a row instead of column, would this code be correct?


"x = 0 'Pointer to COLUMNS in excel
Rs.MoveFirst 'Move to the start of the record set
While Not Rs.EOF
Cells(B,x).Value = Rs.Fields(0).Value 'Col 1
Cells(C,x).Value = Rs.Fields(1).Value ' Col 2
Cells(D,x).Value = Rs.Fields(2).Value ' Col 3
'Add more colums if required
x = x +1 'increment the pointer
Rs.MoveNext
WEND"

 

[bopol]

it's ugly but it's working as required.. will probably clean up later but for now this should do:


'Copy Data to Tags
rs.MoveFirst
Value1 = rs.Fields(3).Value 'i only need the 4th column value in sql field
rs.MoveNext
Value2 = rs.Fields(3).Value
rs.MoveNext
Value3 = rs.Fields(3).Value
rs.MoveNext
Value4 = rs.Fields(3).Value
rs.MoveNext
Value5 = rs.Fields(3).Value
rs.MoveNext
Value6 = rs.Fields(3).Value

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.Worksheets("SWITCHBOARD").Cells(10, "C").Value = Value2
ObjExcelApp.Worksheets("SWITCHBOARD").Cells(10, "D").Value = Value3
ObjExcelApp.Worksheets("SWITCHBOARD").Cells(10, "E").Value = Value4
ObjExcelApp.Worksheets("SWITCHBOARD").Cells(10, "F").Value = Value5
ObjExcelApp.Worksheets("SWITCHBOARD").Cells(10, "H").Value = Value6

ObjExcelApp.ActiveWorkbook.Save
ObjExcelApp.Workbooks.Close
ObjExcelApp.Quit
Set ObjExcelApp = Nothing

'Close Connection
rs.Close


hope this also helps those with similar requirements. Thank you for all the help everyone!

 

[Holmux]

It has been my experience of 45 years, that when the company starts wanting data to be stored in a SQL Database, they are out growing the "intention of PLC design", not that it can't be done.

Parky's example is proof that it can be done!

When companies start needing/wanting this type of data collection it is usually a good sign they need to looking at SCADA software.

Ignition could provide all of this type of data collection and reporting with just a few mouse clicks and some simple Sql Query building.

Just my thought about the question.

If the PLC needs to communicate with the DB to be able to operate and for fill it's task, it is my book a poor design.

I have seen many of these solutions where everything in the hole building needs to be up and running before production is able to start

I worked many years as an Oracle DBA, so i know both sides of the setup, and I will always prefer to have the data that I need to run production stored in the PLC.

Sorry, this was a little

 

[Toine]

If I am not mistaking then I think you are misinterpreting what David writes and the two of you actually agree. As far as I can tell, the OP wants some process data to go from the PLC into the DB (and already has that working), then wants some of that data in an Excel sheet for reporting. The PLC part is already done for. The reporting seems to be coming along quite well, too. I don't see mention of data from the database being fed back into the PLC program.

 

[parky]

You could still do it in loops
Have an array of Value i.e. Value ()
Then in the loop
X = 0
While NOT rs.EOF ' note you could also include a max limit
Value(x) = Rs.Fields(3)
X = X +1
Rs.MoveNext
WEND
Do the same for copying the data to Excel
So
For Y = 1 to x ''offset for "B" column as you have the length in x
Cells(10,Y).Value = Value(y-1) ' Copy to excel the Value
Next y

 

[bopol]

You could still do it in loops
Have an array of Value i.e. Value ()
Then in the loop
X = 0
While NOT rs.EOF ' note you could also include a max limit
Value(x) = Rs.Fields(3)
X = X +1
Rs.MoveNext
WEND
Do the same for copying the data to Excel
So
For Y = 1 to x ''offset for "B" column as you have the length in x
Cells(10,Y).Value = Value(y-1) ' Copy to excel the Value
Next y

Thank you! that's a whole lot easier!