List out the file names present in the folder using excel macro

Hi All,

Im having n number of files in the folder and all the files are same but having diffrent data and diffrent name.I want to pull the data's from N number of files to master excel file.I m able to pull the data from all the files.Here i want to list out the file names when i select the data from multiple files.

Im having the seperate code for pull the data and list out the file names.But i want to merge the code to perform action at a single shot.Consolidated data base work book having more sheets..

Any one can take a look and help me out.

below is the code for listing file names present in the folder:

Please Login or Register  to view this content.

And below is the code for pull the data from more excel files

Please Login or Register  to view this content.

Thanks ,
Lakshmanan

Can anyone take a look and reslove this query..?

Hi Jaslake,

That code is working fine for me.First off all thanks for the correction and the reply.With the help of above code am able to get the file name for the selected file.

My Actual requirement here is:

Usually we are collecting data's from various projects and placed in one share point folder.By using my macro i need to collect all the relavant datas from he month files of all projects.
Every month we are colletiong the datas and placing in diffrent folder with folder name like Jan,feb..etc..Dec.

Here i have attached four xcel sheets.
data base sheet is a conslidated sheet
Mont,Month2,Month3 ecel sheets are projects sheets.

Fo every quarter and and ever half and annualy we are calculating datas with the help of monthly data's.


With my attached macro am able to consolidate or import datas for the first month data alone.But i need to collect the data all the month data's or if i select 3or 4 month datas means that all should import from the folder to data base sheet.
Please refer my attached macro data base sheet and project sheets.


Please help me to import datas for all the months in a year .....expecting your possitive reply

Thanks,
Lakshmanan

Hi Naveed,

Thanks for the reply.I have attached my whole macro work books here the thread number 7.Please runt the database code and pull the datats from months sheet.That all working fine for me.as per Jaslake advice i have done the sheet name pulling process.Now here the last challange is i need to collect the data's for diffrent month of the year.Like if i want jan month data alone i will jan month project datas in one folder and pul easily.If i want to take 3 or 4 months data means the macro should selct that 4 months data alone.How i achive this by the month refernce.With my attached macro im able to select only one month data alone..


Naveed and Jaslake you ppl are the hope..Pls help e out to complte this activity..Am in the neck point now...


Thanks ppl

Originally Posted by Naveed Raza

Hi Lakshmanan

Try this attached file and let me know

Note: Put all the Month files in one folder & then run the Macro , When Dialog box will open that time SELECT all the files from Down to Up whatever files u want to merged then click on Open ok

do let me know if any further assistance required & i hope this will work fine for u...

Hi Jaslake,

Thank you fo the reply.Here i have attached the data base file.This was ran by one time.for Name shaking only i have Named the files Like Month1,Month2,Month 3.In all the sheets the Project name is indicated as "Project".With my attached macro am able to retrive all the data
and sheet name easily.Here all the sheets like month 1,2 3 all are the same month But diffrent projects (Let us cnsider like Jan month data of the projects).For name name shaking only i have given like mnth1,2,3.But all the sheets of the data base work book we can able to the all the
months from Jan to dec.With my macro am able to get the data for Jan month or only one data can be retirved.Nut our quarter or half or annualy reports we are considering 3 or or 12 months of data.at this point my macro usgae is not worthful.So only am asking help here..



Thanks,
Lakshmanan

if i run the Macro it will collect the datas and paste the datas into January month of the each worksheets of the data base file.But if i select More than one month it shoulb place the datas into the respective month of the worksheets..For your information ..the month are named top of all the sheets in the data base sheet..


Thanks,
Lakshmanan

HI Jaslake,

Here now we have to consider the Month1,Month2,Monh3 Sheet as a Project1,Project2,Project3...In all the sheets under the Project column the Project1(Month1),Project2(Month2) like wise sitting when run the macro.Totally we have 30 Stable projects in my account.
So for all the 12 months we are receving the diffrents datas from same 30 projects only.So only am placing the 3rd column as a project column.any way for 12 months we are going to collect the diffrent datas from same project.So am placing projects as a common column.
If i want to to get the datas from jan month am going to jan month folder and receving data sing this macro.next if i want feb,Mar datas along with jan months means it should collect the datas for all the selected month.Thats is the chalange here.
This is my indian time crossed mid night.But i din get sleep.Eagarly waiting for your reply and help.


