Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving macro from workbook to Personal Macro Workbook | Excel Discussion (Misc queries) | |||
Saving a workbook step in a Macro | Excel Discussion (Misc queries) | |||
Saving Macro to Personal Workbook | Excel Discussion (Misc queries) | |||
Macro file save as, saving sheet not workbook | Excel Programming | |||
Saving a workbook as PDF in Macro | Excel Programming |