Problems moving Excel files from local drive to server
Posted on 2017-03-30
I have a client who has created a system of Excel workbooks (Windows 10, I believe Excel is 2013) that are critical to his company. There is a master workbook with material costs and there are individual workbooks for each client that specify the custom details of their product. The customer sheet pulls pricing information from the master workbook.
The client has these files located on his local computer (in a folder from the root, not through \users). I strongly suggested that we move the files to the server so that it gets backed up regularly and that others can access them. I tried to do this and ran into a couple of issues.
The first issue is that when I opened a customer workbook, it indicated that the link to the source had failed. That made sense as it was no longer on C:. I did a few clicks and was able to link it to the new location on the server. That wasn't too difficult.
The second customer workbook had an additional problem because some of the worksheets within it were protected. I had to manually un-protect each one, then re-establish the link, then re-protect the worksheets. Fairly straightforward and it worked well.
The problem is that the client has about 600 of the customer workbooks and isn't very excited about having to make these one-time changes to every one. I'm looking for suggestions here as to how I can simplify the process.
My first thought was to write VBA code that would identify which sheets are protected, un-protect them, re-link the workbook, then re-protect the sheets. I've done a moderate amount of Excel programming but never these specific functions. I expect that dealing with the protection is fairly straightforward but the re-linking may be more difficult. This is a one-time need so I have to trade off my programming time vs. paying a user to make the changes manually.
Is there a straightforward way I could make these changes to all 600 spreadsheets?
Suggestions would be greatly appreciated.