Thanks,
Lakshmanan

No Issues jaslake,

Helping others is a great thing.Am just giving idea and getting sleep is not a gud thing and it is not meaningless.You are helping me.Am not die if i not sleep today.Want to give respect ppl.

This is the one to decide my next level of my carrier.I promised to my manager like " I will submit this project report in coming two days"


Thanks,
Lakhmanan

Hi Jaslake,


I have attached My database file with the Projects files.Let us consider this projects files are jan month files .When running macro it collects data perfectly..In the project column of all the sheets having project names after running the macro.This is report for jan Month.if i have all the the 12 months data in 12 different folder means what i have to do.This is the problem here.While data uploading to the master sheet they asked me to put the progress bar to show the progress..

Hoping you are clear...If anything needed pls message me..am waiting

Hi Jaslake,

As per your dvice i have attached my files and output data base sheet..Please ask me if you need anything..i will share you the details.


Thanks,
Lakshmanan

Hi Anyone,

Hi,

Almost,I have done with the macro.Please use that data base sheet and pull the datas from Projects sheets..Here im able to pull the datas for a single month only..That three project reports are for jan month data..Im able to pull with the help of my macro.But i need to pull the datas for Whole year like 12 months or some times i need to pull the datas for month selection basis.
That 3 projects depends on jan month..Like wise all the months am having datas in the same format but diffrents datas.This 3 projects has been placed in one folder.If want to pull the data for Jan month means i browse Jan month folder and pull the data using my macro..Am facing problem when i need to select more than one month or a 12 months of data.And i dont know the logic how to pull the data..


Thanks
Lakshmanan

Hi All,

"Bump no response"


Thanks,
Lakshmanan

Hi alone !

"Bump no attached files ‼‼‼"

Hi Marc L,

I have attached my all files in the Thread reply number 17.Please have a look and help me out.


Thanks,
Lakshmanan

Hi Jaslake,

Please see all the sheets top first row there we have mentioned all months.Now i have attached three projects with datas..that all related to Jan month data..all the month having same templete of the projects.But inside of the project template data only will be changing for every month..like feb,Mar,april...

Steps to be followed:

1.Place all the three prjects workbooks in one folder (Folder name like January).Use the macro present in the Data base work book and pull the data from three projects.It should pull and place the datas in the appropraite cells which mapped in the macro sheet.

2.Copy all the Projects (3 projects) workbooks and place another one folder and name the folder like Feb.Now pull the values using data base macro workbook.Here the datas should go to Under the feb month (Month mentioned in the first row of all the sheets in the data base work book) of all sheets

3.Like wise Mar,april,may...etc.


This is the full requirement.
If you have any query pls ask me..


Problem faced:

whenever am pulling data from folders..it will be going to place under the Jan month of every sheet.This is my problem..


Please help me..


Thanks,
Lashmanan

Originally Posted by jaslake

Hi Lakshmanan

I received your PM...still don't see an attachment that populates more than one month. Do it manually so we can see your output requirements. Attach the Files that you used to create the manual population.

Hi anyone,

Please take look on my query.I have done 90% of my macro work and waiting 10% completion alone.Please help me..Any help appreciated.


Thanks,
Lakshmanan

See post #21 …

Hi Marc L,

I have uploaded the all the attachements in the post #17 and gave the detailed comments in the Post # 25..Please let me know if you people need more information..

Thanks,
Lakshmanan

Why not to have directly explained it in the post #22 instead of a very bad "Bump no response" ?!

Should I remember you this forum is supported by contributors who dedicate part of their time
in an absolutely free way to help other people and answer questions ?

In case of a high priority issue and you can't even wait a bit,
you always have the alternative to afford commercial services or hire a local consultant …

Hi Marc L,

Im not forcing you people.Just asking help..I have done with 90% work.Balance 10% only am waiting....sorry for the inconvienece..Please help me..

Thanks a lot

Hi Marc L,

Im not asking about Progress Bar now.Just pull the data and place the datas into next empty cell of the upcoming months in the Data base sheet.It would be fine even if you provide me the logic or one liner code.I will make as whole.Please help me on the same..


Thanks,
lakshmanan

Sorry, I confused with another of your threads !

But just notice if a guru as jaslake « can't make any sense out of it » and
asked you for a data mod - and seems still waiting ! - I would not do better !

