How to approach defining where macro looks?

[davegb]

I have a spreadsheet I publish quarterly for the state.Each quarter, I
create a set of directories to put all of the spreadsheets that I use
to create the final product with. The path to those directories is:
H:\AllDocs\CRSR PIP DD\
Under that directory, I create a directory like this each quarter:
SFY 06 Q1
Within that directory, I create 4 subdirectories called:
Aggregate Raw
Aggregate Interim
Records Base
Records Mod

I receive 2 different kinds of spreadsheets, "Raw" and "Base". My final
product is a combination of a number of each. There are 8 "Base"
sheets, each one with a different name corresponding to a separate
worksheet in the final workbook. This quarter's workbook is "SFY 06 Q1
Statewide". I already have macros, with a lot of help and patience from
the regulars here, that cleanup and reformat the "Base" sheets, each in
a single sheet workbook, then save the modified product into the
corresponding "Mod" directory. Once I've looked them over, I want to
copy the data from each of these workbooks , and paste it into the
corresponding sheet in the "SFY 06 Q1 Statewide" workbook. For example,
I have a Workbook called "Reentries Mod" with a single worksheet,
"Sheet1". I want to copy the data on that sheet and paste it into the
worksheet in "SFY 06 Q1 Statewide" called "Rentries Records". The base
workbook name and the sheet name will always be the same but with "Mod"
in the workbook name, and "Records" in the sheet name in the "SFY 06 Q1
Statewide" workbook.
I'm trying to figure out how to tell the macro which directory it
should find the "Mod" sheets in, and where the "SFY 06 Q1 Statewide"
sheet resides, which is always in the parent directory of the "Records
Mod" directory. But it's name will change each quarter.
Is there a relatively simple way to tell the macro what the new
"Statewide" file name is and where the "Mod" files are without having
to re-enter the entire path every time I do this process? Up to now,
I've been manually modifying the macro code to reflect the name of the
final "Statewide" workbook and to indicate the new paths to the "Mod"
files. I'd like to automate this part of the process. Does anyone have
any suggestions as to how I might do this?
As always, thanks in advance.

 

[shockley]

Your problem is simple, since the name of each path, worksheet, and workbook
changes from quarter to quarter only according to the quarter and the year
to which it corresponds. So you just need a way to tell the macro the
quarter and the year and then create variables for each item: path, workbook
and worksheet. To whit, there are 3 ways of supplying this information:

1) Either parse the CRSR PIP DD directory to find the folder with the
highest year and quarter number or,

2) If you know that you will ALWAYS be doing this work, for example, after
the quarter ends, then you can write an algorithm to determine the quarter
number (1 - 4) and year using the current date as the input. For instance if
the date the macro runs is Jan 10, 2007, then the algorithm would return
year=06 quarter=1 and your variable for the folder name would become SFY 06
Q1. vba has date functions that would make this a simple matter to code.

3) Have the macro present an input box when it starts running asking for the
year and quarter.

Coding these should be simple (less so for the first option), but if you
decide one of these methods would work for you and you need help, let me
(us) know.

HTH,
Shockley



I have a spreadsheet I publish quarterly for the state.Each quarter, I
create a set of directories to put all of the spreadsheets that I use
to create the final product with. The path to those directories is:
H:\AllDocs\CRSR PIP DD\
Under that directory, I create a directory like this each quarter:
SFY 06 Q1
Within that directory, I create 4 subdirectories called:
Aggregate Raw
Aggregate Interim
Records Base
Records Mod

I receive 2 different kinds of spreadsheets, "Raw" and "Base". My final
product is a combination of a number of each. There are 8 "Base"
sheets, each one with a different name corresponding to a separate
worksheet in the final workbook. This quarter's workbook is "SFY 06 Q1
Statewide". I already have macros, with a lot of help and patience from
the regulars here, that cleanup and reformat the "Base" sheets, each in
a single sheet workbook, then save the modified product into the
corresponding "Mod" directory. Once I've looked them over, I want to
copy the data from each of these workbooks , and paste it into the
corresponding sheet in the "SFY 06 Q1 Statewide" workbook. For example,
I have a Workbook called "Reentries Mod" with a single worksheet,
"Sheet1". I want to copy the data on that sheet and paste it into the
worksheet in "SFY 06 Q1 Statewide" called "Rentries Records". The base
workbook name and the sheet name will always be the same but with "Mod"
in the workbook name, and "Records" in the sheet name in the "SFY 06 Q1
Statewide" workbook.
I'm trying to figure out how to tell the macro which directory it
should find the "Mod" sheets in, and where the "SFY 06 Q1 Statewide"
sheet resides, which is always in the parent directory of the "Records
Mod" directory. But it's name will change each quarter.
Is there a relatively simple way to tell the macro what the new
"Statewide" file name is and where the "Mod" files are without having
to re-enter the entire path every time I do this process? Up to now,
I've been manually modifying the macro code to reflect the name of the
final "Statewide" workbook and to indicate the new paths to the "Mod"
files. I'd like to automate this part of the process. Does anyone have
any suggestions as to how I might do this?
As always, thanks in advance.

 

[davegb]

Your problem is simple, since the name of each path, worksheet, and workbook
changes from quarter to quarter only according to the quarter and the year
to which it corresponds. So you just need a way to tell the macro the
quarter and the year and then create variables for each item: path, workbook
and worksheet. To whit, there are 3 ways of supplying this information:

1) Either parse the CRSR PIP DD directory to find the folder with the
highest year and quarter number or,

2) If you know that you will ALWAYS be doing this work, for example, after
the quarter ends, then you can write an algorithm to determine the quarter
number (1 - 4) and year using the current date as the input. For instance if
the date the macro runs is Jan 10, 2007, then the algorithm would return
year=06 quarter=1 and your variable for the folder name would become SFY 06
Q1. vba has date functions that would make this a simple matter to code.

3) Have the macro present an input box when it starts running asking for the
year and quarter.

Coding these should be simple (less so for the first option), but if you
decide one of these methods would work for you and you need help, let me
(us) know.

Thanks for your suggestions. I had thought of the third option, which
would certainly work. I like the first option best, seems more elegant
and would involve doing some new things in VBA I haven't done before.
Tell me if my initial idea for an approach seems reasonable.
I'd create the new folder for the current reporting period (like SFY 06
Q1 for last quarter). I'd get the name of each directory under
H:\AllDocs\CFSR PID DD in turn. I'd parse to get the year (say, 06 in
this case), then set a variable to this value. The loop would go to
each directory and compare the existing value of the variable with the
current one, and replace the existing one if the current one was
larger. Once I had the largest year, I'd repeat the process for all
directories with that year on quarters. Then the macro would create the
appropriate subfolders. Seem reasonable, or is the a better way?