Is there a way to change the source of a database range from a macro?
I have a defined range attached to a database source, I want to dynamically change the source by macros.
The new source will have the same number of columns an fit into the defined range.
Is the range linked to a database query? Just check “Insert/Remove rows and columns” in the extra options of that database range. This will adjust the range with each refresh and expand/shrink adjacent calculations.
Yes, the range is linked to a database table.
Expanding / shrinking the row is not a problem, I would like to change the database source from TableA to TableB, and C and so on.
Now I have to open to Data Sources View and do the change manually, a but cumbersome when The use is to change back and forth several times between different data source tables for the same database range.
Link it to a parameter query like this one:
SELECT *
FROM "somewhere"
WHERE Lower("Client Name") = Lower(:Please_Enter_Client_Name)
ORDER BY "something"
Every time you refresh the database range, you will be prompted for the parameter (multiple parameters are possible).
A lot more things that can be done on sheet: Apache OpenOffice Community Forum - [Solved] Using Filter in Calc V3.1 with dates - (View topic)
OK, I’ll try this.
It might work for me, but not for my users, they need it as simple as possible.
My idea was to have three push buttons which each run a macro changing the database range source from tableA, B and C and doing a refresh of the range.
Then a push of the button will change the input to the sheet and give a new/different perspective of the issue in question.
Is this not possible to achieve?
Button1 opens a Calc document with data linked to tableA.
Button2 opens a Calc document with data linked to tableB.
Button2 opens a Calc document with data linked to tableC.
Also possible with hyperlinks pointing to sheets in the same Calc document.
Naaah 
It was a simplified scenario I described.
Still there is constants and other variables in the sheet which should be kept while changing the “source” of the input data to the sheet.
Would it be possible to define 35 ranges, with 35 different datasources and then macros which did a refresh on only one of the ranges based on which button is clicked.
All database ranges define the same area in the sheet.
Who is supposed to program this?
Sounds like very poor database design. Why don’t you define all these things in the database?
May be.
Still, is it somehow possible to change the source of a database range from a macro?
Or some way to dynamically choose the source based on the content in like cell A1.
Without user interaction like in the suggested parameter query, which is actually a good solution for advanced users.
Our employees work with this since many years. Send a form letter for clients and before printing/mailing enter the client unique number. Print to-do-list, and before actually printing, enter the date of the things to do. The demo I linked demonstrates how to enter parameters into a form, store them in a separate table and then use the stored parameters to filter record sets. All this can be set up entirely without any macro code (more conveniently with a tiny little bit of macro code) nor spreadsheets. Spreadsheets are a hell of bad user experience unless all your users are trained to fill out spreadsheets correctly, insert cells, check references and adjust them. Search for errors and fix them.
Everybody can fill out database forms. Everybody can fill out the dialog of a param query.
There you got it for my needs.
Thank you.
Simplified, when the user push the button, I will update the filter table with the value in lets say A1, and the use this table in a join in the generic view which retrieves data from the DB.
Then I can use one database range and one database range source, a view which is filtered by the filter-table.