Locating data from closed worksheets in multiple directories

Hi,

I would like to create a macro which finds data from multiple worksheets and collates them in my Master Worksheet.

I am competent with a lot of functions with Excel, however I have never used Macro's before. I have a little bit of VB knowledge, but only the very basics. I will attempt to explain my situation as clearly as I can.

Please note in your response that I am not familiar with a lot of the programming jargon. I also do not know how to actually create (or is it record?) a macro.

Finally, before I dive into it, I would *prefer* not to have to add code to the closed worksheets, but I can do this if there is no other way!

**********************************************

For every day, a new worksheet is created by my colleagues which summarises the previous days production. My task is to create a worksheet which locates only a few specific cells within the daily summary worksheet and then prints the values into my master worksheet. This allows me to look at a specific couple of pieces of equipment and their specific production. (Production is not a number per se, but rather text. I don't see how this would affect the code, though, as it's simply coppying a cell).

This may appear easy to program, but the twist is the daily summary files are in folders corresponding to their month and year. So Febuary 1st will have its file in a directory different to January 31st and January 1st will be in a different year and month directory to december 31st. The directories differ by name only (C:\\year\month) for example.

I have attached my Master Document. It is called "MFI to well match". In my opinion, it does not contain any sensitive, confidential or innapropriate contents. You can ignore columns K onwards, they're just some simple code I wrote to rearrange some formula. It may serve to open it now so you can follow me. I have not attached a daily summary file since it contains confidential information.

Cells B4 to J4 are pieces of equipment, called MFI's. On any particular day, an MFI records the production of a specific well. This is the information I need to find from each daily summary file. You will notice I have manually entered them from the 1st of January to the 24th. That was time consuming and I hope to never repeat that process!!!

Ideally, would like my macro to do this:
- I select the row corresponding to a date, say row 25, which has already had its macro find and print the required data
- I drag that row down to copy everything to the next days row, row 26
- While both rows are highlighted (or maybe just row 26), pressing a button called "update" in the top will go and find my data for that specific day i.e. updating row 26 with its corresponding daily summary data.

My colleagues have a spreadsheet which does this already for something similar. (The reason I'm not getting them to help me now is because they're all busy of course!!)


This might help you with the solution:

The open master worksheet name: "MFI to well match.xls"
The closed worksheet name: "APF Daily Report 24.01.10.xls" obviously for the 24th of January 2010.
The directory for that file we can call: "C:\Production\2010\January"
All of the cells in column B for "MFI to well match.xls" coresspond to cell $T$65 in the closed worksheets.
All of the cells in column C for "MFI to well match.xls" coresspond to cell $T$66 in the closed worksheets.
All of the cells in column D for "MFI to well match.xls" coresspond to cell $T$67 in the closed worksheets.
All of the cells in column E for "MFI to well match.xls" coresspond to cell $T$68 in the closed worksheets.
All of the cells in column F for "MFI to well match.xls" coresspond to cell $T$69 in the closed worksheets.
All of the cells in column G for "MFI to well match.xls" coresspond to cell $T$70 in the closed worksheets.
All of the cells in column H for "MFI to well match.xls" coresspond to cell $T$71 in the closed worksheets.
All of the cells in column I for "MFI to well match.xls" coresspond to cell $T$73 in the closed worksheets.
All of the cells in column J for "MFI to well match.xls" coresspond to cell $T$64 in the closed worksheets.




In advance, thank you so much for taking the time to help. It is very much appreciated. I am finishing work now, but I will call in tomorrow morning to check if any solutions have been found or if there are any further questions.

PS I hope the above wasn't too confusing.

Anyone have any ideas?

Leith, thank you so much for your reply.

The actual directories for the daily closed worksheets are not C:\year\month

I did not label them as they actually appear due to the names in the pathway being sensitive to confidentiality.

As such, my question is how should I change the code for a directory such as:

O:\Engineering\M\Prod Actuals\APF Daily Reports/year/month

In this line of code:

Please Login or Register  to view this content.

I have tried:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

but I am afraid I get the error message "APF Daily Report 01.01.10.xls Not Found" when clicking update with row 5 highlighted. A similar message for row 6 with the Jan 2nd file not found.

Thank you again, Leith, much appreciated.

I am unsure of your question exactly, but the format from the original code for the actual filename appears to be correct.

i.e. the file name is "APF Daily Report 01.01.10.xls"

where the date in that name is dd.mm.yy.xls

in the directory

"O:\Engineering\M\Production Actuals\APF Daily Reports\2010\January"

Each month will be spelled out fully in a word i.e. "January", February", "March" so on and so forth.

The date in the A column upon which the file is searched for is in the format dd/mm/yy, as you can see from the file you adapted.

I just tested the macro by purposefully mispelling something in the directory. When i ran the macro, it couldn't find the directory, so at least we know my adaption of your code for the actual directory was correct. It appears to just be a matter of finding the actual file within that directory now?

I hope that answered your question.

I also just realised that the sheet in each closed workbook which the data is contained is is called "Information for Supp Report". It is the first sheet of the workbook anyway.

Not sure if this makes a difference.

Originally Posted by Leith Ross

Hello OilMan,

If the answer to my previous post was yes then change these 2 lines in the macro...

Please Login or Register  to view this content.

Leith, using this adaption the Macro returns the error:

"Reports/2010/January.xls Not Found"

Thanks for your patience with this troublesome little piece of code!

I don't know what's wrong - it's saying it can't find the file when it quite clearly exists. The error messages reproduces the exact name of the file saying it can't find it!

I imagine that it's quite early in the morning in Cali, so I will check back in on Monday morning (your Sunday evening) to see what progress has been made. Unless of course you are online before 5pm my time

Thanks again.

Hi
Save the attached file inside a folder from which you wish to pull data. run the macro Called "Multiple".Now run the macro List_file_path Does it resemble your folder path? If yes there is a third macro (Master). I think it is incomplete. I will complete it if the first 2 are satisfactory
Ravi

Certainly.

Please see the attached file.

As I stated above, the code appears to be locating the correct directory but can not seem to "see" the file within that directory, despite everything being spelt and formatted the same.

Originally Posted by Leith Ross

Hello Oilman,

Yes there is a method to to retrieve data from a closed workbook without opening it. The method uses ADO to open the workbook and read it. There are restrictions with how data is arranged and retrieved when using this method. I can't rewrite the macro using this method because my system has never run correctly with ADO.

As for the second part of question, I can change the code to handle all the rows rather than selecting one at time. I can work on that this evening.

Sure, well let's forget about the ADO then; pressing a red cross isn't terribly onerous!

That would be very handy if you could adapt that macro to update multiple rows simultaneously, as I have a few months worth of data to collect!

Thanks again