running macros based on cell values

[greegan]

In each report, when opened (each report is a *.csv file), we see the name of the report (without characters or numbers) in A1. A3 will have something similar to the following:
Date Range Reporting On: Jan 04, 2012 00:00 CST - Jan 04, 2012 23:59 CST
This date range is important with all files.
In the workbook Reports Conversion.xlsm I have a worksheet named "Run Reports".
On this worksheet in B1 and B2 I have a droplist with dates which read off a table on the "Tables" worksheet.
The droplist will show dates beginning with Yesterday's (Tables range A4 begins with =TODAY()-1 ) and carries down for a total of eight dates.
These dates will be selected by the User prior to running the reports this macro file is intended for. For the most part the dates selected for the Beginning (B1) and Ending (B2) Dates will be the same, selected as yesterday's. On Mondays we will run the reports separately for Friday and Saturday and then on Tuesday we will run the reports separately for Sunday and Monday. These are normally done manually and if this works correctly I expect Friday, Saturday and Sunday to be run on Mondays.
Each day there is one report we will run from the current week's Sunday to the day before the report is run "Yesterday". ie if we are running Friday's report we run all of them based on Fridays date for both B1 and B2. When we run that one specific report (we currently call report_week) will run it from Sunday's date (B1) to Friday's date (B2). Keep in mind Sunday would be only run the once with Sunday's date selected for both B1 and B2.
What I would like to have is a macro open one attachment at a time from outlook, then checks the name of the report in A1 and then compares the range dates appearing in A3. If they correspond with the name identified in the macro, then run the script I prepared for the report. Then go to the next attachment.
If it needs to open each file separately that would be fine. The files may arrive in different emails, but should arrive in a specific folder so I hope that would help select the email.
IF opening the files from the emails then I'd be find with manually opening each file, having them all opened and then having this part of the macro find the correct file to run.

I can do most everything but the confirming the report name and dates (and grabbing the files from the emails). Could someone help me with this?
I'd very much appreciate this.
Thank you
- g

 

[greegan]

I see 23 views. Do I need to give you more information on what I am needing?

-- g

 

[greegan]

In addition...
I have found I can determine the dates in cell A3 of the open csv file as
=MID(A3, 25, 13) for B1
=MID(A3, 51, 13) for B2

I need to be sure the file name in A1 is correct and then verify the dates as above match the correct cells in my macro workbook.

Thanks

-- g

 

[greegan]

Bump?

 

[greegan]

Bump?

 

[greegan]

I would like to post a new thread but I think as my question is an updated version of the one above I will post it here. Someone I hope will be able to provide me an answer.

I would like my macro to open all csv files in a folder identified by cell A3.
I would then like to have the macro check the values of A1 and A3 and compare these to those values I need to run each report.

Each csv will be used for an individual report. All six files have a default prefixed file name with numeric valued suffixes that cannot be predetermined (prefixed file names are the same for each daily report).

Four of the six files have unique prefixed file names, two of the six are the same and only the numbered suffix will make them unique file names.

For example:
companyname_conversion_report_20120111161513(1).csv
companyname_no_order_report_2012011116.csv
companyname_order_report_20120111162724(1).csv
companyname_performance_report_20120111161645.csv

These to file are similar in name and structure however the values in A3 will determine which report I will use with each file.
companyname_order_performance_report_201201162.csv
companyname_order_performance_report_2012011116.csv

A3 will have something like the following:
Date Range Reporting On: Nov 27, 2011 00:00 CST - Dec 01, 2011 23:59 CST
Where in all but one date range will include the same date. One of the companyname_order_performance_report_* files will have a date range begining on the Sunday prior, up to the date it specifies for the report.


What I would like to have is the macro open all csv files in the folder specified by the user, the macro checks for the report names and dates in each csv file and matches them to the required file title (A1 of each file) then the dates identified in A3.

On a worksheet in my macro file I have the beginning and end dates specified in B1 and B2, respectively.

Maybe it needs to be as simple as listing all 6 file prefixes in one column...
A2
companyname_conversion_report_
companyname_no_order_report_
companyname_order_report_
companyname_performance_report_
companyname_order_performance_report_
companyname_order_performance_report_
With B2 as the Beginning Date and C2 as the End Date

The macro could check to see if for report one A1&A3(using the MID function determining the beginning&end dates) matches what the macro file has in cells A2&B2&C2, and cycle through until the file and data matches, if all six files have been checked and nothing matches I would want a message to pop up asking the user to choose another directory or to cancel the process.

Currently I have the user selecting the file and report and running the macro from there, but I'd like this to be automated by vba.

I hope this explanation is much clearer and someone can assist me with this.
Thank you

-- g

 

[greegan]

What else do I need in my description that will help you help me?

-- g

 

[greegan]

I understand the reports a bit better.
The root name will always remain the same for each of the six reports.
The numeric value following will be 14 characters long, including yyyymmddhhmmss
Could I use a format of "00000000000000" in the file name to allow my macro to pick up the file (open it) without being concerned with the numbers following?
I'm hoping to use this in the Pull UDF mentioned previously and in my other modules.

-- g