Macro for compiling data from folder and..

[premjeetgupta]

Hi Seniors,

I need a macro help to complete following problems.

• I have two folders names are "Coll" & "Metro".
• Coll contains 4 files "EML1 C","EML1 H","EML3 C" & "EML3 H"
• Path for C:\Users\premjeet\Desktop\Coll
• Metro contrains 4 files "EML2 C", "EML2 H", "EML3 C" & "EML3 H".
• Path for C:\Users\premjeet\Desktop\Metro

I want a macro that can compile these 4 files automatically down the line. Headers are same in each files.

Also it is my daily activity so following requirement also if you kindly help.
After compiling all 4 files of each folder.
I am doing certain things to get result for each folders.
I am attaching a Raw file (EML2 H, EML3 H) and a modified file "Metro". There is only 3 attachment allowed so I am missing (EML2 C, and EML3 C).

• I have done few manual work with compiled file like I have deleted all the data of column "AB" except "communication to the NE is down".
• Then i delete all the columns except A, D, E, P, T & AB.
• From "Event Date and Time" I delete latest date.
• Then I delete all the dates from column "Clearing Time" except latest date and data contains "-".
• Then from "Event Date and Time" except latest date I do change all dates with latest date & time format with 12 PM.
• Then from column "Clearing Time" except latest date I do change all the data ("-") & dates with latest date & time format with 11:59:59 PM
• Then in last column I get Down Time. (Clearing Time - Event Time)*1440
• Then I take pivot of column Friendly Name - Down Time.
• In pivot I choose sum of Down Time
• Then I take Availability of Down Time with formula (1-(Down Time / 1440).

I know it is too lengthy but I would be very much thankful for you kind help.

Regards
Premjeet
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Luke M]

Hi Premjeet,

I'd start by taking a look at some of the merging macros/tools Ron has here:
http://www.rondebruin.nl/win/section3.htm

For the long list of post-processing, easiest would be to be bold, record a macro of yourself doing all the actions, and then start editing the macro from there.

 

[premjeetgupta]

Thanks @Luke M Let me try if I will face any challenge will get back to you.

Thanks again.