Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to approach defining where macro looks?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to approach defining where macro looks?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to approach defining where macro looks?
shockley wrote: 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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defining a print area in a macro | Excel Discussion (Misc queries) | |||
Defining cells that macro will change | Excel Worksheet Functions | |||
defining names in a macro | Excel Discussion (Misc queries) | |||
macro associated with shape object, useful approach if can be solv | Excel Programming | |||
Defining Dynamic Ranges in Macro | Excel Programming |