product date stamp file save

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
Let’s 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 don’t know.
 
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
 
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

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

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/com...&p=1&tid=e0f94e98-9c02-4853-9d80-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!
 
Sure. I'll take a look a Peterson's code this evening after the Help Center
empties out and update this post tomorrow.
 
Hi Gary

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

Thank you

Robert Loxley
 
Back
Top