Copy Excel Files from Source Folder to New Folder

OzzieD

New Member
Joined
Jan 3, 2017
Messages
4
Hi Guys,

First time poster. Need your help. I've got about 500 excel that are in "C:\Test" Folder. I want to generate a macro that:
  1. loops through all the files in the folder
  2. For each excel file in the folder (1.xlsx,...)
  3. copy all worksheets into a new workbook with the same filename as the source excel in a new folder called "C:\Cleaned"

What im trying to achieve is:
For each excel file in Test folder, i want an identical excel file in Cleaned folder - Copied from the file in test folder and saved in the Cleaned folder with the same name.

The reason why im doing this is because for some reason this is the only way SAP will except the excel dump, else it keeps erroring out - "cannot load file" - the only way the files load is if i copy the source data's worksheets in a new workbook and then re-upload.

Thanks in advance gurus.

Oz
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
If this is a one time thing, create the cleaned folder, go to the original folder, press CTRL+A to select all the files then copy and paste them into the cleaned folder.
 

OzzieD

New Member
Joined
Jan 3, 2017
Messages
4
i wish it were that easy, tried that approach -
along with:
- create new sheets inside the file, copy all the data as values - saved (failed)
- saved as - new file - tried upload, (failed.)
- used a plug-in to strip all external links (failed)
- stripped the excel doc properties (failed)

the only way it would accept it was if i copied all the sheets into a new file, saved it and uploaded.
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
Dang, I missed where you needed to copy into one workbook not just folder. I know how to do it but requires some VBA
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051

ADVERTISEMENT

Are all the existing workbooks formatted with the same column headers? On row 1 and is the data you want on the same sheet name on each workbook?
 

OzzieD

New Member
Joined
Jan 3, 2017
Messages
4
yeah - i figured. I have another angle. I've gotten it down to why my original files wont load vs why when i copy them to a new workbook, they load.

When I load the new files: it creates the file in temp folder and then uploads into the environment.
When I try to load the original files: it gets denied and says "Cannot Upload File"

I believe its because for whatever reason - the original excel files cannot be generated in TEMP folder - which is why the environment is rejecting it. Anyone know how to remove that restriction?
 

Roderick_E

Well-known Member
Joined
Oct 13, 2007
Messages
2,051
This will open all the files in a specific source...

Code:
Sub openMyfile()


    Dim Source As String
    Dim StrFile As String
    Dim newwbk as workbook

Set newwbk = Workbooks.Add


    'do not forget last backslash in source directory.
    Source = "C:\Users\redwards\Desktop\archive\"
    StrFile = Dir(Source)


    Do While Len(StrFile) > 0
        Workbooks.Open Filename:=Source & StrFile
' here is where you would copied the data from the active opened workbook into the new workbook
'newwbk.sheets(1).....blah blah = activeworkbook.sheets(XYZ)....blah blah blah
        StrFile = Dir()
'close the activeworkbook here???
    Loop
End Sub

msgbox "Complete"
 

OzzieD

New Member
Joined
Jan 3, 2017
Messages
4
each workbook could have a maximum of 4 worksheets - its hard to tell if each workbook has all 4 sheets, none-the-less. The macro i recorded was to select all worksheets, copy them by clicking on "create copy of" and move to new workbook.

The following code copies all tabs to a new workbook.

Sub SelectSheets()
Dim myArray() As Variant
Dim i As Integer
Dim j As Integer
j = 0
For i = 1 To Sheets.Count
If Sheets(i).Visible = True Then
ReDim Preserve myArray(j)
myArray(j) = i
j = j + 1
End If
Next i
Sheets(myArray).Select
Sheets(myArray).Copy

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top