Hello All:
Please tell me if the following is possible with a macro, if so, please help me:
The following pertain to Excel. I have a folder called Claims and it’s in another folder called Cost Department.
In the Claims folder, I have folders (month folders) that are labeled January 2004 e.t.c. to July 2004, next month, a new folder will be created next month for August 2004.
In the January 2004 folder same as other month folders there are different workbooks. The workbook I am interested in, in all these folders is called Data, and then in a worksheet called Raw Data.
I know that I can record a macro to open all the folders available right now, and open the workbook (only 1 at a time) since all the workbooks in each month have the same name, but the data in them is different (don’t ask me, that's how the person getting the data named the workbooks).
An example of the macro I recorded to open a worksheet in a particular month folder is:
Sub openfolders()
Workbooks.Open Filename:= _
"C:\Documents and Settings\XXX\Desktop\Cost Department\Claims\January 2004\Data.xls"
End Sub
I can't open all the Data worksheet for all the months because excel won't allow it.
Question:
How do I write a macro that will open all the available month folders in the Claims folder. Say at the time I am designing this code the last month folder available is: July 2004. If I am using this code in December, how do I make sure the code opens all the available month folders.
E.g. I would like Excel to open the Cost Department folder in my desktop, then open the Claims folder, then open the January 2004 folder, open Data workbook, open the Raw Data worksheet and then close it (I will be posting another question for searching through the worksheet later).
Then open the February 2004 folder, open the Data workbook, open the Raw Data worksheet and then close it, and do the same for all the other folders available in the Claims folder.
Is it possible?
We presently have a very very manual way of searching through these worksheets, and I was thinking a macro could help out, please help.
Thanks again.
SharonMee
Please tell me if the following is possible with a macro, if so, please help me:
The following pertain to Excel. I have a folder called Claims and it’s in another folder called Cost Department.
In the Claims folder, I have folders (month folders) that are labeled January 2004 e.t.c. to July 2004, next month, a new folder will be created next month for August 2004.
In the January 2004 folder same as other month folders there are different workbooks. The workbook I am interested in, in all these folders is called Data, and then in a worksheet called Raw Data.
I know that I can record a macro to open all the folders available right now, and open the workbook (only 1 at a time) since all the workbooks in each month have the same name, but the data in them is different (don’t ask me, that's how the person getting the data named the workbooks).
An example of the macro I recorded to open a worksheet in a particular month folder is:
Sub openfolders()
Workbooks.Open Filename:= _
"C:\Documents and Settings\XXX\Desktop\Cost Department\Claims\January 2004\Data.xls"
End Sub
I can't open all the Data worksheet for all the months because excel won't allow it.
Question:
How do I write a macro that will open all the available month folders in the Claims folder. Say at the time I am designing this code the last month folder available is: July 2004. If I am using this code in December, how do I make sure the code opens all the available month folders.
E.g. I would like Excel to open the Cost Department folder in my desktop, then open the Claims folder, then open the January 2004 folder, open Data workbook, open the Raw Data worksheet and then close it (I will be posting another question for searching through the worksheet later).
Then open the February 2004 folder, open the Data workbook, open the Raw Data worksheet and then close it, and do the same for all the other folders available in the Claims folder.
Is it possible?
We presently have a very very manual way of searching through these worksheets, and I was thinking a macro could help out, please help.
Thanks again.
SharonMee
