Xojo: Manage Excel Reports Through Automation

By | 2017-03-15

Introduction
Here I go again! This time I use Xojo to control and manipulate MS Excel’s files with. Here I use the add-in for MS Office that is shipped with Xojo. It requires that MS Excel is installed. At present I look through my add-ins to see if I still have the add-in for MS Excel and that does not require MS Excel to be installed.

Meanwhile, I continue to use the two examples that I first published here, a Chart report and a Pivot report together with a SQLite database. I find it interesting to see how the software various when it come to interaction with MS Excel.

Here is the simple GUI solution I use, both solutions are hooked to the button’s Action event:

Of course, a console solution may be more appropriated…

First out is the code for the Chart report:


Dim xlApp As New ExcelApplication
Dim xlWBook As ExcelWorkbook
Dim xlWSheet As ExcelWorksheet
Dim xlRange As ExcelRange

Dim dbSQLite As New SQLiteDatabase
Dim rs As RecordSet
Dim vaMonth As Variant
Dim intRow As Integer
Dim intColCounter As Integer

intColCounter = 4

Dim f as FolderItem
f = GetFolderItem("").Parent.Child("res\Production.db")

dbSQLite.DatabaseFile = f

If dbSQLite.Connect = False Then
MsgBox "Unable to open the database." + EndOfLine + _
dbSQLite.ErrorMessage
Return
End If

rs = dbSQLite.SQLSelect("SELECT Month, Result FROM Prod_Output ORDER BY Month;")

Dim fi As FolderItem
fi = GetFolderItem("").Parent.Child("res\ChartReport.xlsx")

xlApp.Visible = False

xlWBook =xlApp.Workbooks.Open(fi.AbsolutePath)
xlWSheet = xlWBook.Worksheets(1)

If rs <> Nil Then
Do Until rs.EOF
vaMonth = rs.Field("Month").Value
Select Case vaMonth
Case "January"
intRow = 4
Case "February"
intRow = 5
Case "Mars"
intRow = 6
Case "April"
intRow = 8
Case "May"
intRow = 9
Case "June"
intRow = 10
Case "July"
intRow = 12
Case "Augusti"
intRow = 13
Case "September"
intRow = 14
Case "October"
intRow = 16
Case "November"
intRow = 17
Case "December"
intRow = 18
End Select

If intColCounter = 9 Then intColCounter = 4

xlRange = xlWSheet.Cells(intRow, intColCounter)
xlRange.Value = rs.Field("Result").Value

intColCounter = intColCounter + 1

rs.MoveNext
Loop
Else
MsgBox "No records were found!"
Return
End If

Dim d as New Date
Dim vaDate As Variant
Dim vaTime As Variant

vaDate = d.ShortDate
vaTime = d.ShortTime

Dim vaResult As Variant
vaResult = Replaceall(vaDate,"-","") + Replace(vaTime, ":","")

dim ff as FolderItem
ff = GetFolderItem("").Parent.Child("Reports\ChartReport" + vaResult + ".xlsx")

xlWBook.SaveAs ff.AbsolutePath

'Releasing all Database Objects
rs.Close
dbSQLite.Close
rs = Nil
dbSQLite = Nil

'Releasing all Excel Objects
xlApp.Quit
xlRange = Nil
xlWSheet = Nil
xlWBook = Nil
xlApp = Nil

Exception err as OLEException
MsgBox err.message

Next, here is the code for the second report, the Pivot report:

Dim xlApp As New ExcelApplication
Dim xlWBook As ExcelWorkbook
Dim xlWSheet As ExcelWorksheet

Dim dbSQLite As New SQLiteDatabase
Dim rs As RecordSet

Dim intCountRs As Integer
Dim intCounter As Integer

Dim f as FolderItem
f = GetFolderItem("").Parent.Child("res\Production.db")

dbSQLite.DatabaseFile = f

If dbSQLite.Connect = False Then
MsgBox "Unable to open the database." + EndOfLine + _
dbSQLite.ErrorMessage
Return
End If

rs = dbSQLite.SQLSelect("SELECT Dept, Month, Result FROM Prod_Output GROUP BY Dept, Month;")

intCountRs = rs.RecordCount

If intCountRs > 0 Then

'For unknown reason MS Excel is opened during the execution...
xlApp.Visible = False

Dim fi As FolderItem
fi = GetFolderItem("").Parent.Child("res\PivotReport.xlsx")

xlWBook =xlApp.Workbooks.Open(fi.AbsolutePath)
xlWSheet = xlWBook.Worksheets(1)

intCounter = 0
Do Until rs.EOF
xlWSheet.Range("C4").Offset(intCounter).Value = rs.Field("Dept").Value
xlWSheet.Range("D4").Offset(intCounter).Value = rs.Field("Month").Value
xlWSheet.Range("E4").Offset(intCounter).Value = rs.Field("Result").Value
intCounter = intCounter + 1
rs.MoveNext
Loop

Else
MsgBox "No records were found!"
Return
End If

Dim d as New Date
Dim vaDate As Variant
Dim vaTime As Variant

vaDate = d.ShortDate
vaTime = d.ShortTime

Dim vaResult As Variant
vaResult = Replaceall(vaDate,"-","") + Replace(vaTime, ":","")

Dim ff as FolderItem
ff = GetFolderItem("").Parent.Child("Reports\PivotReport" + vaResult + ".xlsx")

xlWBook.SaveAs ff.AbsolutePath

'Releasing all Database Objects
rs.Close
dbSQLite.Close
rs = Nil
dbSQLite = Nil

'Releasing all Excel Objects
xlApp.Quit
xlWSheet = Nil
xlWBook = Nil
xlApp = Nil

Exception err as OLEException
MsgBox err.message

Both these solutions give a view how to work with MS Excel in Xojo. As we can see Xojo is shipped with a built-in class to manage the database. Again I find the code itself rather self explanatory.

Happy coding!

Dennis

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.