create a direcotry using VBA coding

[mls via AccessMonster.com]

Hi,
I want to check and create a directory if that folder doesn't already exist.
When I enter date in my FORM text field I need to check for that folder name
in the path given. If it already exist then the excel report should use that
folder other wise it should create new folder with the date entered. Is this
possible using VBA?

 

[Marshall Barton]

I want to check and create a directory if that folder doesn't already exist.
When I enter date in my FORM text field I need to check for that folder name
in the path given. If it already exist then the excel report should use that
folder other wise it should create new folder with the date entered. Is this
possible using VBA?

Check VBA Help for the Dir function and the MkDir statement.

 

[Daniel Pineault]

You can do this using a combination of the Dir Function and the MkDir
Statement.

Based on this, you can use the function found at
http://www.devhut.net/index.php?lang=en&pid=0000000027#FolderExist to test if
a directory already exists then if it does not, simply create it using the
mkdir statement. Something like

sDir = "C:\Temp\20100119" 'Directory to check for
If FolderExist(sDir)=False Then
MkDir sDir 'Directory does not exist, so create it
End if

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daniel Pineault]

I should add a little percision regarding using the date field from your form
as you may need to format it depending on the formatting of your directory
naming convention. You may wish to do something a little more like

sDir = "C:\Temp\" & Format(Me.DateControl,"yyyy-mmm-dd")
If FolderExist(sDir)=False Then
MkDir sDir 'Directory does not exist, so create it
End if

Where you will change the "C:\Temp\" to whatever your base directory is and
change the "yyyy-mmm-dd" to whichever format you use. Simply lookup Format
in the help for a full list of formatting synthax options.

You should also probably first test to make sure there actually is a value
in the control before all of this. Something along the lines of

If IsNull(Me.DateControl)=False Then
'Place the rest of the code here or perform an Exit Sub
End if

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[mls via AccessMonster.com]

Thanks Daniel,
My date format is always going to be the same as I have used input mask in my
form so user have to enter in that format 01-04-2010. so I don't need to re
format that again

I should add a little percision regarding using the date field from your form
as you may need to format it depending on the formatting of your directory
naming convention. You may wish to do something a little more like

sDir = "C:\Temp\" & Format(Me.DateControl,"yyyy-mmm-dd")
If FolderExist(sDir)=False Then
MkDir sDir 'Directory does not exist, so create it
End if

Where you will change the "C:\Temp\" to whatever your base directory is and
change the "yyyy-mmm-dd" to whichever format you use. Simply lookup Format
in the help for a full list of formatting synthax options.

You should also probably first test to make sure there actually is a value
in the control before all of this. Something along the lines of

If IsNull(Me.DateControl)=False Then
'Place the rest of the code here or perform an Exit Sub
End if

You can do this using a combination of the Dir Function and the MkDir
Statement.

[quoted text clipped - 15 lines]

 

[Stuart McCall]

Hi,
I want to check and create a directory if that folder doesn't already
exist.
When I enter date in my FORM text field I need to check for that folder
name
in the path given. If it already exist then the excel report should use
that
folder other wise it should create new folder with the date entered. Is
this
possible using VBA?

My preferred method of doing this is not to check for the folder's
existence, but to simply ignore the error you would get from the MkDir
statement when it does:

On Error Resume Next
MkDir Me.MyTextField

Then if the folder doesn't already exist, it's created, and if it does
exist, nothing is done.

 

[Bob Quintal]

Thanks Daniel,
My date format is always going to be the same as I have used input
mask in my form so user have to enter in that format 01-04-2010.
so I don't need to re format that again

Wrong answer. dates are stored in Access tables as a double
precision number. Formatting only changes the way the number shows
in the textbox or datasheet view. When you use the date value in
code, ALWAYS reformat.