rob0923
07-06-2009, 03:16 PM
Hi,
I am writing a program that will create a directory depending on the company name and create three folders named Word, Excel and PDF.
The script seems the be working correctly. It seems to check for an existing directory and do nothing if it has found one, or create one if one of the directories isn't found.
I have found various examples of creating folders in an excel VBA, but was wondering if there was a way to create a parent directory depending on the value in "B1" and create an array to insert the remaing directories, but not sure if it is worth the code because of how small the code is for MkDir
I was also wondering how to write an error code for this section of code
incase the drive is readonly or the value in B1 is null and want to exit the sub.
Thanks on advance.
Sub Createdir()
'Set Directory for Company
Dim NmComp As Excel.Range
Dim TodayDate As String
TodayDate = Format(Date, "mm.dd.yyyy")
Set NmComp = Worksheets("Sheet1").Range("B1")
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate, vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Word", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Word"
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Excel", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Excel"
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\PDF", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\PDF"
End If
End Sub
I am writing a program that will create a directory depending on the company name and create three folders named Word, Excel and PDF.
The script seems the be working correctly. It seems to check for an existing directory and do nothing if it has found one, or create one if one of the directories isn't found.
I have found various examples of creating folders in an excel VBA, but was wondering if there was a way to create a parent directory depending on the value in "B1" and create an array to insert the remaing directories, but not sure if it is worth the code because of how small the code is for MkDir
I was also wondering how to write an error code for this section of code
incase the drive is readonly or the value in B1 is null and want to exit the sub.
Thanks on advance.
Sub Createdir()
'Set Directory for Company
Dim NmComp As Excel.Range
Dim TodayDate As String
TodayDate = Format(Date, "mm.dd.yyyy")
Set NmComp = Worksheets("Sheet1").Range("B1")
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate, vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Word", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Word"
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Excel", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\Excel"
End If
If Dir(ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\PDF", vbDirectory) = "" Then
MkDir ThisWorkbook.Path & "\" & NmComp & "_" & TodayDate & "\PDF"
End If
End Sub