Creating a excel file that reads and analyzes other excel files

[B_Jarbs]

Hello, I am trying to create an excel file that will take multiple files and read them and put the requested information into this file. So I do not have any VBA experience but I do have some C++ knowledge. Basically I want the user to click a button that will have them select a folder containing multiple files. I then want it to read the file and take the specific cells I want and place them into the master file. All the files that the information will be collected from is the same template. Also if there is a way to determine if a file is not from that template to reject it with an error message in that specific cell. If I didn't mention I would like the files and file path to be displayed and in the following columns their data. Any insight will be good as I am trying to learn how to do this because it was assigned by my boss.

Thanks.

[Alf]

Probably something like this then?

Please Login or Register  to view this content.

A command like this will write folder and file name in column A and downwards

Please Login or Register  to view this content.

Alf

[B_Jarbs]

Probably something like this then?

Please Login or Register  to view this content.

A command like this will write folder and file name in column A and downwards

Please Login or Register  to view this content.

Alf

Thank you for taking the time to help me on this. This is definitely heading me into the right direction. So as far as the analyzing data goes, each row will be one folder so say I want the data from cell b2 (from the selected folder) and go into say column C of the master file and just so I see the pattern data from b3 to go into column B. (also in what area would they go into the code?). As far as the command to write a folder and file name in column A how would I get it to write the file name in A and the pate in B? And again, where would it be placed in the code? Thanks in advance

[Alf]

Please Login or Register  to view this content.

After the command line

Please Login or Register  to view this content.

Please Login or Register  to view this content.

After the "Workbooks.Open" and before the "wk.Close"

The string "Myfolder" contains the path and "sFile" is the file name of the file the macro opens.

Alf

[B_Jarbs]

Perfect, and what about the analyzing part? how do i get it to read the specific cells from the other files?

[B_Jarbs]

Perfect, and what about the analyzing part? how do i get it to read the specific cells from the other files?

[B_Jarbs]

Probably something like this then?

Sheets("Sheet1").Activate ' if information found in Sheet1
' your code to extract date from opened file
' to wherever you like on target sheet.



Alf

2 questions about this one. First is the sheet 1 referring to the master file or the one I am pulling the information from? And second, I cannot figure out what the comments mean. I have tried so many things in the last 2 hours and I cannot figure out how to add the other documents cell information.

[Alf]

In this case Sheet1 refere to the opened file.

The comments was made to tell you where to put your code that will extract data from the opened file to the target (master file).

As ther is a number of options how to do this I would suggest you upload a file / or a model of the file from where you wish to extract data and a "Master" file showing where the data should be placed.

Alf

[B_Jarbs]

Okay. I just made a template and master file. Keep in mind I am not asking you to fill this entire thing out but just enough so I can get the right Idea. Also, some of the cells have pull down menus. Not sure if that will change anything

[Alf]

cells have pull down menus. Not sure if that will change anything

No that will be no problem, you just get the cell value that is present. What could be a problem is merged cells, as they may look nice but are a paint to work with VBA vise.

As you are "extracting" about 76 values from each template file you should consider how to reduce the number of copying operations to a minimum.

For instance at the end of the "Master" you have to copy the "vertical" values E60:E65 from "Template" and pasting it "horizontaly" to range BT:BY in "Master".

So you need something like

Please Login or Register  to view this content.

I tested a bit of code before you uploaded these two files and this should give you an idea how to proceed.

Please Login or Register  to view this content.

As the "Master" file will be the file containing the macro it will also be known as "ThisWorkbook". The offset command ensure that data are not being "ower written". In example above A2 (in Sheet1, Master file) will have the name of the "Template" file, B2 will have the folder name and C2 will have the cell value from B2 of the "Template" file.

Using the offset command for pasting values is ok if all files have the same number of values, but if a value is missing in a file the next file will have one value in the wrong row.

Say the 3 first file have all the values they should have, the 4th file is missing a value that should go in D5 so when file 5's value is pasted one value goes into D5 when it should go to D6 instead.

Since all the values from one template file should go into one row in the "Master" file you could use a "counter". If you would like to do so you must add a line like this at the start of the macro

Please Login or Register  to view this content.

Then set a start value of i (2 because the master file has headings in row 1)

Please Login or Register  to view this content.

and you also need to increment i after each Template file is "processed" i.e.

Please Login or Register  to view this content.

Then you must change all the paste command to this "formate

