Copy Excel Files from Source Folder to New Folder

[OzzieD]

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

 

[Roderick_E]

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]

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]

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]

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]

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]

This will open all the files in a specific source...

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]

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