Solved

Create a folder thru VBA

Posted on 2007-03-28
8
556 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 150 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 50 total points
ID: 18808926
If Len(DirstrPath, vbDirectory)) = 0 Then
    MkDir strPath
End If
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 100 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
Technology Partners: 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 200 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 200 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 150 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

726 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