[Solved] Writing Calc Specific Cell Values Into Base

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
burkeadams
Posts: 3
Joined: Sun Jul 16, 2017 9:37 pm

[Solved] Writing Calc Specific Cell Values Into Base

Post by burkeadams »

Hello OpenOffice Community,

I am a restaurant owner, and have been using OpenOffice for many, many years now to achieve my own custom accounting goals, but am now looking to centralize all this data. For example and for starters, I have a spreadsheet that is used each day by my management to ensure that the previous day's tills have all correctly balanced based on the end of the day actuals. Then I have a second spreadsheet that I use each week to balance the weekly cash deposit I take to the bank, based on the data from the daily spreadsheets. I have a simple file saving macro that organizes and dates these files as they are filled out, and the input from the daily files into the weekly files is currently manual. What I'm trying to do now is automate this as much as I can by pushing very specific cell data out of my daily calc files, and pulling them into my weekly calc file. I have presumed that using Base was the best way to achieve this. I've fully explained the use case because I am also open to suggestions that may be better than this idea in it's entirety.

I've been working on a macro to get this to work, and everything functions 100% correctly when I manually enter values to populate the database table into the macro. The problem is that I cannot seem to find the syntax to use in the SQL statement for calling out a cell's current value. Here's what I have, which is just using HSQL for now:

Code: Select all

Sub UpdateDatabase

Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Dim sDifference As Currency
Dim sLottery As Currency
Dim sBank As Currency
Dim sRevenue As Currency

oDoc = thisComponent
oSheet = oDoc.getSheets.getByName("Daily")
oDifferenceCell = oSheet.getCellRangeByName("N13")
oLotteryCell = oSheet.getCellRangeByName("B37")
oBankCell = oSheet.getCellRangeByName("B38")
oRevenueCell = oSheet.getCellRangeByName("F17")

sDifference = oDifferenceCell.getValue
sLottery = oLotteryCell.getValue
sBank = oBankCell.getValue
sRevenue = oRevenueCell.getValue

Context=CreateUnoService("com.sun.star.sdb.DatabaseContext")
DB=Context.getByName("DBName")
Conn=DB.getConnection("","")

Stmt=Conn.createStatement()
strSQL="INSERT INTO ""B1DailyAccounting"" (""Date"",""Over/Short"",""Lottery"",""Bank"",""Revenue"") VALUES(TODAY,'sDifference','2.22','3.33','4.44')"
Stmt.executeUpdate(strSQL)

Conn.close()

End Sub
The problem is with the strSQL line. The VALUES TODAY, 2.22, 3.33, and 4.44 work fine, but I cannot call any of the cell values I've set up above, shown by 'sDifference', which clearly isn't being recognized. Again, it works great if I set a manual value there, so I know the database connection is working. I'm wondering if what I'm trying to do is possible at all, given I'm using SQL inside of OpenOffice Basic, and then trying to call Basic functions back through SQL.

Any insight would be much appreciated.

-Burke
Last edited by burkeadams on Mon Jul 17, 2017 4:50 pm, edited 1 time in total.
OpenOffice v4.1.3
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Writing Calc Specific Cell Values Into Base

Post by Villeroy »

TODAY returns a Basic date.
'2.22','3.33','4.44' are strings
'sDifference' is a literal string

Open your database window, call Tools>SQL and check out the right syntax for the fields types. If you can't do it in the GUI, you will never do it with macros.

I would simply atach a form to the sheet (new record only) and drag the cells into the form controls.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
burkeadams
Posts: 3
Joined: Sun Jul 16, 2017 9:37 pm

Re: Writing Calc Specific Cell Values Into Base

Post by burkeadams »

Sounds like I need to educate myself on the use of forms. Thanks for pointing me in the right direction, Villeroy, much obliged.
OpenOffice v4.1.3
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Writing Calc Specific Cell Values Into Base

Post by Villeroy »

The spreadsheet attached viewtopic.php?t=88516&p=416210#p416210 includes one input form, two form for filter criteria, a linked database range and a pivot table demonstrating most of the things you can do with Base and Calc without macros or spiced up with very few lines of code. Register the database document as "PowerFilter".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
burkeadams
Posts: 3
Joined: Sun Jul 16, 2017 9:37 pm

Re: [Solved] Writing Calc Specific Cell Values Into Base

Post by burkeadams »

This will save me a whole bunch of time. Thanks again, Villeroy!
OpenOffice v4.1.3
Windows 10
Post Reply