Moving around and renaming a massively linked Excel folder
September 11, 2014 3:33 AM Subscribe
At work I have the responsibility for an Excel-based model, which is a nested series of workbooks and folders with many links to each other. We need to be able to rename the master folder, move the folder about, and keep all links still linked to the enclosing folder.
There is no problem with correctly maintaining the links of workbooks within the first level of the model folder, but all the links from workbooks in subfolders don't update to a new location or model name. We have a macro which goes through the folder and changes all links to a new path, but this is time-consuming, and requires another step for users that we would like to eliminate. Basically we need a fool-proof system.
There is no problem with correctly maintaining the links of workbooks within the first level of the model folder, but all the links from workbooks in subfolders don't update to a new location or model name. We have a macro which goes through the folder and changes all links to a new path, but this is time-consuming, and requires another step for users that we would like to eliminate. Basically we need a fool-proof system.
It really sounds like the best course of action would be to recreate it in a database, probably something easy and lightweight like Access.
posted by The Michael The at 4:38 AM on September 11, 2014 [3 favorites]
posted by The Michael The at 4:38 AM on September 11, 2014 [3 favorites]
Ugh. Using Excel like this is pretty much always going to be a nightmare.
The best Excel-only idea I have is, make all hard-coded links relative to a mapped drive, a drive that is mapped to the path to the master folder. Then, when you change the name of the "master folder", or move it, re-map the drive to the new location. On every workstation or laptop of every person who works on this thing.
But that's not going to be painless either. In fact, unless you can push out the drive re-mapping via AD policy or something like that, it may not be viable at all.
posted by thelonius at 4:40 AM on September 11, 2014 [2 favorites]
The best Excel-only idea I have is, make all hard-coded links relative to a mapped drive, a drive that is mapped to the path to the master folder. Then, when you change the name of the "master folder", or move it, re-map the drive to the new location. On every workstation or laptop of every person who works on this thing.
But that's not going to be painless either. In fact, unless you can push out the drive re-mapping via AD policy or something like that, it may not be viable at all.
posted by thelonius at 4:40 AM on September 11, 2014 [2 favorites]
Response by poster: Thanks, The Michael The, I wish I could change it to an Access database but the folder and workbooks have to remain.
posted by hannahlambda at 4:41 AM on September 11, 2014
posted by hannahlambda at 4:41 AM on September 11, 2014
Yeah. I've been here. The answer is either not excel or change your workflow so the whole thing doesn't need to move.
Having said that I'm guessing your Macro is not optimal nor is the way the spreadsheets et al. Are named. I can't really see why the macro should be slow.
posted by JPD at 4:41 AM on September 11, 2014 [2 favorites]
Having said that I'm guessing your Macro is not optimal nor is the way the spreadsheets et al. Are named. I can't really see why the macro should be slow.
posted by JPD at 4:41 AM on September 11, 2014 [2 favorites]
Response by poster: I know Excel is a terrible tool for this, but we are stuck with it, both Excel and the workflow :(
thelonius, is the best way to make links refer to the correct path by using the INDIRECT function, pointed to the address of the correct folder?
posted by hannahlambda at 4:52 AM on September 11, 2014
thelonius, is the best way to make links refer to the correct path by using the INDIRECT function, pointed to the address of the correct folder?
posted by hannahlambda at 4:52 AM on September 11, 2014
Ugh. No. Don't that. Do it in the vb.
posted by JPD at 4:55 AM on September 11, 2014 [1 favorite]
posted by JPD at 4:55 AM on September 11, 2014 [1 favorite]
I was thinking more of a weekend of suffering where you went and converted all the "C:\some folder\some other folder\excel master folder....." links to a mapped drive letter, so that they'd be like "Z:\....", and then map "Z:" to "C:\some folder\some other folder\excel master folder....."
the more I think about this, though, the less I like it
posted by thelonius at 5:08 AM on September 11, 2014
the more I think about this, though, the less I like it
posted by thelonius at 5:08 AM on September 11, 2014
What exactly are you trying to accomplish? You talk about renaming the master folder and "moving the folder about", what for exactly? Wouldn't symbolic links (the mklink command) let users see a different set of folder names to the excel model?
Reading between the lines it sounds like everyone knows the current scheme is broken but is trying to avoid doing anything about it by rearranging the deckchairs. Were I a consultant on this system I would be reading holiday brochures.
posted by epo at 5:39 AM on September 11, 2014
Reading between the lines it sounds like everyone knows the current scheme is broken but is trying to avoid doing anything about it by rearranging the deckchairs. Were I a consultant on this system I would be reading holiday brochures.
posted by epo at 5:39 AM on September 11, 2014
We have a very similar set of linked spreadsheet models.
But, we never ever use links between them, that's bad practice and they won't pass any of our audits if we do that.
The solution we use is to have a sheet which lists named ranges, a source and a target, with a big "Go"* button. So you open the sheets you need to use and press the go button and it will copy all the data from the output of one sheet to the input sheet of another.
Sorry, I know that "Don't do it like that" is not a great answer and you probably don't have the time or inclination (or buy-in) to completely change model structures.
Here is a Stack Overflow question which you have probably seen.
I guess I would use an on file open action to check file location and update using vb as you have done. Maybe a pair of cells somewhere with an expected path and a current path so it only runs if they are different if time is an issue.
Or perhaps a powershell script in the folder itself which can do the job without you needing to open excel. Non are very lovely. Sorry.
*It's a bit more nuanced than that, but ultimately it is a big red button.
posted by Just this guy, y'know at 5:46 AM on September 11, 2014 [2 favorites]
But, we never ever use links between them, that's bad practice and they won't pass any of our audits if we do that.
The solution we use is to have a sheet which lists named ranges, a source and a target, with a big "Go"* button. So you open the sheets you need to use and press the go button and it will copy all the data from the output of one sheet to the input sheet of another.
Sorry, I know that "Don't do it like that" is not a great answer and you probably don't have the time or inclination (or buy-in) to completely change model structures.
Here is a Stack Overflow question which you have probably seen.
I guess I would use an on file open action to check file location and update using vb as you have done. Maybe a pair of cells somewhere with an expected path and a current path so it only runs if they are different if time is an issue.
Or perhaps a powershell script in the folder itself which can do the job without you needing to open excel. Non are very lovely. Sorry.
*It's a bit more nuanced than that, but ultimately it is a big red button.
posted by Just this guy, y'know at 5:46 AM on September 11, 2014 [2 favorites]
The Horror. The Horror
I'm no expert [though I did manage an Excel db that had more than a half million obscenely cross linked entries which probably subtracted years from my life] but, and I ask this to everyone, is there any way you can have Excel be the front-facing interface and then import the data into Access?
posted by vapidave at 7:03 AM on September 11, 2014 [1 favorite]
I'm no expert [though I did manage an Excel db that had more than a half million obscenely cross linked entries which probably subtracted years from my life] but, and I ask this to everyone, is there any way you can have Excel be the front-facing interface and then import the data into Access?
posted by vapidave at 7:03 AM on September 11, 2014 [1 favorite]
...I know Excel is a terrible tool for this, but we are stuck with it ...
a) this probably won't be the last time this is done, right?
b) and every time it is done, there's a risk of it crumbling?
c) and every time it is done, it costs: time, effort, stress, 2nd guessing (like this thread)
Excel is not a database - it's a spreadsheet. This was well-known ten years ago. To insist upon continued use as an RDBMS suggests...that your organization (secretly/subconsciously) wants and needs an RDBMS.
Why they won't take that road is: corporate-bipolar-syndrome mixed with a broken risk-aversion circuit: Leadership is afraid that changing this workflow and it's supporting technology will introduce risk, when - in reality - the status quo is the risk.
Whatever you do today, to solve the problem (again) for the short term, the next steps are clear to me:
- document the status quo costs.
- document the status quo risks.
- identify high, medium, and low cost rdbms roadmaps. identify any continued risk for each roadmap.
- articulate the benefits in terms of risk and cost
- look for a new job, because management fuckin hates this shit. sorry.
or
Instead of a manual rename-and-move, write a script that takes the new name and location as inputs, updates all the worksheets, then executes the 'move'. now you have three problems: a shitty workflow, a shitty data model, *and* a shitty in-house-written low-qual VBA app that will probably introduce latent bugs for lack of exhaustive unit and functional tests.
This scenario and it's outcomes are well-known and utterly predictable because history.
posted by j_curiouser at 8:31 AM on September 11, 2014 [3 favorites]
a) this probably won't be the last time this is done, right?
b) and every time it is done, there's a risk of it crumbling?
c) and every time it is done, it costs: time, effort, stress, 2nd guessing (like this thread)
Excel is not a database - it's a spreadsheet. This was well-known ten years ago. To insist upon continued use as an RDBMS suggests...that your organization (secretly/subconsciously) wants and needs an RDBMS.
Why they won't take that road is: corporate-bipolar-syndrome mixed with a broken risk-aversion circuit: Leadership is afraid that changing this workflow and it's supporting technology will introduce risk, when - in reality - the status quo is the risk.
Whatever you do today, to solve the problem (again) for the short term, the next steps are clear to me:
- document the status quo costs.
- document the status quo risks.
- identify high, medium, and low cost rdbms roadmaps. identify any continued risk for each roadmap.
- articulate the benefits in terms of risk and cost
- look for a new job, because management fuckin hates this shit. sorry.
or
Instead of a manual rename-and-move, write a script that takes the new name and location as inputs, updates all the worksheets, then executes the 'move'. now you have three problems: a shitty workflow, a shitty data model, *and* a shitty in-house-written low-qual VBA app that will probably introduce latent bugs for lack of exhaustive unit and functional tests.
This scenario and it's outcomes are well-known and utterly predictable because history.
posted by j_curiouser at 8:31 AM on September 11, 2014 [3 favorites]
Response by poster: I know those giving DTMFA advice have the best intentions, but this seriously is not the question I was asking, as I've said in two follow-ups.
posted by hannahlambda at 1:25 AM on September 12, 2014
posted by hannahlambda at 1:25 AM on September 12, 2014
not so much dtmfa...more like 'what to expect when you're expecting'.
posted by j_curiouser at 1:47 AM on September 12, 2014
posted by j_curiouser at 1:47 AM on September 12, 2014
So then - if you can't change the workflow - which I imagine also means you can't do a solution like the one suggested by Just this guy, y'know then really what you want to do is make the file as stable as possible and write a better macro for it. That's really your only option.
Stability is often a function of size - which is mostly a function of the number of calculations being made in the file. So find a way to reduce those as much as you can.
posted by JPD at 5:40 AM on September 12, 2014
Stability is often a function of size - which is mostly a function of the number of calculations being made in the file. So find a way to reduce those as much as you can.
posted by JPD at 5:40 AM on September 12, 2014
This thread is closed to new comments.
I'm really hoping someone has a nice solution!
posted by hannahlambda at 3:39 AM on September 11, 2014