Saving Workbook via Macro
 

I want to use a command button to save and close a workbook when the user is
finished. I need to know:

1) how to check to be sure directory exist and if it does not create it.
(Example Path: C:\My Documents\Orders\ )

2) the code to save using todays date. Example Filename: 07-0204 Station 2
Order
I asked this question before and was given the following "yyyy mm ddd".
This
format gave me the year, month and day -- 2007 02 Sun. I would like
the last
two digits of the year, month and day as numbers -- 07-0204.

3) where to place the path in code in #2 above.
Example: C:\My Documents\Orders\07-0204 Station 2 Order

Thanks,
Les

Saving Workbook via Macro
 

"WLMPilot" wrote in message
...
I want to use a command button to save and close a workbook when the user
is
finished. I need to know:

1) how to check to be sure directory exist and if it does not create it.
(Example Path: C:\My Documents\Orders\ )


Dim aDirs
Dim sDir As String
Dim i As Long

aDirs = Split("C:\My Documents\Orders\", "\")
sDir = aDirs(LBound(aDirs))
On Error Resume Next
For i = LBound(aDirs) + 1 To UBound(aDirs)
sDir = sDir & "\" & aDirs(i)
MkDir sDir
Next i
On Error GoTo 0


2) the code to save using todays date. Example Filename: 07-0204 Station
2
Order
I asked this question before and was given the following "yyyy mm ddd".
This
format gave me the year, month and day -- 2007 02 Sun. I would like
the last
two digits of the year, month and day as numbers -- 07-0204.


Activeworkbook.SaveAs Filename:= Format(Date,"yy-mmdd") & " Station
2.xls"


3) where to place the path in code in #2 above.
Example: C:\My Documents\Orders\07-0204 Station 2 Order


Activeworkbook.SaveAs Filename:= sDir & "\" & Format(Date,"yy-mmdd") & "
Station 2.xls"

Saving Workbook via Macro
 

Hi Bob,

Why not use

Dim aDirs() As String

instead of

Dim aDirs


Isn't it a bit more efficient?

Thanks,

Antonio


"Bob Phillips" wrote:

"WLMPilot" wrote in message
...
I want to use a command button to save and close a workbook when the user
is
finished. I need to know:

1) how to check to be sure directory exist and if it does not create it.
(Example Path: C:\My Documents\Orders\ )


Dim aDirs
Dim sDir As String
Dim i As Long

aDirs = Split("C:\My Documents\Orders\", "\")
sDir = aDirs(LBound(aDirs))
On Error Resume Next
For i = LBound(aDirs) + 1 To UBound(aDirs)
sDir = sDir & "\" & aDirs(i)
MkDir sDir
Next i
On Error GoTo 0


2) the code to save using todays date. Example Filename: 07-0204 Station
2
Order
I asked this question before and was given the following "yyyy mm ddd".
This
format gave me the year, month and day -- 2007 02 Sun. I would like
the last
two digits of the year, month and day as numbers -- 07-0204.


Activeworkbook.SaveAs Filename:= Format(Date,"yy-mmdd") & " Station
2.xls"


3) where to place the path in code in #2 above.
Example: C:\My Documents\Orders\07-0204 Station 2 Order


Activeworkbook.SaveAs Filename:= sDir & "\" & Format(Date,"yy-mmdd") & "
Station 2.xls"

Saving Workbook via Macro
 

It is, and it would heave been better in this case.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Antonio" wrote in message
...
Hi Bob,

Why not use

Dim aDirs() As String

instead of

Dim aDirs


Isn't it a bit more efficient?

Thanks,

Antonio


"Bob Phillips" wrote:

"WLMPilot" wrote in message
...
I want to use a command button to save and close a workbook when the
user
is
finished. I need to know:

1) how to check to be sure directory exist and if it does not create
it.
(Example Path: C:\My Documents\Orders\ )


Dim aDirs
Dim sDir As String
Dim i As Long

aDirs = Split("C:\My Documents\Orders\", "\")
sDir = aDirs(LBound(aDirs))
On Error Resume Next
For i = LBound(aDirs) + 1 To UBound(aDirs)
sDir = sDir & "\" & aDirs(i)
MkDir sDir
Next i
On Error GoTo 0


2) the code to save using todays date. Example Filename: 07-0204
Station
2
Order
I asked this question before and was given the following "yyyy mm
ddd".
This
format gave me the year, month and day -- 2007 02 Sun. I would
like
the last
two digits of the year, month and day as numbers -- 07-0204.


Activeworkbook.SaveAs Filename:= Format(Date,"yy-mmdd") & " Station
2.xls"


3) where to place the path in code in #2 above.
Example: C:\My Documents\Orders\07-0204 Station 2 Order


Activeworkbook.SaveAs Filename:= sDir & "\" & Format(Date,"yy-mmdd") & "
Station 2.xls"