Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default product date stamp file save

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default product date stamp file save

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default product date stamp file save

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default product date stamp file save

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default product date stamp file save

Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
--
Gary's Student


"Robert Loxley" wrote:

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Hi Gary

Have you had an opprtunity to have a look at the code yet?

Thank you

Robert Loxley

"Gary''s Student" wrote:

Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
--
Gary's Student


"Robert Loxley" wrote:

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default product date stamp file save

I apologize.

It is on my "to to" list and I will try to get to it today.

Sorry again.
--
Gary's Student


"Robert Loxley" wrote:

Hi Gary

Have you had an opprtunity to have a look at the code yet?

Thank you

Robert Loxley

"Gary''s Student" wrote:

Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
--
Gary's Student


"Robert Loxley" wrote:

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Thank you and no need to aplolgize!

"Gary''s Student" wrote:

I apologize.

It is on my "to to" list and I will try to get to it today.

Sorry again.
--
Gary's Student


"Robert Loxley" wrote:

Hi Gary

Have you had an opprtunity to have a look at the code yet?

Thank you

Robert Loxley

"Gary''s Student" wrote:

Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
--
Gary's Student


"Robert Loxley" wrote:

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9
Default product date stamp file save

Gary

Any luck yet?

"Robert Loxley" wrote:

Thank you and no need to aplolgize!

"Gary''s Student" wrote:

I apologize.

It is on my "to to" list and I will try to get to it today.

Sorry again.
--
Gary's Student


"Robert Loxley" wrote:

Hi Gary

Have you had an opprtunity to have a look at the code yet?

Thank you

Robert Loxley

"Gary''s Student" wrote:

Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
--
Gary's Student


"Robert Loxley" wrote:

Gary

I found a maro written by Dave Petersen which amongst other functions saves
a defined cell range rather than the whole spreadsheet to a specific file.

if you visit...will see what I mean

http://www.microsoft.com/office/comm...0-80ff66ce0ed5

Would you be able to adapt part of that code to include in your brilliantly
written code to save a cell range from (A1 to H40) Thats the top order form.

which will be saved to top left hand corner of saved order form remembering
that there are 2 other form templates which are further down the spreadsheet.

I would appreciate it if you could help again!

"Robert Loxley" wrote:

Gary

It works a treat. I will let you have the rest of the day off!

I am going to puzzle out the last part of what I was hoping to achieve with
reference to only saving an individual order form rather than all 3 in one go.

Thank you again

"Gary''s Student" wrote:

Sorry, it also failed for me. This works:

Sub loxley()
Dim n As String
Dim r As Range
Set r = Cells(1, 1)
n = r.Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

--
Gary''s Student


"Robert Loxley" wrote:

Thanks Gary

i replaced the macro with your suggestion

following message appeared...

run time error '1004'
method 'range' of object' global failed.

first line of code highlighed in yellow color - thats on the line with the
cell reference

does that mean anything to you?

thank you

"Gary''s Student" wrote:

Lets assume that the customer name is in Cell A1, then:

Sub loxley()
n = Range(A1).Value
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

You can change the A1 to the proper cell.


With regard to your second question, I dont know.

--
Gary's Student


"Robert Loxley" wrote:

Thank you Gary!

Is there any way...

1 - that the customer variable which is available from within my order form
can be used automatically to create the complete file name rather than the
operator completing that part manually?

2 - as there are 3 order forms located vertically per sheet i preferably
would only like to save the 1 order for per save function rather than 3. is
there any way only the cell range for each form can be saved?

Thank you

"Gary''s Student" wrote:

Create a button in the usual way and assign to it the folllowing macro:

Sub loxley()
n = InputBox("Enter customer name: ")
n = n & " " & WorksheetFunction.Text(Now(), "ddd_mmm_yyyy__hh_mm")
ActiveWorkbook.SaveAs Filename:="C:\archive\product A\" & n
End Sub

When the button is pressed, the user will be prompted for the customer name.
The file will then be save.
--
Gary's Student


"Robert Loxley" wrote:

I am hoping with your programming skills, by way of a coded macro, to save an
exel file by operating a button and achieving the following objective
automatically...

name file as...

customer_date_time.xls

and save file to specific path for example c:\archive\product A

---

please note

customer name is variable - which is selected from a drop down list in a
specific cell in an order form.

date & time is real time

The time variable is required and preferable as it is possible for 1
customer to take 2 different products on the same date. One cannot have to
two file names the same!.

For example : customer A_301006.xls (X2) wont work
but customer A_301006_1000.xls and customer A_301006_1005 will save without
conflict

----

Background information...

I have 3 order forms set out vertically on a single spreadsheet covering 1
specific product. There is more than 1 product! (Product A-Z).

All 3 order forms on the Product A spreadsheet are for the same product.

All 3 order forms can be used for different customers selected from a drop
down list in a specific cell located within the actual order form.

The individual orders are only saved to a specific archive product location
folder manually at the moment! (example c:\archive\product A) once customer
has recieved the product.

As all 3 order forms are on 1 spreadsheet I dont want to save all 3
simultaneoulsly,
otherwise I will unnecessarily save duplicate orders! and create
unnecessarily large files.

I would only like to save the respective cells to cover each order form
individually

ie I will require an individual save button located side on to each order
form.

The forms are already cleared by way of a simple macro to continually re use.

Thank you for reading my request

Robert

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Autoname a save as file from a cell in a read only file g4rod Excel Discussion (Misc queries) 2 October 6th 06 01:16 PM
I can not save archive file in excel quocvnf Excel Discussion (Misc queries) 1 April 15th 06 01:50 PM
Automatic Populate Todays Date in Cell when File is Saved. Nello Excel Discussion (Misc queries) 3 April 21st 05 11:08 PM
Excel file modification date GROSNER Excel Discussion (Misc queries) 5 March 4th 05 02:19 AM
Weekly Transaction Processing Ralph Howarth Excel Worksheet Functions 4 January 19th 05 06:37 AM


All times are GMT +1. The time now is 12:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"