Saving Worksheet/Workbook via CommandButton

[WLMPilot]

I have a spreadsheet that is controlled by a macro in order to control cursor
movement. The spreadsheet will be used by users to enter items & qty in
order to restock supplies.

I have a command button with caption "Save and Close Order". I need two
things from someone on this button.

1) Automatic Filename: I wish to create a filename.xls automatically using
the date the order is placed along with value from B1 (which identifies the
station, ie 2). The format for the filename I need is yy-mmdd. The format
for the entire filename is "yy-mmdd ST2 Order" where the number "2", in this
example, is the value in B1.
I need code to make up this filename when the commandbutton is clicked. I
will add the path for the filename.

2) Also need the code to actually save and close the workbook.

Thanks,
Les

 

[Bob Phillips]

I have a spreadsheet that is controlled by a macro in order to control cursor
movement. The spreadsheet will be used by users to enter items & qty in
order to restock supplies.

I have a command button with caption "Save and Close Order". I need two
things from someone on this button.

1) Automatic Filename: I wish to create a filename.xls automatically using
the date the order is placed along with value from B1 (which identifies the
station, ie 2). The format for the filename I need is yy-mmdd. The format
for the entire filename is "yy-mmdd ST2 Order" where the number "2", in this
example, is the value in B1.
I need code to make up this filename when the commandbutton is clicked. I
will add the path for the filename.

Activeworkbook.SaveAs Filename:=Format(Date,"yyyy-mm-ddd") & " ST" &
Range("B1").Value & " Order"

2) Also need the code to actually save and close the workbook.

As above plus

Activeworkbook.Close

 

[WLMPilot]

Thanks,

How do I verify path exist and create it if it does not? I assume an error
code is involved in this part.

Thanks again,
Les

 

[Bob Phillips]

Just try and create it, top down

On Error Resume Next
MkDir "C:\myDir"
MkDir "C:\myDir\myTest"
'etc
On Error Goto 0

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)