Parsing Variable string

[Still_Learning]

Hi,
I'm need to keep the first part of my variable and place the return value
back to the save variable.

Dim RevisedDate as String
RevisedDate = "7/22/2009 3:19:52 PM"

Now i need to parse this to have.
RevisedDate = "7/22/2009"
I need help on the parseing part.

Thanks

 

[Rick Rothstein]

I'm not entirely clear on the structure of your code, but I think you want
the Format for what you want to do. For example,

DateOnly = Format(CDate(RevisedDate), "m/dd/yyyy")

I'm not sure why you are storing your date/time values in a String variable
though. If you used a Date variable, you wouldn't need the CDate call (note
the # signs instead of the quote marks)...

Dim RevisedDate As Date
RevisedDate = #7/22/2009 3:19:52 PM#
DateOnly = Format(RevisedDate, "m/dd/yyyy")

 

[Still_Learning]

Thank you, Rick
This solved my parsing problem.
I changed revisedDate to be a date instead of a string.
What I was doing with revisedDate was placing it in a footer so when any one
click on the button - Macro it would place a uniform footer across all paper
work.

The only thing I’m working on now is making it update the revised date in
the footer any time some save changed data.
Do you know of a way to set something up (maybe a private sub or function or
something else) that would active when someone save excel with changes.

 

[Rick Rothstein]

The workbook has a BeforeSave event that is triggered before a save
operation takes place... you can put code in there to be executed before the
save happens. To get to this procedure area easily, right click the XL icon
immediately to the left of the File item on Excel's menu bar and select View
Code from the popup menu that appears. This will take you to the code window
for the workbook... click the drop down on the left side at the top of the
code window and select Workbook from the list, then click the drop down on
the right and click on BeforeSave in its list. You will be presented with
the event handler for the BeforeSave event... your code would go there.

 

[Still_Learning]

Thank you, Rick
This solved my revised date saving problem.

Here the code for my BeforeSave event. How do I get it to select all excel
sheet.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim fsMain
Dim fsFile

Set fsMain = CreateObject("Scripting.FileSystemObject")
Set fsFile = fsMain.GetFile(ActiveWorkbook.Path & "\" &
ActiveWorkbook.Name)

With ActiveWorkbook.ActiveSheet
revisedDate = fsFile.DateLastModified
revisedDate = Format(CDate(revisedDate), "m/dd/yyyy")
End With

With ActiveSheet.PageSetup
.LeftFooter = "&8&F" & Chr(10) & "Revised " & revisedDate
.PrintErrors = xlPrintErrorsDisplayed
End With

End Sub

 

[Rick Rothstein]

You have to loop through them one at a time. Normally, this would be done
something like this...

Dim WS As Worksheet
.....
.....
For Each WS In Worksheets ' note the trailing 's'
'
' Your code goes here... your ActiveSheet
' references would be replaced WS
'
Next
.....
.....

 

[Still_learning]

Just a Question for you lets say I apply this – BeforeSave event to
VBAProject (PERSONAL.XLS). Would this event still be triggered before a save
to all other workbook? If not is there a way to have a Global BeforeSave
event for all workbook?

 

[Patrick Molloy]

see my response to yuor other thread: Saving the revised date for any
workbook.