Find FileName based on Cell Contents?

[66tigger40]

Hi

I have a spreadsheet that I need to do the following with:

Working down column C find the first Cell - get the value and then find the folder with that name on a particular drive, when it's found open a set file (always the same name in every folder).

When the file is opened copy a value from the first workbook into the first available blank cell in the worksheet.

Then loop through again until we reach a blank in column C

Is this possible?

Thanks

 

[SkipVought]

Hi,

What's the functional requirement?

You seem to have a METHOD in your head, that may or may not be the best approch to solving the functional issue.

As in you other post in this forum, the METHOD that you specify is not necessarily the best method for accomplishing the task.

There are many options. It's better to focus on WHAT needs to be done. Then figure out the HOW.

Skip,
[sub]
[red][/red]
[/sub]

 

[66tigger40]

Hi Skip

Thank you for your response - you are right I do probably have the wrong method in my head. If i may explain in more detail what I'm trying to do you might be able to point me in the correct direction?

I have a master speadsheet that is downloaded from our mainframe system. After some messing around with formats and getting rid of columns etc - I end up with the following:

Company Name;Acct Code; Total Amount

What I need to do is to take the account code, open the correct folder for that account and open the spreadsheet and paste the Total Amount in for that month.

I've inherited the spreadsheets so can't change how they are setup unfortunately - I have about 60 separte folders for each customer all containing the same spreadsheet

The spreadsheet is a month by month running total.

Hope this helps

Thanks greatly

 

[SkipVought]

What is the download method from the maingrame? Is the data from a database like Oracle or DB2 or is it a flat file or is it a REPORT (yuk! I hope not).

In the first two cases, you may be able to use MS Query via Data/Get external data form each of the workbooks to query only the data that is requied for that customer. Alternatively, ADO could be used as well.

If its a REPORT, your options are somewhat restircted.

FYI, in most cases, it is far better to work from a master data source and produce a report based on some criteria, rather than have to locate a report in which to park the results.

I'd consider gathering the scattered reports and combining the data into one report data sheet. Append the incoming mainframe data to the consolidated report data and THEN produce the YTD or MTD to whatever scope is required for each customer, all out of ONE WORKBOOK. It would GREATLY simplify the process AND make the data much more valuable.



Skip,
[sub]
[red][/red]
[/sub]

 

[66tigger40]

Hi Skip

Thank you for the response the master data comes from an exported crystal report - and it is yuk!, that's why I have to mess with it so much.

Your right regarding the master data report the only problem is to the data is too big for excel over 100K rows so unless I do it in access I can't have one workbook - well i don't think i can?

THanks

Nicola

 

[SkipVought]

I'm confused. You previously stated, "I have a master speadsheet that is downloaded from our mainframe system."

Now you're referring to an, "exported crystal report...over 100K rows..."

The "yuk" refers to the report headings (as opposed to table headings) and report format (often folded rows) that make data analysis difficult.

Yes, importing into MS Access is the way to go. But my remarks were focused on the customer reports that you have to find, open and locate the next row. THAT process is backwards, IMHO, for all the reasons previously stated. The location (Access or Excel) of the MASTER and the CONSOLIDATED data is immaterial. Its the PROCESS!

Skip,
[sub]
[red][/red]
[/sub]