asked on

How to copy 100 excel file content to another 100 files

Hi guys,
I have a difficult questions and I am a newbie to excel.
I have more than 100 excel files to copy and paste to another file and the format of the source files are identical to the file I want to copy and paste to.
How can I do this copy and paste task in one go instead of doing it file by file?! Many thanks!

Cathy

Qlemo

Is the filename the same for source and destination?
Do you want to replace the destination files or append?

In general, you can do that only easily if you copy from one folder to another, without any changes.

Cathy Chow

ASKER

I haven't name my file in the destinations so should I use the identical file names as the source?! Also, I want to append not replace the destination. Thanks again!

Qlemo

For appending you need to open source and destination file one or another way, and for automating the process there has to be a (simple) rule to match bith filenames.
Do you need to do this once or repeatedly (e.g. each month)?

Cathy Chow

ASKER

Thank you for your reply and I have to do it from time to time when I have prices back from suppliers to paste into the destinations.

Qlemo

That sounds like a much more complex job than just to append data unconditionally. You do not want to replace anything in the destination, just append?
For a simple approach, I recommend following:

You drop the new files into a folder only used for exactly this purpose.
You call a PowerShell script checking for all files in that (fixed) folder and appending to the same named file (with or without a constant prefix or suffix) in the (fixed) destination folder.
The script moves files successfully appended to a different folder, e.g. a subfolder "archive".

Would that work for you?

Cathy Chow

ASKER

It sounds a bit complicated for me and it's not your problem, it's just my skill in excel is not good enough. I will try and let know you know how it works for me. Many thanks for taking your time to help me.

Qlemo

You won't need Excel skills, as I or another Expert will have to create the PS script. I'm just asking if the general procedure would be ok for you.

Cathy Chow

ASKER

Yes it shall work for me, thank you very much indeed!

Cathy Chow

ASKER

Hello Qlemo,

Is there any news about your PS script?! Many thanks!!

Qlemo

Sorry for not coming back earlier. Do we really talk about Excel files (.XLS, .XLSX, .XLSM), or are they in fact plain CSV files? Excel really needs automation (accessing each cell to enter data), while CSV files can be processed with "simple" file operations.

Cathy Chow

ASKER

Thank you for your feedback and they are all excel files not CSV files.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cathy Chow

ASKER

Million thanks for your help and will run a task and test it first. In case of any problems, can I come back to you again?! Much appreciated!!