Solved

Problems moving Excel files from local drive to server

Posted on 2017-03-30
4
83 Views
Last Modified: 2017-04-17
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.
0
Comment
Question by:CompProbSolv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
JesterToo earned 500 total points
ID: 42072135
Since there are so many workbooks I doubt if you want to stick 1-time vba code into each of them only to have to remove it again later.  A simpler approach would be to write a vbscript to do the work.  It could load each of the client workbooks and make the changes and then you're don with it after confirming that they all have been changed correctly.
0
 
LVL 21

Author Comment

by:CompProbSolv
ID: 42072187
I agree that I don't want to put VBA code in each of the file.  My general idea was to write VBA code in a new worksheet that opens each of them and does the unprotect, link, and protect.  Were I a VBA expert, this might be very efficient.  I'm not that proficient, so I'm wondering if there's a better way.  If not, I'll explore further about what it will take to do the VBA work as I have described and weigh the cost of that against paying a regular user to do the work manually.
0
 
LVL 22

Expert Comment

by:JesterToo
ID: 42072198
That sounds like a reasonable approach.  If I find a simple solution I will post it back here... if you haven't yet closed the question.
I'm not VBA-proficient either but I have written several vbscripts to manipulate excel files... just not seen/done a link change.

Good luck!
0
 
LVL 21

Author Closing Comment

by:CompProbSolv
ID: 42095687
Best answer that was received....
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question