curiouswebster
asked on
Questions about filling an Excel combo box...
I need to move the settings page for multiple combox boxes away from an Excel spread sheet (Office 2007) and into a distinct Excel file. Right now we have a "Selections" page but I need to replace that with an external data source.
1) How do I move the Selections page to a distinct Excel file, XML file, CSV file, etc?
2) How do I then populate that list from a database?
3) How do I find the place in the Excel Workbook where the a given selection is already referenced by a combo box?
Thanks.
1) How do I move the Selections page to a distinct Excel file, XML file, CSV file, etc?
2) How do I then populate that list from a database?
3) How do I find the place in the Excel Workbook where the a given selection is already referenced by a combo box?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just read:
> assuming that you keep the distinct excel file with the selections sheet open AT ALL TIMES, you can use this vba macro to do the trick.
Does this mean in order for me to reference the external Excel page, that page must always be open in Excel?
This may be a show stopper, forcing me to look for another way to persist the data locally (XML?) and NOT need another instance of Excel open.
Please let me know...
> assuming that you keep the distinct excel file with the selections sheet open AT ALL TIMES, you can use this vba macro to do the trick.
Does this mean in order for me to reference the external Excel page, that page must always be open in Excel?
This may be a show stopper, forcing me to look for another way to persist the data locally (XML?) and NOT need another instance of Excel open.
Please let me know...
I would use the Excel file as the master for the database, instead of updating the Excel file FROM the database. Keep all the necessary values in the Excel file and in the database, link the table to the Excel file.
As far as akoster's comment, I do think you have to have the sheet open for it to work in the combo box, but maybe he can come up with something for you that will prevent that.
As far as akoster's comment, I do think you have to have the sheet open for it to work in the combo box, but maybe he can come up with something for you that will prevent that.
ASKER
> instead of updating the Excel file FROM the database
We use Excel for data entry and have an Oracle database where the value reside, managed by the client. So we need to have the values cascade down from database to Excel.
> link the table to the Excel file.
That's a great idea. Linking the table to Excel.
So, when the user is onlline, the values will be refreshed. But when they are offline, the old values will still be in the same exact spot as when they are online.
Do I understand this correctly?
Thanks again!
We use Excel for data entry and have an Oracle database where the value reside, managed by the client. So we need to have the values cascade down from database to Excel.
> link the table to the Excel file.
That's a great idea. Linking the table to Excel.
So, when the user is onlline, the values will be refreshed. But when they are offline, the old values will still be in the same exact spot as when they are online.
Do I understand this correctly?
Thanks again!
If you use the inner-excel-link as proposed, indeed it will only work when the file is opened. it may be minimised though and the sheet with the selection ranges can be hidden.
It might be best to include a hidden worksheet to each workbook containing dropdown boxes. When necessary, you could write a small macro code to update the selection data when opening this workbook.
It might be best to include a hidden worksheet to each workbook containing dropdown boxes. When necessary, you could write a small macro code to update the selection data when opening this workbook.
At my old job we used Excel for many of our "master" files which we wanted to give the users more control over. They could update the Excel values, then we (Oracle administrators) had routines to pick up the files and load them into Oracle (either when they changed or on a scheduled basis, depending on the need).
So yes, I believe that would work for you, keeping Oracle updated for 'online' work and Excel for 'offline'.
I agree with akoster's idea to update the selection data (also from the "master" Excel document) when opening the workbook with a hidden sheet.
So yes, I believe that would work for you, keeping Oracle updated for 'online' work and Excel for 'offline'.
I agree with akoster's idea to update the selection data (also from the "master" Excel document) when opening the workbook with a hidden sheet.
ASKER
The Oracle database is owned bythe client. We have read permission only. I want to use the Oracle database (when it's available and the user has selected to Work Online) to update Excel. Otherwise, I want the data to get stale in Excel.
Gotcha'. I think you're already getting some ideas on linking Excel to Oracle in another thread, so I'm staying out of that one. ;)
ASKER
Thanks again for the help!
ASKER
I feel sure that I will be able to use an external data source through one of the various methods you've described.
> 2) Can you be more specific - do you want the list to be dynamically connected to the database, or do you just want to copy and paste a list into the Excel sheet?
I do want real-time access to a database which will contain the drop-downs. That's our ultimate goal. But I needed the alternate Excel file as our "offline" solution, needed as a result of the hurricane.
Thanks for all the help!