Macro to Open the Excel Files in the Folder

[webmax]

Hi

I have a Folder Name Jan 2014 in that the There was date wise folder like 01st Jan , 02nd Jan etc.

In that date wise folder i have a excel sheets.

I want a macro which will console all the excel files in the different folders in one and the same excel file save in One Folder Name Called Console.

i am unable to attach the zip file. i am giving folder structure like this C:\Users\Desktop\Jan 2014\01.01.2014

Output should come as C:\Users\Desktop\Console

regards
Shahul

 

[Deepak]

Hope! this would help in regard what you are looking for!!!

Option Explicit

Sub All_Combine()
    Dim oFile As Object 'Scripting.File
    Dim oSubFolder As Object 'Scripting.Folder
    Dim owbk As Workbook, ws As Worksheet, twbk As Worksheet
    Dim s As Long, FSO As Object, OLF As Object, oFolder As Object
    Dim LF As String, crng As Range
   
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Cells.Clear
s = 1
LF = "C:\Users\Desktop\Jan 2014"
Set FSO = CreateObject("Scripting.FileSystemObject")
    Set OLF = FSO.GetFolder(LF)
        Set oSubFolder = OLF.SubFolders
            For Each oSubFolder In OLF.SubFolders
                For Each oFile In oSubFolder.Files
                    Set twbk = ThisWorkbook.ActiveSheet
                    On Error Resume Next
                        Set owbk = Workbooks.Open(oFile)
                            Set ws = owbk.Sheets(1)
                            Set crng = ws.UsedRange
                            crng.Copy twbk.Cells(s, 1)
                            s = s + crng.Rows.Count
                        ws.Parent.Close False
                Next oFile
            Next
Set crng = Nothing
Set FSO = Nothing
ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:="C:\Users\Desktop\Console\Consoled.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[webmax]

Hi

i want the files to save in the folder name Console console not as excel separately

 

[Deepak]

Hi

i want the files to save in the folder name Console console not as excel separately

i didn't get it.

 

[webmax]

Now the excel files are save in the single worksheet.I want these excel file would be save separately as different workbooks in the folder mentioned.

 

[Deepak]

Hi,

This will save all data to a workbook named "Consoled" in folder "Console" on desktop.

ActiveWorkbook.SaveAs Filename:="C:\Users\Desktop\Console\Consoled.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Hope! You haven't tried the whole code or still i failed to understand.

or you wish to gather all files in the folder "Console" along with all combined data in a workbook.

 

[webmax]

yes i wish to gather all files in the folder console without combined data in the folder
For example i have 3 files. the Three files should save in the console folder with different workbook
1) File1.xls
2) File2.xls
3) File3.xls

 

[Deepak]

Ok.You might looking for this.

Option Explicit
Sub All_Combine2()
Dim oFile As Object, oSubFolder As Object
Dim FSO As Object, OLF As Object, oFolder As Object
Dim LF As String, DFol As String
   
LF = "C:\Users\Desktop\Jan 2014"
DFol = "C:\Users\Desktop\Console"
Set FSO = CreateObject("Scripting.FileSystemObject")
    Set OLF = FSO.GetFolder(LF)
        Set oSubFolder = OLF.SubFolders
            For Each oSubFolder In OLF.SubFolders
                FSO.CopyFile oSubFolder & "\*.xlsx", DFol
            Next
Set FSO = Nothing
End Sub

 

[Deepak]

Just do this....


open cmd

'copy paste the following.
cd /d "D:\Dailyreport\Jan2014"

'then paste
for /r %d in (*) do copy "%d" "C:\Users\shahul\Desktop\Test"