Solved: Find the last data in the far right corner of the word document

[Shazam]

Hi everyone,

I would like to retrieve the last data on the far right corner of the word document. It should retrieve $139,501. The lines fluctuates daily. How can it be incorporated into the line code below.

Something along these lines.



.Subject = lEnd = oCell2.Range.Sentences.Last.End - 2

[mdmackillop]

I know I shouldn't use Selections but try
Sub LastSum()
Selection.EndKey Unit:=wdStory
Selection.MoveUp Unit:=wdParagraph, Count:=1, Extend:=wdExtend
MsgBox Split(Selection)(UBound(Split(Selection)))
Selection.HomeKey Unit:=wdStory
End Sub

[Shazam]

I know I shouldn't use Selections but try
Sub LastSum()
Selection.EndKey Unit:=wdStory
Selection.MoveUp Unit:=wdParagraph, Count:=1, Extend:=wdExtend
MsgBox Split(Selection)(UBound(Split(Selection)))
Selection.HomeKey Unit:=wdStory
End Sub


Hi mdmackillop thank you for replying. Nice code. How can I put the code onto the Subject Line? Because I would the last data in the word document on the subject bar when emailing.

Something like this.

.Subject = EndKey


Sub Send_Test()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.CreateItem(olMailItem)
With objmail
.To = "Anybody@home.com"
.Subject = EndKey
.Body = ""
.NoAging = True
.Attachments.ActiveWorkbook
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True

End Sub

[mdmackillop]

Just make it a function
With objmail
.To = "Anybody@home.com"
.Subject = LastSum
.Body = ""
.NoAging = True
.Attachments.ActiveWorkbook
.Display
End With
Set objmail = Nothing
Set objol = Nothing
SendKeys "%{s}", True

End Sub

Function LastSum() As String
Selection.EndKey Unit:=wdStory
Selection.MoveUp Unit:=wdParagraph, Count:=1, Extend:=wdExtend
LastSum = Split(Selection)(UBound(Split(Selection)))
Selection.HomeKey Unit:=wdStory
End Function

[Shazam]

Cool!! Thank you mdmackillop (http://vbaexpress.com/forum/member.php?u=87). I need to start reading on this. I have a question have you tried the VSTO language? Is there a big difference with VBA & VSTO?

[fumei]

Without Selection:Function LastSum() As String
Dim r As Range
Set r = ActiveDocument.Paragraphs _
(ActiveDocument.Paragraphs.Count - 1).Range
LastSum = Split(r.Text)(UBound(Split(r.Text)))
End FunctionNote that this gets the second to last paragraph, as there is in fact one more paragraph mark.

Would be nice if you could have the data in a formfield, or bookmark. It would make it MUCH easier.

BTW: very nice use of Split, Malcolm.

[mdmackillop]

Thanks Gerry, I'll get my head round it yet. Re Split, I find it a very useful function for extracting file/folder names and other data.

[Shazam]

I'm trying to concatenate these 2 codes but it does not work.

Any Ideas?

"BackLog: " & Trim(ActiveDocument.Sentences(4).Text)


LastSum



.Subject = "BackLog: " & Trim(ActiveDocument.Sentences(4).Text) & LastSum

[fumei]

Works fine for me. Although I need to point out that you do NOT, repeat NOT, state what "does not work" means.

Please do not do this. We can not read minds. "Does not work" is a totally useless bunch of words.

The code works fine.

It does NOT give the correct figure, true, but that is not the codes fault. I will assume that is what you mean by "not work".

It returns - at least from the sample document you posted:

BackLog: Week Totals .: $139,501

According to your sample doc, that is not the Weekly Totals, it is the Report Totals.

Shrug...what else CAN it give????

LastSum returns $139,501 from Report Totals, which is paragraph 5.

You are appending with Sentences(4).Text.

Maybe that is your problem...maybe not...you do not say.

All you say is "does not work".

Please do not use "does not work". It means nothing.

[fumei]

If you want to keep the number together, then perhaps use the same number - for both Sentence, and the range used to get the last figure.Function LastSum2(paraNum As Long)
Dim r As Range
Set r = ActiveDocument.Paragraphs(paraNum).Range
LastSum2 = Split(r.Text)(UBound(Split(r.Text)))
End Function


Sub tryMe()
Dim j As Long
j = 4
MsgBox "BackLog: " & Trim(ActiveDocument.Sentences(j).Text) _
& LastSum2(j)
End SubNow Sentence number, and the paragraph number will match.

4 is hard coded, but you could make that as an input.

Using 4 it returns "BackLog: Week Totals .: $49,779"

Using 5 it returns "BackLog: Report Totals .: $139,501"

[Shazam]

Please do not use "does not work". It means nothing.

Will Do.



If you want to keep the number together, then perhaps use the same number - for both Sentence, and the range used to get the last figure.[vba]Function LastSum2(paraNum As Long)
Dim r As Range
Set r = ActiveDocument.Paragraphs(paraNum).Range
LastSum2 = Split(r.Text)(UBound(Split(r.Text)))
End Function

Thank You very much.

[fumei]

You are welcome. I apologize if I came across sounding a bit harsh. I did not mean it to sound that way. Me bad.