Please Login or Register  to view this content.

Alf

[B_Jarbs]

Please Login or Register  to view this content.

This will work for some parts, but what about the parts where I cannot use a range. What would I put instead of range for extracting data from the template?

[B_Jarbs]

Please Login or Register  to view this content.

This will work for some parts, but what about the parts where I cannot use a range. What would I put instead of range for extracting data from the template?

[Alf]

This will work for some parts, but what about the parts where I cannot use a range.

Like you pick certain kind of berries, one by one!

See example

Please Login or Register  to view this content.

Alf

[B_Jarbs]

Thanks, You are helping so much. I will start trying to put all of this together. ne last question... for now, sometimes excel files get mixed into this folder that are not of this template. Is there a way to pick out only the ones from this template and put an error message in the row of the other ones?
Also,

'Dir finds the files in the selected folder, to specify a specific filetype replace xlsx with proper file ending
sFile = Dir(MyFolder & "*.xlsx")

For what ever reason some of the files are saved as .xlsx and some as .xls, it wont let me do both or at least im not doing it right, but if we figure a way to determine if the template is correct I dont think this line would be necessary.

[Alf]

Please Login or Register  to view this content.

Should give you both ".xlsx" and ".xls"

If all the "Template" data sheet has the same name a code like this could be used to filter for the right kind of file.

Please Login or Register  to view this content.

and then you need to add

Please Login or Register  to view this content.

This code will open the file, try to activate the Sheet called "Deckblatt, Cover Sheet" and if this sheet don't excists close the opened file jump down to the line "skipper:"

What you should look into is "how to read cell values into arrays" and "pasting array values back to a range" as this would certainly speed up the time it takes to copy and paste values.

As arrays is not my cup of tea I'm sorry can't help you there.

Alf

[B_Jarbs]

I still cant get anything to copy. I dont know if it matters but the real template file has more then 1 sheet. How would I specify which sheet to use?

Please Login or Register  to view this content.

This has been what I have been testing with no luck.

[Alf]

How would I specify which sheet to use

When you open the file then you can specify what sheet to activate so if data is found on sheet called "MyData" then the command should be

Please Login or Register  to view this content.

Have you tried to step through the code and see if it opends files from the selected folder.

Alf

[B_Jarbs]

It is set as the active sheet.

Have you tried to step through the code and see if it opends files from the selected folder.

I am not sure how to do that. When I run the file the A and B columns fill up with the file name and path, but thats as far as I can get.

[B_Jarbs]

Also in regards to this code,

Please Login or Register  to view this content.

and then you need to add

Please Login or Register  to view this content.

First, does it goes in the

Please Login or Register  to view this content.

Section or does it go somewhere else? and is there a way it can display an error message in the row?

and same with the

Please Login or Register  to view this content.

Does that go after i=i+1?

[Alf]

Well if you get file name and path then macro finds the files in the selected folder.

Have you tried to open one of the files and check what the sheet name is on that file and if this sheet do contain the information you are looking for?

If not what sheet does contain that information? Because this is the sheet name you need to activate in the macro.

Then you did have some "merged" cells in the "Template" file you uploaded and as I said before "merged" cells to cause problem in VBA.

Why don't you record a macro starting from scratch by opening one file and copy values from this file and pasting it to the master file and see if you get any error messages and then you can post this code so it can be trimmed down a bit.

Alf

[B_Jarbs]

Please Login or Register  to view this content.

Okay, it looks like I got it to work, but I need more help with the skipper. I might have it wrong in here because I am only able to read one file from the folder. Can you tell me if it is misplaced or even copy this code and put it where it needs to go? This may be the last thing I need help with

[Alf]

Try this code and see if it works better

But remember this code is set up to open all files in a folder and when a file is opend activate a sheet named "Deckblatt, Cover Sheet" if this sheet name does not excist the file is closed and the skipper line makes macro jump down to "sFile = Dir()" and the "Loop" line brings macro back to "Do While sFile <> "" to test the next file.

So if you have a 100 files in the selcted folder and only one file has a sheet named "Deckblatt, Cover Sheet" then you only get one result!

Perhaps you should check if the sheet name really is "Deckblatt, Cover Sheet" in the files you wish to extract data from?

Please Login or Register  to view this content.

Alf

[B_Jarbs]

Great works well. So I did the entire file and there is a new issue. When loading messages like this keep popping up
error.PNG

