?
Solved

Create a folder thru VBA

Posted on 2007-03-28
8
Medium Priority
?
568 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

801 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