질문하기

greb's profile - activity

2021-02-16 21:51:41 +0100 질문에 댓글을 달았습니다 Speed file copy from macro

@JohnSUN, Thanks for the indications. I've indeed succeeded using Linux cp command from macro. It doesn't bring any inf

2021-02-16 21:24:53 +0100 받은 배지  인기있는 질문 (source)
2021-02-15 21:32:27 +0100 질문에 댓글을 달았습니다 Speed file copy from macro

@JohnSUN, Thanks for your comment. I never used Shell(), but sounds interesting. With it, is it possible to launch a

2021-02-03 14:20:26 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-02-03 14:19:31 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-02-03 14:19:05 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-02-03 14:11:35 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Thanks again for feedback. Indeed, the ODB file registration checking was problematic and due to error. I

2021-02-02 18:13:44 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Have solved the LIST field issue, using a CAST function, see my question post. As well, have sorted out t

2021-02-02 18:10:31 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-02-01 14:27:59 +0100 코멘트달린 답변 Base - use of SDBC Firebird File

Hello @Ratslinger, Once again, thanks a lot for taking time to answer to my question. You're right, "brief" is not a v

2021-02-01 14:17:19 +0100 받은 배지  주목할 만한 질문 (source)
2021-01-31 01:25:38 +0100 받은 배지  인기있는 질문 (source)
2021-01-30 13:37:22 +0100 질문하였습니다 Base - use of SDBC Firebird File

Base - use of SDBC Firebird File Hello, I'm not really in a problem solving, but while testing LO Base possibilities, w

2021-01-28 20:39:20 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Ok, noted for auto registration. Alternative may so just be to add a small text to explain how to register

2021-01-28 11:39:26 +0100 으뜸 답변으로 표시됨 Base - Export data of a stored query in a csv file within a macro

Hello,

I would like to export data of a stored query to a csv (or any character delimited) file. I know how to do by graphically copy or drag and drop the query content to a Calc spreadsheet and then save it in csv.
But what I need is to handle all these actions within a macro, that only requires from the end-user to just click on a button in the form.

I've note found how to do in my searches (but may have not searched well…?), and would need some advices to guide me.

Would this need for example to use the SaveDataToFile(FilePath as String, DataList()) LibreOffice function (Tools > UCB)? But then, how to get the query data (several rows and several columns) matching the DataList() type?

Note: I'm using an embedded Firebird-LibreOffice database, on Debian 10, with LibreOffice 7.0.1.2 (but need that the macro works well in a Windows environment).

Here is an example file with table, query, form (with button) prepared, but with empty macro, if it is useful: export_csv.odb

Thanks in advance for any help, and please ask if any more detail required!


[EDIT] - 2021-01-24

A potential solution would be to use a SQL query, and LibreOffice open and write function.

