Make Copies of Excel Workbook & Retrieving the Data

[RacingMoose]

Good evening everyone. We have an Excel workbook that many users will be using for a temporary data entry effort. We expect we will need up to 400 copies of our master workbook for data entry. Does anyone know how we can create copies of our master workbook using a specific file naming convention without doing this manually?

The next step for our process will be getting the data from all of these workbooks into 1 workbook. One of our ideas was to link to them from Access, but that would be a big manual effort to link to each of these workbooks. If anyone has any suggestions, it would be greatly appreciated.

Thanks.

 

[SkipVought]

Hi,

Why not one shared workbook on a mutually assessible network?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RacingMoose]

Thanks for the quick feedback. Unfortunately, our security setup won't allow us to do that. These will be numerous individuals across various departments where we're unable to get them access to the same shared network drive and folders.

 

[SkipVought]

I've worked in aerospace defence for over 30 years, so I know all about security. But often there are PUBLIC folders on the network or PUBLIC folders on your mail server. This would be much MUCH less complex.

Otherwise, are you ready to code? What is your background in programming?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

Only way is code I'm afraid

Easiest way would probably be to get the excel files to load to a database table rather than trying to re-combine back into 1 workbook as that is fraught with all kinds of user interface issues like poeple adding/ removing columns etc

In terms of splitting the master workbook up, that is fairly simple - you could easily set up a list of workbook names you want it saved as, iterate over that and perform a "save as", however I would caution against using excel for distributed data entry - you are going to have a headache piecing it all back together...I can pretty much guarantee that!

IMHO it would be easier in the long run to create a simple webform or survey type web-app that would perform this kind of task

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question