AUTOMATIC FILE SAVING

[pravin]

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)

 

[Shane Devenshire]

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

 

[pravin]

1.Thanks for the prompt reply and regret the non clarity in my question.
2.I have a template for making quotations.Each customer has a name code like
- BUZ,CDE,COD , in cell C7
3.Each quotation has a serial number eg. 178 in cell F7
4.When I click a "Save" button, with an assigned micro, the quotation
worksheet must be saved under the "QUOTATIONS" folder in the specific
customer's sub-folder,arranged in serial order of the quotation number. eg.
K:QUOTATIONS\BUZ\178, K:QUOTATIONS\BUZ\179,K:QUOTATIONS\BUZ\180

6.How can I create a micro which will do the above for me.
Thanks

excelforautomatinginvoicing

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)

 

[Shane Devenshire]

take the code I gave you and modify it:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
Cancel = True
End Sub

This code assumes you have folders already in place with the desired names?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

1.Thanks for the prompt reply and regret the non clarity in my question.
2.I have a template for making quotations.Each customer has a name code like
- BUZ,CDE,COD , in cell C7
3.Each quotation has a serial number eg. 178 in cell F7
4.When I click a "Save" button, with an assigned micro, the quotation
worksheet must be saved under the "QUOTATIONS" folder in the specific
customer's sub-folder,arranged in serial order of the quotation number. eg.
K:QUOTATIONS\BUZ\178, K:QUOTATIONS\BUZ\179,K:QUOTATIONS\BUZ\180

6.How can I create a micro which will do the above for me.
Thanks

excelforautomatinginvoicing

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)

 

[pravin]

1.Thanks for your guidance.
2.I made the micro as suggested by you as follows

Sub SAVEFILEQUOTATION()

' Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:\QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
End Sub

I get the error 9
subscript out of range

excelforautomatinginvoicing

take the code I gave you and modify it:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
Cancel = True
End Sub

This code assumes you have folders already in place with the desired names?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

1.Thanks for the prompt reply and regret the non clarity in my question.
2.I have a template for making quotations.Each customer has a name code like
- BUZ,CDE,COD , in cell C7
3.Each quotation has a serial number eg. 178 in cell F7
4.When I click a "Save" button, with an assigned micro, the quotation
worksheet must be saved under the "QUOTATIONS" folder in the specific
customer's sub-folder,arranged in serial order of the quotation number. eg.
K:QUOTATIONS\BUZ\178, K:QUOTATIONS\BUZ\179,K:QUOTATIONS\BUZ\180

6.How can I create a micro which will do the above for me.
Thanks

excelforautomatinginvoicing

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)

 

[pravin]

Dear Shane,

1.Thanks for your help
2."K:" was the drive in the server and I was getting the error 9
3.I changed the micro to C: as follows and it started working.

ActiveWorkbook.SaveAs Filename:="C:\QUOTATIONS\" & Sheets("Sheet1").[C7] &

"\" & Sheets("Sheet1").[F7]

4. Is it possible to save it in the server in "K:" drive
5. I have a customer worksheet in which I enter for each new customer:
"code, name,address" using the FORM option under DATA
6.Can I write a micro which will create a folder for each new customer I add
to the list

--
excelforautomatinginvoicing

1.Thanks for your guidance.
2.I made the micro as suggested by you as follows

Sub SAVEFILEQUOTATION()

' Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:\QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
End Sub

I get the error 9
subscript out of range

excelforautomatinginvoicing

take the code I gave you and modify it:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
Cancel = True
End Sub

This code assumes you have folders already in place with the desired names?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

1.Thanks for the prompt reply and regret the non clarity in my question.
2.I have a template for making quotations.Each customer has a name code like
- BUZ,CDE,COD , in cell C7
3.Each quotation has a serial number eg. 178 in cell F7
4.When I click a "Save" button, with an assigned micro, the quotation
worksheet must be saved under the "QUOTATIONS" folder in the specific
customer's sub-folder,arranged in serial order of the quotation number. eg.
K:QUOTATIONS\BUZ\178, K:QUOTATIONS\BUZ\179,K:QUOTATIONS\BUZ\180

6.How can I create a micro which will do the above for me.
Thanks

excelforautomatinginvoicing


:

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)

 

[pravin]

I am having another problem in connection with printing my excel files.
Whenever I give the print command then a dialoge box appears asking me the
location for saving the xps file.
I understand that this is the property of xps document.
However, as I have created the automatic file saving through a micro I do
not want this automatic saving feature of xps.
Can I do something about it.

excelforautomatinginvoicing

take the code I gave you and modify it:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="K:QUOTATIONS\" & Sheets("Sheet1").[C7] &
"\" & Sheets("Sheet1").[F7]
Cancel = True
End Sub

This code assumes you have folders already in place with the desired names?

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

1.Thanks for the prompt reply and regret the non clarity in my question.
2.I have a template for making quotations.Each customer has a name code like
- BUZ,CDE,COD , in cell C7
3.Each quotation has a serial number eg. 178 in cell F7
4.When I click a "Save" button, with an assigned micro, the quotation
worksheet must be saved under the "QUOTATIONS" folder in the specific
customer's sub-folder,arranged in serial order of the quotation number. eg.
K:QUOTATIONS\BUZ\178, K:QUOTATIONS\BUZ\179,K:QUOTATIONS\BUZ\180

6.How can I create a micro which will do the above for me.
Thanks

excelforautomatinginvoicing

See your other post for a number of questions I have.

Suppose the filename was in cell C5 one could use something like this to
save the file with that name when you clicked the Save toolbar button.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:=Sheets("Sheet1").[C5]
Cancel = True
End Sub

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


:

I generate quiotations from a template and create a code name for each
quotation in cells B7 to F7 as follows.
TEMPLATE CODE XXX
QUOTATION CODE BUZ 7/07/2009 abc 156

Can I save this in a file automatically using a micro in a file having the
code name (BUZ,DEF,..) and the quotation number (156,157,...)