VBA on .csv file to run all files in one folder and give result in 1 sheet

[ALAMZEB]

Hi
We get files in .CSV file and we run attached macro to arrange data (after running you will know how it looks)
There could be 30 .csv files in folder. I have to go one by one , run macros and than copy paste result to new sheet
Can I run macro that will search all files in folders than run macro on all of them and give me result in one file

Please change file extension from .xls to .csv
ALl macros are in file that is attached below

 

[Deepak]

Macro pls.

 

[Hui]

There were no macro's in the attached file
But this should be what your after

Sub Extract_CSV_Files()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim SrcSht As Workbook

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\Users\Huis\Desktop") 'Change directory to suit
i = 0

Set SrcSht = ActiveWorkbook

For Each objFile In objFolder.Files

  If UCase(Right(objFile, 3)) = "CSV" Then
  
  Workbooks.Open fileName:=objFile
  Sheets(1).Copy After:=Workbooks(SrcSht.Name).Sheets(SrcSht.Sheets.Count)
  
  Windows(objFile.Name).Close
  i = i + 1
  End If
Next objFile

MsgBox "Finished processing " + CStr(i) + " files."

End Sub

 

[ALAMZEB]

Sorry Depak & Hui
i thought i have attached macro in file. i will reattach shortly

Hui : thanks for above macro.i was just wondering where should i include my macros in your code above.

 

[Hui]

In a code module in VBA

See attached file:

Don't forget to change the Directory name

 

[ALAMZEB]

Thanks Hui
It works like a charm but I have reattached sheet with macro
I run this macro one by one on each csv file.This macro extracts the info and arrange them into correct format
Can I run my macro to run on all tabs after your macro is run and then gives me result in one tab?
I ran macro in first three sheets just to give you an idea
Sorry this macro is too large

 

[Hui]

See how this goes
I have cleaned up the code and deleted the Skippy and Code sheets
Your code sits inside a separate Subroutine in Module1 a Code Module
You can add a line Call MFG to the existing module at the end if you want to run it straight away

I have renamed the worksheets 1Deal so they don't get processed so you can test it

I can't test it as I don't have the lookup file

Everything sits inside a loop that processes all the worksheets
Checking that it isn't the Master Sheet or 1 1Deal sheet

 

[ALAMZEB]

Hi Hui

if i run MFG Macro only by changing rest of tabs to 1Deal,the MFG Macro doesn't work.
What i was wondering that after your Extract CSV file macro runs, this MFG macros runs on all tabs from sheet and than save result in one tab.


currently i have to run MFG macro on one tab at a time and than copy all individual tabs result to separate file

 

[Hui]

In your previous post you said you had ran it on 3 sheets already
I renamed them, to 1Deal.... etc so they didn't get reformatted again
But it still ran on the sheets Deal....

If you look at the MFG macro it steps through each of the sheets 1 at a time and reformats them the same as the previous macro did

What results, or range, do you want transferred to a Summary sheet?

 

[ALAMZEB]

Hi Hui

when now i am running MFG macro on deal tabs it gives me error on
Range("E20").NumberFormat = "0.00"
Selection.AutoFill Destination:=Range("E20:E121")

about the result, MFG gives results from I19 on every tab (1Deal as example). can it gives result on one sheets of all tabs/deal tabs.

 

[Hui]

Why is it running on 1Deal it shouldn't be ?

The 1Deal sheets were the sheets you had already modified

 

[ALAMZEB]

Hi hui

Its not runnung on 1deal.i gave 1deal.just for final result layout
i triee to run in deal only but it didnt work and its not going through all deal tabs

 

[Hui]

The code I sent in the last file doesn't run the 1Deal worksheets
But runs the Deal Sheet only!

What have you done in between
can you repost your new file?

 

[ALAMZEB]

Hi Hui

I think when you changed MFG macro, it didnt work. i have attached new sheet with original Macro. If you run it on one deal sheet than you will know it works. but when you rewrite macro to rum on all deal tabs, it gave me some kind of error.
Let me know how you go & thanks Hui for doing all this
Much appreciated

 

[Hui]

please try this:

 

[ALAMZEB]

Hi Hui

its giving erorr

" Next without For"

 

[Hui]

The last three lines of the MFG Sub should be

End If
Next
End Sub

 

[ALAMZEB]

Hi Hui

its working now
as you can see in attachemnt that all deal tabs have their final result from column ! to column P.
can we summarise all that info into Master tab ignoring colors part
&
can we do this other way around such as
If ws.Name <> "Master" And UCase(Left(ws.Name, 5)) <> "1DEAL" Then

istead of not looking in 1DEAL, it should look only into 1Deal

 

[Hui]

At the Top of the MFG macro can you change the line:
From:
If ws.Name <> "Master" And UCase(Left(ws.Name, 5)) <> "1DEAL" Then

To:
If ws.Name <> "Master" Then

Then run the MFG Subroutine

Then Post the file here

I cannot run it as I don't have access to the metcash macro.XLSB file

Please highlight what data you want summarised on one of the worksheets

 

[ALAMZEB]

Hi Hui

I have explanied everything on attcahed sheet with example summary
let me know if you need anythig else

 

[Hui]

There is a new Subroutine MakeSummary
Simply run that
Other comments in Red in your text box

 

[ALAMZEB]

Thanks Hui

Comments regarding your enquiry in file

 

[Hui]

see how this goes

 

[ALAMZEB]

Hi Hui

MFG Macro doesnt work now.its giving me error
as
unable to get pivotitem property of the pivot fiels
i have attacjed macro file as well so you can run

Please change extension of macro file from .xls to .xlsb

 

[ALAMZEB]

Hi Hui

I hope you are fine
Do you need anything else from me