As an example, if it can be useful for some (even if I'm not sure this code is the best code than can be found):

Sub ExportCsv
GlobalScope.BasicLibraries.loadLibrary("Tools")
Dim iCount As Integer
Dim sValue As String
Dim oDoc as Object
Dim sUrl_Source as String
Dim sFolder as String

' Initialize the file (??)
iCount = Freefile

' Get the odb document
oDoc = ThisComponent.Parent

' Get the url (folder + filename) of the current database file
sUrl_Source = oDoc.URL

' Extract the folder of the current database file
sFolder = DirectoryNameoutofPath(ThisComponent.Parent.getUrl(),"/")
sFolder = ConvertFromUrl(sFolder)

' Open the text file
Open sFolder & "/test.txt" For Output As iCount

' Specific SQL handling declaration and preparation
Dim oDatasource as Object
Dim oConnection as Object
Dim oSQL_Command as Object
' Activate the SQL connection
oDatasource = thisDatabaseDocument.CurrentController
If Not (oDatasource.isConnected()) Then
    oDatasource.connect()
End if
oConnection = oDatasource.ActiveConnection()
oSQL_Command = oConnection.createStatement()

' Check if file already indexed in the application  
stSql = "SELECT * FROM ""Query1"""
oResult = oSQL_Command.executeQuery(stSql)
While oResult.next
    sValue = oResult.getString(1) & "," & oResult.getString(2)
    Write #iCount,sValue
Wend

Close #iCount
End Sub

Inspired from write page and this thread.

See as well for eg. the attached file

However, on my case (not exactly the same as the attached file), I have some cell in my table where there are more than 1 line (I deliberately input newline characters). In those case, only the first line in the cell is reproduce during the write process.

Of course a function on type "write2csv" could be very useful, but at least, this can do some part of the job for some, hopefully.


[EDIT] - 2021-02-02

I've been through the different options and files submitted below in the answer by Ratslinger ... (more)

2021-01-28 11:36:58 +0100 받은 배지  오거나이저 (source)
2021-01-28 11:32:37 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Another point, but which I think is very specific to my query: two columns of my query result in N/A value

2021-01-28 11:21:38 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Thank you very much for all this review and solution work! Both solution are working very well. On my si

2021-01-25 22:53:41 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Thank you very much for your time and this much documented solution! I'll have a deeper look and test it

2021-01-25 22:51:23 +0100 받은 배지  유명한 질문 (source)
2021-01-24 20:17:29 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-01-24 19:36:50 +0100 질문을 편집했습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2021-01-24 18:12:48 +0100 질문에 댓글을 달았습니다 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, It's ok, no problem. Anyway, thanks for the updated information. Eventually, I will opt for the "easy" gr

2021-01-21 08:19:57 +0100 질문에 댓글을 달았습니다 Base - Export data of a stored query in a csv file within a macro

@Ratslinger, Thanks for offering. Indeed, I would have preferred a "direct" way, but still, this can be useful. I will

2021-01-21 08:16:24 +0100 코멘트달린 답변 Base - Export data of a stored query in a csv file within a macro

@gkick and @Ratslinger, Sorry for the delay of my answer, and thanks for your suggestion @gkick and for the Firebird pr

2021-01-21 04:10:23 +0100 받은 배지  주목할 만한 질문 (source)
2021-01-20 03:57:36 +0100 받은 배지  인기있는 질문 (source)
2021-01-19 14:51:02 +0100 질문하였습니다 Base - Export data of a stored query in a csv file within a macro

Base - Export data of a stored query in a csv file within a macro Hello, I would like to export data of a stored query

2020-12-28 18:09:49 +0100 받은 배지  주목할 만한 질문 (source)
2020-12-01 17:38:28 +0100 받은 배지  유명한 질문 (source)
2020-12-01 17:23:08 +0100 받은 배지  주목할 만한 질문 (source)
2020-11-16 17:08:16 +0100 코멘트달린 답변 Modify component (filter) of a Base report in a macro

@Ratslinger, Thank you for the tips. Indeed, I restore the 'Where' clause when done. The parameter passing from one m

2020-11-13 17:06:06 +0100 코멘트달린 답변 Modify component (filter) of a Base report in a macro

@Ratslinger, Thanks for all you feedbacks. - I moved to a 'Where' clause option better than a de/activation and filte

2020-11-13 16:52:52 +0100 받은 배지  주목할 만한 질문 (source)
2020-11-12 09:41:43 +0100 으뜸 답변으로 표시됨 Modify component (filter) of a Base report in a macro

Hello,

I have a report in a Base file, that I would like to filter from a macro.

As many other objects in macros, I thought that the report properties could be modified…
And indeed, I don't know why, in the structure of the report object in the macro, the before last subvariable, Component, was not Null, but contained a set of subsubvariables, in which a filter subsubvariable.

I think this one is linked to the Filter control that we can find in the Report builder (when Analyse SQL command is on Yes).

But, this is my issue, I cannot get back this filter subsubvariable, as the Component subvariable has Null as value…

What to do to be able to manipulate the Component subvariable and filter subvariable from the macro.
(the filter control in the report builder GUI works fine, but I need to handle the filter through a macro).

Thanks in advance for any help!

(LibreOffice 7 on a Debian laptop)

2020-11-12 09:41:43 +0100 코멘트달린 답변 Modify component (filter) of a Base report in a macro

As well, my filter is coming from another macro. I declare it just outside before this other macro like this: Global m

2020-11-12 09:17:56 +0100 코멘트달린 답변 Modify component (filter) of a Base report in a macro

I just needed (but it is the right way?) to activate the ApplyFilter subvariable of stQuery: ' Get access to Queries oD