SQL to Excel help

bopol

Member
Join Date
Feb 2008
Location
Singapore
Posts
46
Hi everyone! there's a customer requirement for a report in excel format and they already have a template for the report... I have set up data logging to odbc database (sql) and i can already see the data in sql...


the question is:
1. How do I send Data1 to "Excel File/worksheet1/Cell C,10" for example?


*the report is customer generated by specifying the date of the report.



i have scoured this forum for some examples but so far all i have seen is copying the whole database to excel; this is not possible with their report format so i need to put specific tag values to specific excel worksheets and cells. I do not have much experience in visual basic or sql queries but am much willing to learn.


any suggestions is most definitely welcome. TIA!
 
Quite easy, make sure your Excel has VBA installed, you must have access to the DB. There are two ways you could do it, one way is to write the query in the VBA, an example is to use a date/time picker accessed via a button on the spread sheet so you can enter a date & time (this is probably the most common way of retrieving data by date). open up the VBA, and use the button event to load the date/time picker, select a date and use the OK button event to write your query and return the data to a sheet.
First type in the search on the task bar odbc and it will show you some apps for connections, create a new connection point it to the database and test the connection save it as what you want to call it.
Another way if you are not too familiar with getting access is to Create a new work book.
Then Click on developer tab, record new macro and give it a name and save it, this will start recording your actions in VB.
Click on the Data tab then from other data sources select data connection wizard.
Select From microsoft query
Select the datasource you created from the list and press ok it should connect
Select the table you want and add the columns you want to query
Then select the date/time field and select the limits i.e. >= a date AND <= another date order by date return the data, then stop recording the macro.
Go to VB code and select Macros this will display the macro code, here you will see how MS created the query, you could copy this and modify it by inserting the Date/time from your date/time picker in place of the WHERE clause is, not brilliant and you will need to know how to write a query properly as a thing as simple as adding in a variable to a query has to be exact.
Here is one I created some time ago but I do not have Excel on this PC but will give you some idea.
Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=test1;UID=localadmin;Trusted_Connection=Yes;APP=Microsoft Office 2010;WSID=PC0851;DATABASE=BCHScad;Network=DBNMPNTW" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT R_Cooler.CoolStarted, R_Cooler.BatchID, R_Cooler.OperatorID, R_Cooler.Cooler, R_Cooler.CoolCompleted, R_Cooler.IBCStarted, R_Cooler.IBCCompleted, R_Cooler.Yield, R_Cooler.Aborted" & Chr(13) & "" & Chr(10) & "FROM BCHScad." _
, _
"dbo.R_Cooler R_Cooler" & Chr(13) & "" & Chr(10) & "WHERE (R_Cooler.CoolStarted>={ts '2007-11-08 16:49:41'} And R_Cooler.CoolStarted<={ts '2006-09-10 19:19:20'})" & Chr(13) & "" & Chr(10) & "ORDER BY R_Cooler.CoolStarted" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_test1"
.Refresh BackgroundQuery:=False
End With
End Sub
Here is a bit of code that uses a DSN configured on the computer to fetch a list of recipes from a DB and populate a dropdown list (note: without all the declarations)

Conn1.ConnectionString = "dsn=Recipes;uid='Cookers';pwd='13324';"
SQLStr = "SELECT Rec_Name,Description FROM Recipe_Headers WHERE Rec_Type=" & Format$(RecType) & " ORDER BY Rec_Name"
Conn1.Open
Rs1.Open SQLStr,Conn1,,,
x = 0
Rs1.MoveFirst
While Not Rs1.EOF
Combos(x) = Rs1.Fields(0).Value
DescriptionArr(x) = Rs1.Fields(1).Value
x = x +1
Rs1.MoveNext
NumRec = Rs1.RecordCount
If x > 299 Then Exit While
Wend
Rs1.Close
Set Rs1 = Nothing
Conn1.Close
Set Conn1 = Nothing
Begin Dialog UserDialog 190,180
Text 10,10,190,25,"Please Select Recipe"
ComboBox 10,50,180,100,Combos(),.combo
OKButton 60,155,60,20

End Dialog
 
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.
 
Yes what a terrible idea, storing data in a database... o_O

I've just about quit posting on this forum just because of this kind of response!

When companies start wanting to expand the intention of what the PLC was designed for it's time to think about moving forward with technology that is built for the future, and SCADA would be that platform.

What an Idea????????????

Duh!!!!
 
Thanks forum, you have been of great help to me, but I must bid you farewell.

I no longer understand most of the people on here and most of them don't understand me.