Maybe he is away fishing around the lake !

Closed subject from my side, much ado about nothing …

HI Jaslake,

As per your advice i have updated all the requirements details and the in the thread post # 25.Please help me..You are the only hope for me..And am waiting for your reply for past 2 days.Please ask me if im not clear.Apologize for all the confusions..Please have a look on the #25th reply...


Thanks,
Lakshmanan

Here is your solution, laxmanann.
Data Base1.xlsm

Hi!
Here is v.2. If you mark "replace" all data will be filled from the begining, otherwise they added consequentially.
Data Base1 v2.xlsm

Hi lancer102runs,

Thanks first off all

That macro is working fine as expected except one thing. let us assume here the selected 4 projects workbooks placed in one folder (Folder name is like January).These datas for one month data and which is filling correctly using this macro.Assume if i have same 4 projects But having diffrent datas for month feb and these 4 projects workbooks placed in another one folder(Folder Name is Febrary).So once the Jan month data load is completed im goin to select feb month data from the feb month folder which having like these same projects but diffrent datas for Feb month.Like wise March.april,may,june...till dec..

Detils:
If i select Feb data from feb folders that datas should go to the cells L3 to R3 in the "Defect" Sheet for project1
If i select Feb data from feb folders that datas should go to the cells L4 to R4 in the "Defect" Sheet for project2
If i select Feb data from feb folders that datas should go to the cells L5 to R5 in the "Defect" Sheet for project3
Like wise for All 30 projects..for all the 6 sheets the macro should work..



If i select March data from March folders that datas should go to the cells T3 to Z3 in the "Defect" Sheet for project1
If i select march data from March folders that datas should go to the cells T4 to Z4 in the "Defect" Sheet for project2
If i select march data from March folders that datas should go to the cells T5 to Z5 in the "Defect" Sheet for project3
Like wise for All 30 projects..for all the 6 sheets the macro should work..

Problem faced here: But here the data select 2nd time it is keep on filling down on the JAn month (Months mentioned in the top of the all sheets)


Please check and help me on the same..Eagarly waiting for your next version


Thanks,
Lakshmanan

OK. I anderstand you. Try to do my best

Thanks in advance lancer102rus..

I hope this is the last version of the programm
Data Base1 v3.xlsm

Hi lancer102rus,

Your code is working fine.But still having one problem when pulling the data.

Steps to reproduce:

PLease follow the below the steps and check your end once.

1.Download and place the projects files like project1,2,3 and name the folder like Jan month data..
2.Download the same projects files and place it another folder And name the folder Feb month data.
3.Run the macro and the datas from Jan month folder (All the datas will go and Place under Jan month of all cells in the data base sheet.There is no pbm in it)
4.Dont close and run again and pull the 3 projects details from Feb month folder. (Here only im facing proble.All the feb month datas are sitting under the Same Jan month cells of the data base sheet.But that not i want. When im pulling datas second time or Some other folder the macro should Read all the Previous months rows of cells and place the datas into next available row of the next month In the same rows..till december..This is what i want )


Like wise Mar,Apr,May..Till dec....


Simply,The Next month row cells needs to update When am pulling second time even if select Same month datas from same folder second time or after


Let me know for any questions


Thanks,
Lakshmanan

Hi, laxmanann!
Tell me, please, whata date must be in the "B37" cell in February file? I thought there must be only February. But after step #2 we will have files with January dates (even though they are in February folder). Am i right, or wrong?

Let us assume defect sheet for example:

First Pull for one project:

First pull the datas from one folder and check the D3 to J3.If D3 to J3 cells are null then the macro should place the copied values to D3 to J3.

Second pull for same project (Even if it is same file):

Macro should Check the D3 to J3 Cells.So alredy it is filled with datas.So it should go to the next Feb month rows and check whether the data are present in the L3 to R3.If Null velues present in the cells then place the values here L3:R3.

3rd pull for same project (Even if it is same file):


Macro should Check the T3 to Z3 Cells.So alredy it is filled with datas.So it should go to the next March month rows and check whether the data are present in the T3 to Z3.If Null velues present in the cells then place the values here T3:Z3.

Like wise for all the months and all the projects and all the workbooks the macro should pull and place the values..


Thanks,
Lakshmanan

