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

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

"pravin" wrote in message
...
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

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" skrev i meddelelsen
...
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

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


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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

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


"AltaEgo" wrote:

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

"pravin" wrote in message
...
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

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


"Per Jessen" wrote:

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" skrev i meddelelsen
...
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