Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create a folder thru VBA

Posted on 2007-03-28
8
Medium Priority
?
578 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:mustish1
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 5

Assisted Solution

by:Atropa
Atropa earned 600 total points
ID: 18808922
If Len(Dir(strPath, vbDirectory)) = 0 Then MkDir strPath

That should give you what you need...a
0
 

Assisted Solution

by:rawya
rawya earned 200 total points
ID: 18808926
If Len(DirstrPath, vbDirectory)) = 0 Then
    MkDir strPath
End If
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 400 total points
ID: 18808931
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mustish1
ID: 18809073
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 18809088
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 18809096
oops ,forgot the ")"

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

0
 
LVL 5

Assisted Solution

by:Atropa
Atropa earned 600 total points
ID: 18809103
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
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18809176
Hey M,
You got it?
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question