It comes up for how ever many files I have. There are about 2 different types of conflicts; list1, Review_Type. I did try renaming them but it does not keep them for the next time the file is loaded. Any Ideas? I want this program to be as smooth as possible as I will not be the only one using it.

[B_Jarbs]

Great works well. So I did the entire file and there is a new issue. When loading messages like this keep popping up
Attachment 436061

It comes up for how ever many files I have. There are about 2 different types of conflicts; list1, Review_Type. I did try renaming them but it does not keep them for the next time the file is loaded. Any Ideas? I want this program to be as smooth as possible as I will not be the only one using it.

[Alf]

You could change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

as I assume you just wish to look at values.

Try this first and we'll see how this works.

Alf

[B_Jarbs]

Perfect. Thanks for all your help. I will let you know if any issues arise in the future. I will also add to your reputation

[Alf]

You are welcome.

Thanks for feedback and rep

Alf

Ps I've never encountered the warning / messag "Review_Type" do you what this is about?

[B_Jarbs]

It went away with the pastevalues. One other thing that i just noticed. So on some templates they do not have as many authors as the one i have given you which means their findings value is on a different line. So I am getting some values in the wrong place. Is there any kind of code or something that can say if there is a number value in this line also put it into this line? for example in some templates F53 is a number value (instead of a string) and i want to either transfer it or duplicate it(transfer would be better) to cell E60. Now those cells are from the template. On the master file it would be column BH transferred to BU

[Alf]

Not excatly sure of what you wish to do

Easy to change macro a bit to chech if cell F53 in the opened file is a number and if so either copy F53 to E60 or set E60 equal to F53.

code

Please Login or Register  to view this content.

place these 3 lines before line

Please Login or Register  to view this content.

But what else are you going to do if F53 is a number, remember you are working with the files full time and I've just had a glance at them so you need to explain in detail what you wish to do.


Alf

[B_Jarbs]

Ok i will try to explain it a little better. While running this program on 15 files i realized that there are 2 different templates. one where the authors range from D40 to D53 and another template that range from D40 to D57. This makes the CLASS FINDINGS (the cells below the reviewers) be put further down the rows. So my question is how to make the cells that should be findings get to findings. im going to show a picture to demonstrate.

Region.png
so these numbers are in the wrong place as you can see. i need to move those to that spot if and only if they are a number and not a string

[Alf]

So lookin at the png upload you can use the code I posted i.e

Please Login or Register  to view this content.

but you need to adjust the cell address (row number) as the png file don't show row numbers. Also a quick question you wish to copy from BT?? BY?? The first value in BY?? is a 2 should that not be 0?

Will have a go at it during the weekend but sorting out unstructured data is not my favourite pastime.

Alf

[B_Jarbs]

Okay so just realized something that is making my head spin. If the value in say F54 is not a string and is in fact a number its not the number i am looking for. I would need the value from E54. Is there a way to say if F54 is a value disregard and instead copy E53?

Also, I showed this to my boss and he asked me to change the way the file directory works. This is what he said "It doesn't let me select a root directory and run through all folders, I had to select a folder that actually has Excel files in it. We would like to select the root for a given project and it automatically runs thru the folders." So is there any way we can do that?

Last but not least the data in the date columns are in the wrong format. date.png
Thanks Again.

[Alf]

Is there a way to say if F54 is a value disregard and instead copy E53?

Yes, if you look at post #29 the I've written a code that checks if a cell is a number and if so copies something else.

date columns are in the wrong format

If this is caused by copying change copy value command to copy all or do it as a two step operation i.e. copy format first and then copy value.

It doesn't let me select a root directory and run through all folders, I had to select a folder that actually has Excel files in it.

Don’t think I understand that but I think you need to get a written specification from you boss on how the macro should work and what the it should do.

Having got that you got three choices as I see.

You either hire a consultant to write the code you need.

Or you register at the commercial forum and buy a sufficient number of points to get somebody interested in solving your problem. Not really sure of the going rate for programmers but I think it is about 50 $ / hour and you project could take 3 – 4 hours.

Or ask you boss to pay for you to take an advanced course in Excel macro writing.

As I feel I can't do anything more about solving your problem I will not be spending more time and effort on this thread. Sorry about that but when working for free it gives you choices you don't have when you get payed to do something.

Alf

[Keebellah]

@alf: I agree for 200% with you. What starts as a simple question results in hours of coding, we learn from it but ...