I will continue to read and enjoy learning (even thought I'm 70 years old), but nobody needs my experience to help them along.

God Bless.
 
Bopol: getting back to your OP, getting data into excel is pretty easy, however, there are a number of things you need to post for anybody to give you some pointers.
Where is the DB located on the actual PC you want to access the report, or is it on a server somewhere, access to the DB.
For example, many companies have servers where DB's are stored and mostly controlled by the IT department, if you are in maintenance then you need to build a good relationship with them and include them in the project.
Can you access the data on the PC you intend to use the Excel report.
The simple way if you can browse the DB is to do a query in Excel to return data but this would not be formulated in a report style. perhaps you could post a snapshot of the DB i.e. a screenshot of the data, is this in multiple tables or just one table. Are you reasonably experienced with VB if so then you can import the data into a sheet, use the VB to populate another sheet pushing the data into certain cells to format it.
Here is an example of an Excel spread sheet this was later re-written in Dot Net as a web page on out intranet as IT do not like macro enabled templates The data was selected by a query that populated sheet 2 then using VB it was manipulated to format & calculate the data on sheet 1 to display it in data & graphs.

Lines.png
 
David, I understand your frustration but I'm sure there are many posters here that have and would continue to enjoy your help, you obviously have a wealth of experience and it would be a sad loss to this site. Unfortunately there seems to be a lot of back biting or one-upmanship perhaps it is as my boss once said engineers tend to be highly strung. What I have noticed more recently that instead of helping the original OP the threads descend into a long drawn out battle and often diverts from the original question and attacks other posters rather than helping the OP.
I too am now retired and of course my brain cells get less every year, and not being in the "loop" anymore can only offer my experience of the past in a fast moving environment.
 
In addition to the "Quite Easy" solution above, there is another alternative and that is to run stored procedures on the SQL server by calling them from VBA.

The VBA subroutine below opens an ODBC connection setup on the PC that links it to the database (security credentials are entered via the spreadsheet). A stored procedure is called returns a recordset of all records that belong to the BatchID that is passed as a parameter (myLocalID). Further subroutines in VBA then sort and format the data for presentation.

Code:
Sub Fetch_TimeSeries_By_LocalIDV2(myLocalID As Long)
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Dim mySheet, myFileName, strTemp As String

On Error GoTo ErrHandler

If myConfig.DSN = "" Then
    ConfigOK = Read_Config
End If


myFileName = ActiveWorkbook.Name
mySheet = ActiveSheet.Name

Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."

Set WSP1 = Worksheets("Time Series Data")
WSP1.Activate
' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(7, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents

' Log into our SQL Server, and run the Stored Procedure

con.Open myConfig.DSN, myConfig.UName, myConfig.PWord
cmd.ActiveConnection = con

Dim prmLocalID As ADODB.Parameter
'Dim myLocalID As Integer

'myLocalID = CInt(Range("C2").Value)

' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
'cmd.Parameters.Append cmd.CreateParameter("myLocalID", adInteger, adParamInput, 10, CInt(Range("D2").Text))
cmd.Parameters.Append cmd.CreateParameter("myLocalID", adBigInt, adParamInput, 10, myLocalID)
Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "Return_TimeData_By_LocalID"
Set rs = cmd.Execute(, , adCmdStoredProc)

' Copy the results to cell B7 on the first Worksheet
'Set WSP1 = Worksheets(1)
'WSP1.Activate
'If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs

Dim iRows, iCols As Integer

iRows = 8

While Not rs.EOF
    For iCols = 0 To rs.Fields.Count - 1
        WSP1.Cells(iRows, iCols + 2).Value = rs.Fields(iCols).Value
    Next iCols
    rs.MoveNext
    iRows = iRows + 1
Wend

rs.Close
Set rs = Nothing
Set cmd = Nothing

con.Close
Set con = Nothing

Set WSP1 = Nothing
Workbooks(myFileName).Worksheets(mySheet).Activate

Application.StatusBar = "Data successfully updated."
Exit Sub
ErrHandler:

'rs.Close
Set rs = Nothing
Set cmd = Nothing

'con.Close
Set con = Nothing
  Workbooks(myFileName).Worksheets(mySheet).Activate
  Application.StatusBar = "VBSub:Fetch_TimeSeries_By_LocalIDV2() error " & Err.Number & Err.Description
  Err.Clear

End Sub

@David Your experience would be missed I'm sure

Nick
 
Last edited:
Thanks Manglemender, I was about to get my old laptop out as that is the one with excel on, you have saved me a job and yes I forgot about stored procedures.
Just a few other pointers, Create the spreadsheet as a template and password protect the VB code so if many staff are to use the template they cannot modify it only save the file as a report as a spread sheet.
 

Similar Topics

Hi All, I am really in a bind,any help would be really appreciated. Here is the project i am working on: I am collecting data to a SQL...
Replies
2
Views
2,301
Respected Members; Good day. I want to perform the following task: 1. successfully fetches the data via RSLINK / DDE to excel. 2. Now i want to...
Replies
17
Views
4,374
I have an application where Intouch needs to send a couple values to Excel, where a unique row will be found. By using a SQL command, 3 other...
Replies
4
Views
5,792
Hi, I'm using Excel and a MySQL database to store data from an AB PLC. Using Microsoft query, i can see data from the database in Excel but I...
Replies
3
Views
2,626
Hi All, I am really in a bind,any help would be really appreciated. Here is the project i am working on: I am collecting data to a SQL database...
Replies
1
Views
2,937
Back
Top Bottom