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
Cathy ChowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 ChowAuthor Commented:
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"Batchelor", Developer and EE Topic AdvisorCommented:
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)?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Cathy ChowAuthor Commented:
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"Batchelor", Developer and EE Topic AdvisorCommented:
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:
  1. You drop the new files into a folder only used for exactly this purpose.
  2. 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.
  3. The script moves files successfully appended to a different folder, e.g. a subfolder "archive".
Would that work for you?
Cathy ChowAuthor Commented:
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"Batchelor", Developer and EE Topic AdvisorCommented:
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 ChowAuthor Commented:
Yes it shall work for me, thank you very much indeed!
Cathy ChowAuthor Commented:
Hello Qlemo,

Is there any news about your PS script?! Many thanks!!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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 ChowAuthor Commented:
Thank you for your feedback and they are all excel files not CSV files.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The PowerShell script is
cls
set-psdebug -Strict

# This variables need to get changed
$srcPath  = 'C:\Temp\EE\Input'
$dstPath  = 'C:\Temp\EE\Output'
$archPath = 'C:\Temp\EE\Input\archive'
# this will be appended to the file name of src to get dst filename
# because both *may not* be the same if opening in the same Excel instance
$suffix   = '_all'


# static code - no changes required
$srcXL = New-Object -Com Excel.Application
$srcXL.Visible = $True

foreach ($srcFile in get-childitem $srcPath -File)
{
  $dstWB = $null
  $srcWB = $srcXL.Workbooks.Open($srcFile.FullName)
  $dstWB = $srcXL.Workbooks.Open("$dstPath\$($srcFile.BaseName)$suffix$($srcFile.Extension)")
  if (!$dstWB) { continue }
  $srcWS = $srcWB.Sheets.Item(1)
  $dstWS = $dstWB.Sheets.Item(1)
  [void] $srcWS.Range("1:1").Delete()
  $srcWS.UsedRange.Copy($dstWS.Cells($dstWS.Rows.Count,1).End($xlUp).Offset(1,0))
  $srcWB.Close(0)
  $dstWB.Close(1)
  move-item $srcFile.FullName "$archPath\$(srcFile.BaseName).$(get-date -f 'yyyy-MM-dd')$($srcFile.Extension)"
}

$srcXL.Quit()

Open in new window

It  has been more tricky than expected, so we have to make some more restrictions and definitions.
  • The files to import are in the path set up as $srcPath.
  • The files receiving the new data are in the path according to $dstPath.
  • The destination files are named like the source, but with a suffix according to $suffix.
    E.g. the XLS file you get is named "General Motors Product Updates.xls", then the destination file would be "General Motors Product Updates - Consolidated.xls", and $suffix needs to be set to ' - Consolidated'.
  • If the destination file is found, content is appended and the file moved into the $archPath path with the current date (no time) added to the name.
So you need to make sure the variables at the top are all set up correctly for your purpose, and the files named as stated.
Then start PowerShell, and just run the above code (stored in a .PS1 file) by providing path and script name.
Of course you should start with a single test case only ;-).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cathy ChowAuthor Commented:
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!!
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Yes, please test with a few files first, before closing this question.
Cathy ChowAuthor Commented:
OK will do and keep you posted. Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.