I'm at somewhat of a standstill in my current project, and could use a
little help. I have a series of monthly workbooks that contain daily
data. I'm in the process of automating the creation of the daily
worksheets, so that the end-user only has to enter the information.

What I need to do now, is when the month rolls over, save the workbook
as a different file name, and in a folder that doesn't exist until the
file needs to go there. For example, the current workbook is saved
in:

"F:\assistant\SHIPPING\Daily Inv Report\October 2003\October
Inventory.xls"

When November rolls around, I need to save it as:

"F:\assistant\SHIPPING\Daily Inv Report\November 2003\November
Inventory.xls"

But the directory for November (or any month in the future) might not
exist yet. From here, I have two questions:

1. Is it possible to create a new folder using an excel macro?
2. Is it possible to use a string or integer variable as part of the
path name? such as:
"\" & strMonth & " " & strYear & "\"
.... something like that?


Any help regarding this will be greatly appreciated.

Thanks,
Andrea

Andrea,

The MkDir statement creates a new directory. Try something like

MkDir "F:\assistant\SHIPPING\Daily Inv Report\" & _
strMonth & " " & strYear


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Andrea" wrote in message
om...
I'm at somewhat of a standstill in my current project, and could use a
little help. I have a series of monthly workbooks that contain daily
data. I'm in the process of automating the creation of the daily
worksheets, so that the end-user only has to enter the information.

What I need to do now, is when the month rolls over, save the workbook
as a different file name, and in a folder that doesn't exist until the
file needs to go there. For example, the current workbook is saved
in:

"F:\assistant\SHIPPING\Daily Inv Report\October 2003\October
Inventory.xls"

When November rolls around, I need to save it as:

"F:\assistant\SHIPPING\Daily Inv Report\November 2003\November
Inventory.xls"

But the directory for November (or any month in the future) might not
exist yet. From here, I have two questions:

1. Is it possible to create a new folder using an excel macro?
2. Is it possible to use a string or integer variable as part of the
path name? such as:
"\" & strMonth & " " & strYear & "\"
... something like that?


Any help regarding this will be greatly appreciated.

Thanks,
Andrea

Thanks Chip! I'm still learning all of the functionality of VB in
excel, and I didn't know how to create a folder. Thanks for the info!
It is appreciated... believe me!

Andrea

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!