Good Morning,
Here is what I am doing manually:
This moves data from Base to Calc very well. However is there a way to accomplish this exact task (including filter) through macro? Thank you very much for your help.
As I can refresh the data from this connection I never tried. But as a first step: What shall your macro do?
- Create the same connection
or - Open the database and copy data “by script” from database to sheet, without creating a connection for later refresh.
- You need an additional “filter” on the sheet or is the selected query your filter?
Good Morning,
It should use the second option, and the filter should be on the query.
Thank you.
So lets say it is possible. Some years ago I adapted a macro from the Handbook for Base (german Version mostly by Robert Grosskopf) to sync between two databases for my needs. I have to check, if the english Version contains the same macros. The version seems to be older at tdf: Base Guides | LibreOffice Documentation - LibreOffice User Guides
.
Actually the german page on guides/handbooks seems to be completele different…
HandbĂĽcher | LibreOffice - Deutschsprachiges Projekt - Freie Office Suite
I will check. Thank you.
Hello,
You do not need much of a macro if you set the Query to a database range. Then it is a just a couple of lines of code to refresh the range.
.
See Calc macro failing to update sheet data from Base query . It uses the data (via macro) to update a chart, but this you can ignore.
Well I am learning much. I will check this as well.
This looks like a good solution which I might use later. I have decided that manually is good enough for now. Thank you.
@Ratslinger is there a way of refreshing all the ranges in a spreadsheet with a variation on the macro you referred to in Calc macro failing to update sheet data from Base query - #3 by Ratslinger please?
I have a spreadsheet that imports the results of 5 queries from one database. All the queries use the same :AppraisalID criterion to select one record and it’s related linked records in other tables. I cannot use a single query partly because it would generate masses of unnecessary data and partly because one of the queries is a count of the number of records in a linked table.
I can do this manually by selecting each Range in the sheet, refreshing it, and then filling in the integer of the AppraisalID when prompted.
It would seem to be more efficient to run a macro that asks the AppraisalID once and then updates all the 5 queries without asking me to input the AppraisalID again.
Thank you very much for any help you could suggest with this.
The following way is possible.
' Refresh the Database Range (DBRangeName) data with a new SQL query (newSQL).
Sub DBRangeNewImport(ByVal oDoc As Object, ByVal DBRangeName As String, ByVal newSQL As String)
Dim oDBRange As Object, oDesc, prop
oDBRange=oDoc.DatabaseRanges.getByName(DBRangeName)
oDesc=oDBRange.getImportDescriptor
For Each prop In oDesc
If prop.Name="SourceObject" Then prop.Value=newSQL
Next prop
oDBRange.referredCells.doImport oDesc
End Sub
Thank you sokol92 for your quick reply. I have added your macro to my spreadsheet.
I am getting the following error when I run it:
A Scripting Framework error occurred while running the Basic script Standard.Module1.DBRangeNewImport.
Message: wrong number of parameters!
I haven’t made any changes to the macro - do I need to put any information like the name of the database anywhere?
Thank you very much for your help.
Hello @papasierra !
I tested my macro and it works.
Maybe we’ll do that. You will upload test files: a database (.odb) and a spreadsheet (.ods) that contains two database ranges with parameterized queries. Next, we’ll add a macro that updates these ranges by issuing a single prompt for a parameter.
Test Database.odb (17.5 KB)
Test Base transfer.ods (20.2 KB)
Apologies for the delay. Here is a stripped down database and a simplified spreadsheet with 3 queries imported. I have added your macro to the spreadsheet but the error I mentioned previously pops up. There are 2 appraisees with AppraiseeID 1 and 2.
Thank you very much for your help with this.
Hi sokol92,
Thank you very much for your efforts. I am a very simple soul and have very little experience in understanding programmes.
At first your macros did not run. However I then realised there were some variables that needed changing for the macros to run in my original database.
These were:
Change the name of “Test Database” to the name of my original database.
oDBSource=CreateUnoService("com.sun.star.sdb.DatabaseContext").getByName("Test Database")
Change the name of the queries in the line
qryName=Array("QryAppraisalRoles", "QryAppraisalCompNo", "QryAppraisee")(i-1)
to the names of the queries in the original database.
And then to change the number 3 in the line
For i=1 To 3
to the number of queries I have added.
I mention these changes only for the benefit of others who have as little knowledge as I do with macros.
It only leaves me to express my immense gratitude to you for taking the time to create these macros.
Thank you very much!
2 Likes