![]() |
#1
|
|||
|
|||
![]()
GMayor, your Code for this is Great. Thanks again.
GMaxey, I must learn how to step through the code. This is in my book. There is so much to learn! Yes, I have been reading about Range, it is a method and object. I wrote one more macro, and used your oRng ! That part works but I am hung up on the format of DATE. Perhaps you can save me hours and hours. I have a date on the top line of my document that looks like this: CRQ 1234567 Add Contractor None 7/27/2017 9:52:31 AM I use this code to write it to the document: Selection.InsertDateTime DateTimeFormat:="M/d/yyyy h:mm:ss am/pm", _ InsertAsField:=False, DateLanguage:=wdEnglishUS, CalendarType:= _ wdCalendarWestern, InsertAsFullWidth:=False When I am finished with a piece of work, I want to know how many hours, minutes, and seconds it takes to complete. So I wrote this Macro: Code:
Sub Macro45() ' ' Macro45 Macro ' 'This first part finds the date on the first line of the document and copies it Selection.HomeKey Unit:=wdStory Selection.Find.ClearFormatting With Selection.Find .Text = "/" ‘Finds the Date .Replacement.Text = "" .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Selection.MoveLeft Unit:=wdWord, Count:=2 Selection.EndKey Unit:=wdLine, Extend:=wdExtend Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend Dim oRng As Range Set oRng = Selection.Range oRng.Copy Dim dtDuration As Date Duration = DateDiff("s", oRng, Time) 'This is only the seconds Selection.EndKey Unit:=wdStory Selection.TypeText Text:="It took this long." & Duration End Sub |
#2
|
|||
|
|||
![]()
You should have probably started a new thread. Perhaps Paul (macropod) or one of the others who knows how will move this to a new thread.
You may not need this much functionality but here it is anyway: Code:
Sub Macro45() Dim oRng As Word.Range Set oRng = ActiveDocument.Range With oRng.Find .Text = "[0-9]{1,2}\/" .MatchWildcards = True If .Execute Then oRng.End = oRng.Paragraphs(1).Range.End - 1 If IsDate(oRng.Text) Then ActiveDocument.Range.InsertAfter "It took this long: " & fcnCalcSpanStart_Finish(oRng.Text, Now) End If End If End With lbl_Exit: Exit Sub End Sub Function fcnCalcSpanStart_Finish(oDateStart As Date, oDateNow As Date) 'A VBA Function coded by Greg Maxey, http://gregmaxey.com/word_tips.html, 7/27/2017 Dim lngYear As Long, lngMonth As Long, lngDay As Long, lngHour As Long, lngMinute As Long, lngSecond As Long Dim lngIndex As Long Dim oDateLDPM As Date, oDateLDIM As Date, oAnchorDate As Date Dim dtdHMSCalc As Date Dim strHMS As String Dim arrStringParts() As String If oDateStart > oDateNow Then fcnCalcSpanStart_Finish = "The start date passed much be prior to date now." Exit Function End If 'Calculate complete years passed. If Year(oDateNow) > Year(oDateStart) Then 'A different calendar year. Has one or more complete years passed? If Month(oDateNow) = Month(oDateStart) Then 'Same month in subsequent year. Check day. If Day(oDateNow) >= Day(oDateStart) Then 'Complete year passed lngYear = DateDiff("yyyy", oDateStart, oDateNow) Else lngYear = DateDiff("yyyy", oDateStart, oDateNow) - 1 End If ElseIf Month(oDateNow) > Month(oDateStart) Then 'Complete year passed. lngYear = DateDiff("yyyy", oDateStart, oDateNow) Else lngYear = DateDiff("yyyy", oDateStart, oDateNow) - 1 End If Else 'Obviously no commplete year passed. lngYear = 0 End If 'Calculate full months passed from last full year. lngMonth = (DateDiff("m", DateSerial(Year(oDateStart), Month(oDateStart), 1), _ DateSerial(Year(oDateNow), Month(oDateNow), 1)) + IIf(Day(oDateNow) >= Day(oDateStart), 0, -1)) Mod 12 'Calculate number of days passed from last full month. If Day(oDateNow) >= Day(oDateStart) Then lngDay = Day(oDateNow) - Day(oDateStart) Else 'Calculate for end of month. 'Get date on last day of previous month. oDateLDPM = DateSerial(Year(oDateNow), Month(oDateNow), 0) 'Get date on last day of index month. oDateLDIM = DateSerial(Year(oDateNow), Month(oDateNow) + 1, 0) oAnchorDate = DateSerial(Year(oDateNow), Month(oDateNow) - 1, Day(oDateStart)) If oDateLDIM = oDateNow Then If lngMonth = 11 Then 'Reset month and add a year. lngMonth = 0 lngYear = lngYear + 1 Else lngMonth = lngMonth + 1 End If Else lngDay = DateDiff("d", IIf(oAnchorDate > oDateLDPM, oDateLDPM, oAnchorDate), oDateNow) End If End If 'Calculate hours, minutes and seconds. If TimeValue(oDateStart) > TimeValue(oDateNow) Then lngDay = lngDay - 1 dtdHMSCalc = oDateNow - oDateStart lngHour = Hour(dtdHMSCalc) lngMinute = Minute(dtdHMSCalc) lngSecond = Second(dtdHMSCalc) 'Format returned value. fcnCalcSpanStart_Finish = lngYear & IIf(lngYear = 1, " year, ", " years, ") & lngMonth & IIf(lngMonth = 1, " month, ", _ " months, ") & lngDay & IIf(lngDay = 1, " day", " days") fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, "0 years", "") fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 months", "") fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 days", "") strHMS = lngHour & IIf(lngHour = 1, " hour, ", " hours, ") & lngMinute & IIf(lngMinute = 1, " minute, ", _ " minutes, ") & lngSecond & IIf(lngSecond = 1, " second", " seconds") If fcnCalcSpanStart_Finish <> vbNullString Then fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & ", " & strHMS Else fcnCalcSpanStart_Finish = strHMS End If fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, "0 hours", "") fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 minutes", "") fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", 0 seconds", "") arrStringParts = Split(fcnCalcSpanStart_Finish, ", ") If UBound(arrStringParts) > 0 Then If UBound(arrStringParts) = 1 Then fcnCalcSpanStart_Finish = Replace(fcnCalcSpanStart_Finish, ", ", " and ") Else fcnCalcSpanStart_Finish = vbNullString For lngIndex = 0 To UBound(arrStringParts) Select Case True Case lngIndex <= UBound(arrStringParts) - 2 fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & ", " Case lngIndex <= UBound(arrStringParts) - 1 fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & " and " Case Else fcnCalcSpanStart_Finish = fcnCalcSpanStart_Finish & arrStringParts(lngIndex) & "." End Select Next lngIndex End If End If lbl_Exit: Exit Function End Function |
#3
|
|||
|
|||
![]()
GMaxey, Thanks for your reply. I am at home now. I read enough last night about Range to know you made a range in the first paragraph, that is the first line of the document.
As for the rest, I must go study it. I am learning a lot. |
#4
|
||||
|
||||
![]() Quote:
Split from: https://www.msofficeforums.com/word-...-saturday.html
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
![]()
Omigosh GMaxey, I ask for some help fixing a flat tire on my old Chevrolet and you give me a Rolls Royce! You must have 30 years experience at this!
This works like a dream! |
#6
|
|||
|
|||
![]()
Glad to help.
Actually I set a range to the active document, then looked for something that looks like the start of a date, then moved the end of the found range to the end of its paragraph (not including the mark), then verified it was a date. As for the function. I didn't do all of that on a lark today. I've tinkered with code to return a calculated age based on a birthday but never took it past years, months and days. Today I groomed that code to drill down to seconds. It may appear classy but I sit in a pile of scalp and bloody hair. I am not the whiz kid with dates and math functions. Not quite 30 but almost 20. |
#7
|
||||
|
||||
![]()
Question: Are you getting any time difference from what you'd get with-
Code:
Sub Test() MsgBox Format(ActiveDocument.BuiltInDocumentProperties("Total Editing Time") / 1440, "[d] hh:mm") End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#8
|
|||
|
|||
![]()
Paul,
I can't answer for the OP as I while I thought about that I got lost in trying to keep my old function from returning 1 day + some number of hours, minutes, seconds when the total time span was less than 24 hours. One would think that your one liner would give a more accurate response to the amount of time actually working on a document. |
#9
|
|||
|
|||
![]()
Macropod, I will try that with a Message Box. I have never tried code for a Message Box but I am anxious to try. Actually I intend on starting a new thread concerning combining all my simple Macros with Message Boxes.
With help from this forum, I played with VBA last week a lot. I can do more than two times the work using Macros. Nobody knows about Macros at work. It is early in the morning, everybody is still sleeping and I have lots of yard work I want to do while it is still cool. My green beans are coming up, I have to get some chicken wire fence to protect the plants from the rabbits. I was snaring rabbits but the police told me to stop that. Thanks guys! |
#10
|
||||
|
||||
![]()
The message box was used just to simplify the demo. The more important issue is whether the method (using a Built-In Document Property to get the "Total Editing Time") is just as good as all the circumlocution you're currently using. The output could just as easily be output to the document itself. Not only that, there's also a DOCPROPERTY field that will output the same output wherever you want it to appear, in minutes, without the need for a macro at all!
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
![]()
Macropod, I have worked with your code:
MsgBox Format(ActiveDocument.BuiltInDocumentProperties("T otal Editing Time") / 1440, "[d] hh:mm") It gives about the same number of minutes and seconds. It seems to start as soon as you open a new document. The clock starts ticking as soon as you open a new document. I am convinced it is accurate too. However, with my work, I open a new document and may not start on a piece of work for several minutes or may an hour later. I am having so much fun with the ease of my work using macros and I marvel at the time savings; so I get everything ready to go ahead of time. Everything is set up before I start on a piece of work. I have to balance accuracy and speed, but the speed part is thrilling. Without Macros, one piece of work takes around 30 minutes of fast, intense work. With Macros my fastest time is 8 minutes and 19 seconds! Of course some of these pieces of work require telephone calls and other research to clarify the data; and this research may greatly increase the time. And I have it rigged so that when I get a piece of work and start to work on it, the very first thing that happens is that I copy and paste an identifying Number using a macro that also grabs the Now time That's when the time starts. Nevertheless, if I didn't already have the other code with lots of logic and code already set up I know I would use your code. I have never studied Microsoft Word very much, I learned enough just to be able to write a document with some shapes and arrows. So I am surprised that anything exists such as your (ActiveDocument.BuiltInDocumentProperties("Total Editing Time") / 1440, "[d] hh:mm") But I did recently buy a good book "Word 2016 IN DEPTH" by Faithe Wempen, a QUE Book. I bought it to compliment my studies of VBA. On page 39-43 Document Properties are discussed, including the Statistics Tab. This is all very interesting. I must look this over to see if I might ever want to use anymore of these. |
#12
|
||||
|
||||
![]()
The 'Total Editing Time' time starts from when you open the document until you close it with saved changes. What this means is that you can open & close a document without making edits and the stats don't change. It also means you can open & close a document several times, making & saving edits as you go, and only the open time will be counted.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
![]()
Macropod, I wondered why the time did not change when I ran the code several times in a row! Ha! It must be because I did not change or edit it.
|
#14
|
|||
|
|||
![]()
GMaxey, This calculation between two dates is working great. Others may be interested to see what the output looks like:
It took this long: 1 day, 22 hours, 27 minutes and 30 seconds. Thursday, August 10, 2017 9:48:13 AM But usually, I get something like this: It took this long: 10 minutes and 15 seconds. Thursday, August 10, 2017 9:45:49 AM The date that it subtracts from looks something like this: 8/8/2017 11:20:42 AM |
![]() |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculate time over two dates | Diver | Excel | 3 | 07-22-2017 11:31 PM |
![]() |
aymanharake | Excel | 6 | 02-17-2017 04:22 PM |
Calculate Time in Hours and Percent | pabyford | Excel | 6 | 12-13-2016 12:29 PM |
![]() |
Catherine | Project | 1 | 02-19-2014 11:15 AM |
![]() |
jrasche2003@yahoo.com | Excel | 2 | 02-09-2007 07:10 AM |