My code works so:
1) open file from folder
2) read the date in "B37" cell
3) searching the filename in column "Project".
3a) if such name already exists, then we remember this row
3b) if we found nothing, then programm adds row after the last filename and
4) for each sheet in "Data Base" file programm calculates column according to the date from 2)
5) after we found row and column, programm copies data beginning from that cell
6) going to the next file.

Did you tested programm on real datas?

Dear lancer102rus,

Thanks for the helping.I have tested with real datas and it is working fine for me.Is this possible to set the progress bar based on the data load.?



Thanks,
Lakshmanan

Hi lancer102rus,

I have used the code with small update for Design productivity Sheet in the data base sheet.But am gettig error.can you please check and help me out.

Please Login or Register  to view this content.

If anyone there in online..Please have a look and help me out.

Thanks,
Lakshmanan

Hi Zbro,

Thanks for the reply..Post #42 Having one attachement and the datas some of the cells there and that is my output.In the attached macro file has a code for all the sheets present in the macro data base file...(Defect,Effort,Scedule,Review defects,Design productivity,Execution productivity are the sheets present in the data base file.)In the Post#42 Having macro for all the above sheets except "Design productivity".But i have tried to put the same code (logic) for the Design prodcutivity sheet.But Nothing that alone not working out and other sheets related coding working as expected.just need to debug.i tried a lot .But not working.

I just added the below code alone with the existing code.but not working.Pls help me this alone

Please Login or Register  to view this content.

Thats all what i can do
Data Base1 v4.xlsm333

Hi all,

I need a help of comapring values..Please refer the attached sheet in the Post # 53..

In Design Productivity Sheet :

The values will be calculating based on the previous sheels.Here i just want to compare the cells and generate colors of the cells.

Jan Month data no need of color change.When the macro run for the FEB month the "Test Case Preparation Productivity" column (O) should comapre the JAn month "Test Case Preparation Productivity" and produce the color.Like wise for March month data should comapre the Feb month and generate the color.

Colors:
When comparing the Value of the Feb month is Greater than 5% of Jan month it should produce Green color and if it is a less than 5% it should produce the red color..

Same thing for "Execution Productivity" Sheet..


How can i apply the below code model in this work book

Please Login or Register  to view this content.

you don`t need to do this by macro. Conditional formatting can solve your problem.
E.g. select sheet "Design Productivity". Then select range "J3:N31". Create rule for conditional formatting by menu "Home - Styles - Conditional formatting - Create rule". In opened window choose "Use a formula to determine wich cell to format". write formula in input below "=J3<(D3*0.05)" and select red color to format this cells. Press "Apply". Then again create rule to this range with formula "=J3>(D3*0.05)" and green color format. Press "Apply" and "OK".
Those steps you need to do for every month. Example in attach
Data Base1 v4.xlsm

Dear Lancer102rus,

Thanks for the input.But i dont want to Display colors for every cells of the ever month.Just Feb Month need to comapre the Jan month data..Like Cell O3 to O31 Cells compare the I3 to I31..Like wise for U3 to U31 comapres with I3 to I31.Like wise till end.

When Comparing Feb month With Jan month It should procuce the colors..Please do one sample and show me boss..That is really helpful for me..


Thanks in advance
Lakshmanan

And When Click Import button of the MAcro the Browsing window suddenly opening here.Plese see the attached screenshot..

Then, instead of selecting range "J3:N31", just select range "O3:O31" and use formulas "=O3<(I3*0.05)" and "=O3>(I3*0.05)"

Sure Lancer ..I will check once..Please see the 58 Post alone..All is running fine..And that one alone ..need simple step or your suggestion

Need this?
Data Base1 v4.xlsm

Dear Lancer,

Thnaks for the help..This is what exactly i wanted...You have provided me a 4th version.After that i have made so many changes...I dont know where changed the code now for that Automatic screen path off option..Could you pleease help me/tell me the module or which steo i need to change the code..I tried with your latest code with the old one.I could not able to find it out..

Dear Lancer,

Sorry ...i found the code change..sorry for the inconvinence..Thank you buddy..god bless you always

I just deleted last block "With tmpSh ...." in "UserForm_Activate" Sub

Dear lancer ...Thank you thank you so much..hats off before your knowledge..

Hi Jerry,

sorry for the delay and am closing thread as solved now.Thanks everyone.Special thanks to Lancer102rus..