Ask Your Question
0

SQL in Calc? [closed]

asked 2012-11-12 13:05:31 +0100

asearle gravatar image

updated 2013-03-01 07:14:01 +0100

qubit gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-27 01:49:42.901789

3 Answers

Sort by » oldest newest most voted
1

answered 2012-11-12 16:44:58 +0100

JohnSUN gravatar image

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

edit flag offensive delete link more

Comments

I cannot edit this file. Cells are not selectable.

kyb gravatar imagekyb ( 2017-04-11 10:14:28 +0100 )edit

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)

JohnSUN gravatar imageJohnSUN ( 2017-04-11 12:05:15 +0100 )edit
0

answered 2013-03-17 19:38:35 +0100

qubit gravatar image

@asearle,

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

Thanks.

edit flag offensive delete link more

Comments

NOT AN ANSWER ! DELETE !

rautamiekka gravatar imagerautamiekka ( 2016-09-14 10:38:31 +0100 )edit
0

answered 2012-11-12 19:21:36 +0100

asearle gravatar image

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

edit flag offensive delete link more

Comments

@JohnSUN -- Thoughts?

qubit gravatar imagequbit ( 2013-03-14 01:45:51 +0100 )edit

@qubit - 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

JohnSUN gravatar imageJohnSUN ( 2013-03-14 11:03:21 +0100 )edit

@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 :-)

qubit gravatar imagequbit ( 2013-03-14 11:14:56 +0100 )edit

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

JohnSUN gravatar imageJohnSUN ( 2013-03-14 11:32:10 +0100 )edit

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!)

qubit gravatar imagequbit ( 2013-03-14 11:40:16 +0100 )edit

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 .

JohnSUN gravatar imageJohnSUN ( 2013-03-14 12:18:53 +0100 )edit

NOT AN ANSWER !

rautamiekka gravatar imagerautamiekka ( 2016-09-14 10:38:08 +0100 )edit

Question Tools

Stats

Asked: 2012-11-12 13:05:31 +0100

Seen: 11,051 times

Last updated: Mar 17 '13