Dynamically Creating Folders

[jordanctc]

I have this save as vba code:

ChDir "I:\Accounting\Inventory Files\2004"
ActiveWorkbook.SaveAs Filename:= _
"I:\Accounting\Inventory Files\" & Format(Date, "yyyy")
"\Inventory " & Format(Date, "m-d-yyyy") & " with Discounts.xls"
FileFormat:= _
xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=False _
, CreateBackup:=False

It works great but I know that it errors when the year would roll ove
and it would look for the 2005 folder. Instead of having tons o
folders for future years, I need a way to check for the right folde
before this and create it if necessary.

Jorda

 

[kkknie]

Code
-------------------
Sub test()

On Error GoTo NOFOLDER
ChDir "I:\Accounting\Inventory Files\2004"
On Error GoTo ErrHnd
MsgBox "Bubba"

'All original code goes here

Exit Sub
NOFOLDER:
If Err.Number = 76 Then
'Code to create a folder
'Code to switch to new folder
Resume Next
End If
Exit Sub

ErrHnd:
'Normal error handler here
End Su

 

[jordanctc]

Okay - I working on putting that in mine and getting all to work righ
(haven't figured out why I need ErrHnd yet) but...

In VBA - how do you create a new folder??

I tried recording a macro where I created one in the save as window bu
the Macro Recorder didn't show any code for when I made the ne
folder...

Thanks,
Jorda

 

[kkknie]

To make a new folder, use:

MkDir "c:\FolderName\NewSubfolder"

The On Error Goto ErrHnd statement is needed since without it, if ther
is any other error in your code, the code will branch to the sectio
that creates a new file (which you don't want to do). Another optio
is On Error Goto 0 which turns off error handling.

 

[Tom Ogilvy]

On Error Resume next
mkdir "I:\Accounting\Inventory Files\" & Format(Date, "yyyy")
On Error Goto 0
' deleted line was doing nothing useful
ActiveWorkbook.SaveAs Filename:= _
"I:\Accounting\Inventory Files\" & Format(Date, "yyyy") & _
"\Inventory " & Format(Date, "m-d-yyyy") & " with Discounts.xls", _
FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Should be all the error handling you need.