I'm working on a short macro in a Base document which is supposed to open a report then execute an SQL statement yet fails with the following error at the line defining the statement:
SUB s_Open_ReportSessions
Dim oStatement As Object
ThisDatabaseDocument.ReportDocuments.getByName("CERT-PACK-REPORT").open
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
FROM_STATEMENT = "SELECT ""FILEPATH_FROM"" INTO TEXT ""FILEPATH_FROM.CSV"" FROM ""View1"""
oStatement.execute(FROM_STATEMENT)
END SUB
I'm no macro programmer so it's probably simple, but from the examples around (like this one viewtopic.php?f=45&t=42882 ) I can't get the statement to execute.
Anyone see what I'm doing wrong?
Last edited by tdsmart on Mon Mar 03, 2014 10:19 am, edited 1 time in total.
LibreOffice 4.1.3.2 on Windows 7 / LibreOffice 4.1.2 Debian 8 'Jesse'
I think it is not a good idea what you want do. Reports do work different then forms. Also there a different between the old style report and the reports from the reportbuilder.
I read that post and what I understood relevant to my code was that I needed to explicitly open the database connection, and so added the 'if' switch to connect, however came up with the same error.
Having said that, the report opens just fine and that progress is very good, I am just having issues writing some data to csv in the macro. Is there an easier / more efficient way to do this than executing the SQL statement "SELECT X INTO TEXT /FILE/PATH FROM Y"?
LibreOffice 4.1.3.2 on Windows 7 / LibreOffice 4.1.2 Debian 8 'Jesse'
tdsmart wrote:I understood relevant to my code was that I needed to explicitly open the database connection, and so added the 'if' switch to connect, however came up with the same error.
tdsmart wrote:Having said that, the report opens just fine and that progress is very good, I am just having issues writing some data to csv in the macro. Is there an easier / more efficient way to do this than executing the SQL statement "SELECT X INTO TEXT /FILE/PATH FROM Y"?
I think copy and paste or execute the SQL line in the SQL tool is maybe the best methode. Writing macros is so time consuming that you can better type 1000 times the same line then writing macros.
When I did answer the question first it was not clear to me that the problem was maybe in the use of thisdatabasedocument and thiscomponent. This are such easy commands that I did assume they were good. So it is good to tell a little more about those predefined variables.
Thisdatabasedocument and thiscomponent are predefined variables who point to a document. When you them be sure you know to which object, which document, they are pointing. This makes also clear you can never copy the code and use them without understanding the code. When you use Thiscomponent in Mymacros it differ a little from the behavior in a macro what is stored in a document.
As far I understand when you use it in Mymacros then it points to the current document when you use it in a macro stored in a document then it points always to the document to which it belongs. I do not know if this is the same for thisdatabasdocument but I do not trust that.
Running macro from IDE
When you use Thisdatabasedocument or Thiscomponent in macros running from the IDE then they can work real different then when they are start from a button or in an other way. So this makes clear with some code we can not say any thing about the problems. So when you start macros be sure they start in a good manner and they can point to the good objects.
Searching for errors
When you start programming then you need also skill for finding errors. in BASIC and also the api does have some parts for finding problem. One instruction I use real often is:
print oObject.implementationname.
Then I have the idea which kind of object I do use. A BASIC function I use also is: HasUnoInterfaces. Then there are also the program/extension: MRI and Xray. Whit both you can find a detailed idea about the object you use.
Thanks for the tips RPG - I read the BASIC guidebook and found the correct procedure. Basically I saved the report I was trying to generate as an external calc report, and the macro first opened that report (as a url), exited the form from which the macro ran (the macro itself was stored in My Macros as opposed to the document) and finally ran an external batch script to execute the sqltool. Not finished the project yet but close....
Will post the macro up here later - it's not on this pc now.
LibreOffice 4.1.3.2 on Windows 7 / LibreOffice 4.1.2 Debian 8 'Jesse'
