Create a folder thru VBA

Hi: This is the path where folders are created by date. Can any one please tell me if in the upload folder when i save my file and the folder is not exist on todays date then how to i create a new folder with the below format thru VBA code
Thanks.

strPath = "\\usnymel1fs001\new claims\IDT Carmel\upload\" & "PIMS Payment File" & " " & Format(Date, "MMDDYY") & ".xls"

FOLDERS IN UPLOAD DIRECTORY
03-01-07
02-28-07
02-06-07
mustish1Asked:
Who is Participating?
 
jefftwilleyConnect With a Mentor Commented:
strPath = "\\usnymel1fs001\new claims\IDT Carmel\upload\"

if dir (strPath & Format(date(),"mm-dd-yy") = "" then

mkdir strPath & Format(date(),"mm-dd-yy")

end if

strPath = strPath & "PIMS Payment File" & " " & Format(Date, "MMDDYY") & ".xls"
0
 
AtropaConnect With a Mentor Commented:
If Len(Dir(strPath, vbDirectory)) = 0 Then MkDir strPath

That should give you what you need...a
0
 
rawyaConnect With a Mentor Commented:
If Len(DirstrPath, vbDirectory)) = 0 Then
    MkDir strPath
End If
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mustish1Author Commented:
It create a folder but next time when i run the program it still goes into the if condition and try to create the folder with todays date and then gives error:  Path/File Access Error
If Dir(strPath & Format(Date, "mm-dd-yy")) = "" Then
    MkDir strPath & Format(Date, "mm-dd-yy")
End If
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
place this code in a module

Option Explicit
Public Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long

to use this function, simply

call  MakeSureDirectoryPathExists ("\\usnymel1fs001\new claims\IDT Carmel\upload\" & Format(Date,"MM-DD-YY") & "\"

this will automatically create the folder for you

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
oops ,forgot the ")"

call  MakeSureDirectoryPathExists ("\\usnymel1fs001\new claims\IDT Carmel\upload\" & Format(Date,"MM-DD-YY") & "\")

0
 
AtropaConnect With a Mentor Commented:
That should not throw an error.  You are only making the directory if it does not exist.  If it does exist then you move on in your code.  
0
 
jefftwilleyCommented:
Hey M,
You got it?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.