SQL in Calc?

[asearle]

Hi Everyone,

I often use the following code to query named ranges in sheets (in Excel) using SQL (instead of cell references). I find that this dramatically streamlines what I am doing. Here is a short example …

Dim daodb As DAO.Database
Dim rsReg As Recordset

Set daodb = OpenDatabase(ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, False, True, "Excel 8.0;")

Set rsReg = daodb.OpenRecordset("SELECT Reg_To FROM RegTrans WHERE Reg_From='" & strFrom & "'")

rsReg.MoveFirst

RegTrans = rsReg!Reg_To

rsReg.Close

daodb.Close

I would like to duplicate this functionality in a LibreOffice module but have so far not found the syntax that I need.

I tried importing the VBA and running it but I think this is too tricky for LibreOffice to convert directly.

Can anyone help me with this? Maybe there is a code snippet available?

Many thanks,

Alan Searle

Cologne, Germany

[JohnSUN]

You can do this without a macro - Imitation SQL-query.ods

[kyb]

I cannot edit this file. Cells are not selectable.

[JohnSUN]

Yes, the only cell available for change is Result.C1
Try to unprotect sheet (right-click on tab and choose “Protect sheet” - it protected without password)

[asearle]

Fantastic!

That is exactly what I need.

But is there a way to run “imitation SQL” programmatically: I would like to use this feature to streamline some coded functionality.

Many thanks for sending the example.

Regards,
Alan Searle

[qubit1]

@JohnSUN – Thoughts?

[JohnSUN]

@qubit1 - Unfortunately, there is no thoughts. My knowledge of English is not enough to understand the phrase “use this feature to streamline some coded functionality”, sorry

[qubit1]

@JohnSUN – I think that @asearle is basically saying that he’s looking to simplify the steps he uses to process the data. I’ll need to take a closer look at your code to understand his point

[JohnSUN]

VBA code that @asearle show in his question, just select a set of values ​​Reg_To by the filter Reg_From=. I just showed how it is done without a macro. An additional question I did not understand.

[qubit1]

Ah, okay. Basically, it looks like he’d like to be able to use SQL (or some SQL-esque language) to interact with his spreadsheets.

I haven’t heard of that functionality in Calc, although I believe that one can import/open a spreadsheet into Base and have it act like a database, right? So perhaps you could open a spreadsheet as a database in Base, and then call into that db from Calc and run an SQL query on it? (That’s a wild guess!)

[JohnSUN]

Yes, this is one of the variants. Besides Calc has a whole section of functions to work with the tables as database. However, each task requires an individual approach. But we know nothing about the tasks of @asearle .

[rautamiekka]

NOT AN ANSWER !

[qubit1]

@asearle,

Still looking for an answer here? Please let us know.

Thanks.

[rautamiekka]

NOT AN ANSWER ! DELETE !