Dear Sir ,
I do not not what i missed but it seams that oResult = oQuery.executeQuery(sSql) will have the information of total lines. I need tha information in order to Rdim(MyArr(RowCoun)) instate of
assign some hipotetic number of rows since i dont have some array.add( mynew .value)
I must RDim ( n ) befor adding a line. Right?
Why? What is the purpose? Linking a spreadsheet range is a drag&drop operation.
Filling a Writer table with a data copy can be done similarly.
You can even fill tables in Impress with database data.
Feeding some third party application with data can be ruled out since you are using Basic.
Generating csv can be done easily without any macro code either via Calc or using the SQL language of the connected database engine. MySQL, PostgreSQL, HSQL and any others can generate text files.
Good luck getting a reasonable answer. Been asking for that for weeks in other questions.
.
@dosreis57
You can get a count by executing another SQL statement first. Use an SQL statement such as:
Select Count(myField) From MyTable Where x = something
.
But better yet is to use a result set which will get you the count and your SQL results all ar once. Example:
Dim oRS as Object
Rem Create and execute row set
oRS = createUnoService("com.sun.star.sdb.RowSet")
oRS.DataSourceName = "Registerd DB Name"
oRS.CommandType = com.sun.star.sdb.CommandType.COMMAND
oRS.Command = "Select * from ""Table-Name"""
oRS.Execute
oRS.Last
Print oRS.RowCount
oRS.First
Print oRS.getString(2)
Go to the last record & get the count then return to the first and you then continue on with oRS to get the results.
Also see → LibreOffice: RowSet Service Reference
.
Edit:
For those wondering why I used a registered DB name - The OP has a habit of not providing information. This “project” has gone from Calc to, at least from my last observance, to Writer. Also the DB (gleaned from code) is Firebird but unclear as to embedded or server. Also claimed to be ODBC but I have never gotten that to fully work with LO 64-bit. Maybe using 32-bit. Probably using Windows (version??) as OS.
.
Edit 2022-11-20:
This also has the added benefit of not needing a separate connection method. Even has capabilities if needing to enter a user name and password.
.
Edit 2022-11-24:
.
Works with HSQLDB embedded but not with Firebird Embedded or server. Error after reading about 50 records.
.
My error - works without issue as long as the sequence of insructions is correct! 
So we do not even know which software we are talking about like in 50% of all Base related questions. I’m out.
I’Sorry I’m talking about LibreOffice Writer, Basic Macros + ODBC firebird access + Grid in a Dialog. Perhaps an image should help you to understand what i’ trying to do.
A command button to help users select supplier companies from a (db) Grid in a dialog. Since a Grid is similar to a matrix with rows and columns it probably as embed methods and inbuilt functions to run over those rows and columns them looking for data.
Since i’ m a beginner in Libre and basic macros, it’s kind of an hard thing for me to do.
However, after digging hard i already achieved a few helpful things:
Where:
1- Search conditional(4) for query in order to get data to feed the grid;
2- locate data in a certain column in the grid;
3- ordered column mentioned above;
4- Input conditional for (1)
5- locate data “From the beginning or from this one” (first/0 row /column Empresa or the row you are placed in
(data transferred for an array perhaps there is no need and it can be done on the grid, i just don’t know how!?)
6-Select data from the Grid in order to transfer it to letter fields, such as seen in the demo: Name, Adress, Place and Zip.Code
Andreas Säger via Ask LibreOffice <noreply@ask.libreoffice.org> escreveu no dia sábado, 19/11/2022 à(s) 21:24:
Hello,
an example:
Sub Fill_Array
nCounter = 0
Dim MyArr (nCounter)
oController = ThisDatabasedocument.CurrentController
if not oController.isconnected Then oController.connect
oConnection = oController.activeConnection
oStatement = oConnection.CreateStatement
sSql = "SELECT ""Nachname"", ""Vorname"", ""Adresse"", ""Postleitzahl"", ""Ort"" FROM ""Adressenliste"""
oResult = oStatement.executeQuery(sSql)
while oResult.next
redim Preserve MyArr(nCounter)
MyArr(nCounter) = dimArray(4)'one row
aRow = MyArr(nCounter)
aRow(0) = oResult.Columns.Nachname.getstring
aRow(1)= oResult.Columns.Vorname.getstring
aRow(2) = oResult.Columns.Adresse.getstring
aRow(3) = oResult.Columns.Postleitzahl.getstring
aRow(4) = oResult.Columns.Ort.getstring
MyArr(nCounter) = aRow
nCounter = nCounter + 1
wend
end Sub
Hello,
This seems like a very nice example. Thank you for your help.
But! MeanWhile i realize that the Grid itself is already an object with “graphic representation” but also containing a matrix array.
If so i’ve no need to overload my memory with a new redundant matrix.
So! I just need to understand and discover if it’s possible to run over it. And how can i run over this matrix row by row and then 7(seven) columns each with no need to implement
without having to create a matrix/array redundancy. I just need to compare if my_key is InStr cell grid, step by set.
Then if key is found i only have to perform a: oDlg.getControl(“tab_g1”).goToCell( nColumn, nRow) ’ Instead of oDlg.getControl(“tab_g1”).selectRow( n )
Probably it is some kind of the very same code for doing it with scal. But how?
Any ideas? Can you help.
Best regards
Lucky Müller via Ask LibreOffice <noreply@ask.libreoffice.org> escreveu no dia sábado, 19/11/2022 à(s) 16:11:
Sample with zero macro code.
Replace Ratslingers registered database with
DBforAddressInfo.odb (6.2 KB) (still works with Ratslinger’s sample).
A letter template with a filter form in the letter head, invisible when printed:
WhoToSend.ott (34.4 KB)
Ok. Best regards
Andreas Säger via Ask LibreOffice <noreply@ask.libreoffice.org> escreveu no dia sábado, 26/11/2022 à(s) 19:19:
@dosreis57
You still did not provide all the info so I dug into some of your other posts and found (according to your posting) using Windows10, LO v7.3.5.2 and ODBC 32-Bit (still have not seen Firebird server work under ODBC in LO). If you have a working model using this, a post with “details” would be of help to others.
.
Out of curiosity did take a little time to create a test file. Basically used all the pieces had previously given to you in other posts and above except for one - Text Master fields. For that see → Writer Macro to collect and insert data from Base Query - #2 by Ratslinger
.
The dialog I generated is somewhat different than yours. To me yours felt too busy. Tested with Firebird Server, Firebird embedded, PostgreSQL and HSQLDB embedded with all providing the same results. Initial dialog (note toolbar item on Writer doc to initiate the dialog):
There are radio buttons to select the column to search (one at a time), field to entr sarch text, button to activate search and another button to close the dialog. An empty text box will retrieve all records. A double left mouse button click on a grid row will insert that rows’ data into the Writer user fields.
Two files are attached. The database file needs to be registered in LO as DBforAddressInfo. With that, the sample Writer document will work without modification.
Have comments in the code and extra setting also there as commented code. Design is up to you for modification.
DBforAddressInfo.odb (5.5 KB)
FillFieldsFromGrid.odt (16.4 KB)
Edit 2022-11-26:
Have a modified version of the Writer doc and macro. DB stays the same. Uses all selected items and added button to go to next selected and clears previously selected item. Screen shot shows advancement already taken place:
FillFieldsFromGridTestSearchString.odt (16.9 KB)
@dosreis57 To search the strings I used InStr Function