CREATE A FOLDER AUTOMATICALLY

[pravin]

1.I wish to create a folder everytime I add a new customer in my customer list
2.For example, when I add a customer using the FORM under DATA, I enter the
customer code (eg ABC,BUZ,CKF...), customer name,customer address.The
customer code is in column A, starting at A2
3.For the new customer,by using a micro ,I want to create a new folder (eg.
"K:\TRIALQUOTATIONS\BUZ") .
4.In future the customer's quotations will be filed in this folder.
5.I had written the following micro, which is giving "run time error 438"

Sub CREATECUSTOMERFOLDER()

ActiveWorkbook.CreateFolder FolderName:="K:\TRIALQUOTATIONS\" &
Sheets("CUSLIST").[A2]
6.Please advise suitable changes in the micro.
End Sub
excelforautomatinginvoicing

 

[AltaEgo]

Perhaps the easiest way is to force past the error using On Error Resume
Next

Sub test()
Dim dirname

dirname = "K:\TRIALQUOTATIONS\" & Sheets("CUSLIST").[A2]

On Error Resume Next
MkDir (dirname)
'if the above produces an error (directory exists)
'the error will be ignored,
' that line of code will be skipped
On Error GoTo 0
'get back error control
'display a standard runtime error box

'your code

End Sub

For more on error trapping see:
http://www.cpearson.com/excel/ErrorHandling.htm

 

[Per Jessen]

Hi

Sub test()
Dim DirName

DirName = "K:\TRIALQUOTATIONS\" & Sheets("CUSLIST").Range("A2").Value

Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(DirName) Then
MkDir (DirName)
End If
set fs=Nothing
'your code
End Sub

Regards,
Per

 

[pravin]

Thanks Per Jessen
1.Your suggestion works beautifully.
2.However,it creates folder for the customer with the code in cell A2.
3. The customer codes are entered in cells A2 to A201 . These customers will
be entered as and when we get new customers.
4.To create another folder for another customer in cell A3, I have to change
the Range to "A3" in the micro, and so on.
5.Is it not possible that the range can be automatically changed by the
micro in such a way that
- whenever a new customer code is entered, the micro will create a new
folder corresponding to the new customer code, without disturbing the
existing folders,
created previously.


--
excelforautomatinginvoicing

Hi

Sub test()
Dim DirName

DirName = "K:\TRIALQUOTATIONS\" & Sheets("CUSLIST").Range("A2").Value

Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(DirName) Then
MkDir (DirName)
End If
set fs=Nothing
'your code
End Sub

Regards,
Per

1.I wish to create a folder everytime I add a new customer in my customer
list
2.For example, when I add a customer using the FORM under DATA, I enter
the
customer code (eg ABC,BUZ,CKF...), customer name,customer address.The
customer code is in column A, starting at A2
3.For the new customer,by using a micro ,I want to create a new folder
(eg.
"K:\TRIALQUOTATIONS\BUZ") .
4.In future the customer's quotations will be filed in this folder.
5.I had written the following micro, which is giving "run time error 438"

Sub CREATECUSTOMERFOLDER()

ActiveWorkbook.CreateFolder FolderName:="K:\TRIALQUOTATIONS\" &
Sheets("CUSLIST").[A2]
6.Please advise suitable changes in the micro.
End Sub
excelforautomatinginvoicing

 

[pravin]

Thanks for your suggestion.
It gets over the problem of showing the error message but does not create a
folder.
--
excelforautomatinginvoicing

Perhaps the easiest way is to force past the error using On Error Resume
Next

Sub test()
Dim dirname

dirname = "K:\TRIALQUOTATIONS\" & Sheets("CUSLIST").[A2]

On Error Resume Next
MkDir (dirname)
'if the above produces an error (directory exists)
'the error will be ignored,
' that line of code will be skipped
On Error GoTo 0
'get back error control
'display a standard runtime error box

'your code

End Sub

For more on error trapping see:
http://www.cpearson.com/excel/ErrorHandling.htm

--
Steve

1.I wish to create a folder everytime I add a new customer in my customer
list
2.For example, when I add a customer using the FORM under DATA, I enter
the
customer code (eg ABC,BUZ,CKF...), customer name,customer address.The
customer code is in column A, starting at A2
3.For the new customer,by using a micro ,I want to create a new folder
(eg.
"K:\TRIALQUOTATIONS\BUZ") .
4.In future the customer's quotations will be filed in this folder.
5.I had written the following micro, which is giving "run time error 438"

Sub CREATECUSTOMERFOLDER()

ActiveWorkbook.CreateFolder FolderName:="K:\TRIALQUOTATIONS\" &
Sheets("CUSLIST").[A2]
6.Please advise suitable changes in the micro.
End Sub
excelforautomatinginvoicing

 

[pravin]

Hi Per Jessen,

Thanks.
I have got the program working with a DO loop
which creates the folder, if a new customer is added.
I have also been able to have the path shown in the customer table for the
folder.
Thanks again.
--
excelforautomatinginvoicing

Hi

Sub test()
Dim DirName

DirName = "K:\TRIALQUOTATIONS\" & Sheets("CUSLIST").Range("A2").Value

Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FolderExists(DirName) Then
MkDir (DirName)
End If
set fs=Nothing
'your code
End Sub

Regards,
Per

1.I wish to create a folder everytime I add a new customer in my customer
list
2.For example, when I add a customer using the FORM under DATA, I enter
the
customer code (eg ABC,BUZ,CKF...), customer name,customer address.The
customer code is in column A, starting at A2
3.For the new customer,by using a micro ,I want to create a new folder
(eg.
"K:\TRIALQUOTATIONS\BUZ") .
4.In future the customer's quotations will be filed in this folder.
5.I had written the following micro, which is giving "run time error 438"

Sub CREATECUSTOMERFOLDER()

ActiveWorkbook.CreateFolder FolderName:="K:\TRIALQUOTATIONS\" &
Sheets("CUSLIST").[A2]
6.Please advise suitable changes in the micro.
End Sub
excelforautomatinginvoicing