Hi
Over many years I've been developing a spreadsheet for quotations which uses a front end 'masterquote.xlsm' file & backend 'quoteprices.xlsx' file.
The front end I update as & when I find new things to add etc. & then copy this file to other users machines. They then use it as a template to create a quote & save them with quote number.xlsx
The back end file is used to store a lot of pricing data.
When the front end is opened the following code opens the back end .xlsx:-
In general this works fine but is reliant on the back end being in a specific location.
There have been occasions where this location is not accessible, either due to the machine being off, being upgraded or working from home as currently.
What i'd like to do is develop this code so that if the location isn't found it asks me to locate the file & saves the new location in this code.
Also, as both front end & back end spreadsheets are password protected, whenever the location of the files changed, i used to manually unprotect each worksheet & use the 'edit links' data connection.
I added a module in the front end to help this using the following code to unprotect all the sheets in both front & back ends to save time:-
But I still need to manually do the 'edit links' process & then run the same code with .protect to reset the protection.
Is it possible to elaborate the first set of code to do the 'edit links' process automatically after unprotecting.
Finally, as I currently have a large number of existing quote files that will be looking in a specific location for the back end prices, i'd like some code that runs through all .xlsm files in a directory that 'posts' this new code into them so that when they are opened in future they automatically know where the new back end prices are ?
I know it sounds complicated but i hope i've managed to get across what i'm trying to achieve.
Thanks in advance for any assistance.
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks