I'm trying to understand differences between excel and word macros. Trying to get Last sentence before first table in a document. I got the word macro to run correctly. I tried applying same code to Excel macro and MoveUp fails. I posted both macro below. Can somebody explain????????????????? Word Macro - Works Correctly ----------------------------------------------------------- Sub getlastline() With ActiveDocument Set FirstTable = .Tables(1) Set FirstCell = FirstTable.Rows(1).Cells(1) FirstCell.Select Selection.MoveUp unit:=wdLine, Count:=1 Selection.Move unit:=wdSentence, Count:=-1 Selection.Sentences(1).Select End With End Sub Excel Code - Move Up fails ---------------------------------------------------------------- Sub getlastline() Set WdObj = CreateObject("Word.Application") WdObj.Visible = True Set WDDoc = WdObj.Documents.Open(Filename:="c:\temp\table test.doc") With WDDoc Set FirstTable = .Tables(1) Set FirstCell = FirstTable.Rows(1).Cells(1) FirstCell.Select Selection.MoveUp unit:=wdLine, Count:=1 Selection.Move unit:=wdSentence, Count:=-1 Selection.Sentences(1).Select End With End Sub
Use something like: Dim myrange As Range With ActiveDocument Set myrange = .Range myrange.End = .Tables(1).Range.Start End With myrange.Sentences.Last.Select Set myrange = Selection.Range myrange.End = myrange.Paragraphs(1).Range.End myrange.Select -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Joel" wrote in message news:892882DF-B2AA-4974-96A8-07156E3CB484@microsoft.com... > I'm trying to understand differences between excel and word macros. Trying > to > get Last sentence before first table in a document. I got the word macro > to > run correctly. I tried applying same code to Excel macro and MoveUp > fails. > I posted both macro below. Can somebody explain????????????????? > > Word Macro - Works Correctly > ----------------------------------------------------------- > > Sub getlastline() > > With ActiveDocument > Set FirstTable = .Tables(1) > Set FirstCell = FirstTable.Rows(1).Cells(1) > FirstCell.Select > Selection.MoveUp unit:=wdLine, Count:=1 > > Selection.Move unit:=wdSentence, Count:=-1 > Selection.Sentences(1).Select > End With > > End Sub > > Excel Code - Move Up fails > ---------------------------------------------------------------- > > > Sub getlastline() > > Set WdObj = CreateObject("Word.Application") > WdObj.Visible = True > Set WDDoc = WdObj.Documents.Open(Filename:="c:\temp\table test.doc") > > With WDDoc > Set FirstTable = .Tables(1) > Set FirstCell = FirstTable.Rows(1).Cells(1) > FirstCell.Select > Selection.MoveUp unit:=wdLine, Count:=1 > > Selection.Move unit:=wdSentence, Count:=-1 > Selection.Sentences(1).Select > End With > > End Sub > > >
I have the same problem with you code as mine. It works in word but doesn't work in excel. With youcode it doesn't like the "end". gives error "Arguement not optional". Excel expects for end a parameter and word doesn't require one. I n Excel LastRow = Range("A" & Rows.Count).end(xlup).Row Even if I get rid of the compiling error by doing this WDDoc.Application.myrange.End = .Tables(1).Range.Start The line doesn't execute. "Doug Robbins - Word MVP" wrote: > Use something like: > > Dim myrange As Range > With ActiveDocument > Set myrange = .Range > myrange.End = .Tables(1).Range.Start > End With > myrange.Sentences.Last.Select > Set myrange = Selection.Range > myrange.End = myrange.Paragraphs(1).Range.End > myrange.Select > > > -- > Hope this helps. > > Please reply to the newsgroup unless you wish to avail yourself of my > services on a paid consulting basis. > > Doug Robbins - Word MVP > > "Joel" wrote in message > news:892882DF-B2AA-4974-96A8-07156E3CB484@microsoft.com... > > I'm trying to understand differences between excel and word macros. Trying > > to > > get Last sentence before first table in a document. I got the word macro > > to > > run correctly. I tried applying same code to Excel macro and MoveUp > > fails. > > I posted both macro below. Can somebody explain????????????????? > > > > Word Macro - Works Correctly > > ----------------------------------------------------------- > > > > Sub getlastline() > > > > With ActiveDocument > > Set FirstTable = .Tables(1) > > Set FirstCell = FirstTable.Rows(1).Cells(1) > > FirstCell.Select > > Selection.MoveUp unit:=wdLine, Count:=1 > > > > Selection.Move unit:=wdSentence, Count:=-1 > > Selection.Sentences(1).Select > > End With > > > > End Sub > > > > Excel Code - Move Up fails > > ---------------------------------------------------------------- > > > > > > Sub getlastline() > > > > Set WdObj = CreateObject("Word.Application") > > WdObj.Visible = True > > Set WDDoc = WdObj.Documents.Open(Filename:="c:\temp\table test.doc") > > > > With WDDoc > > Set FirstTable = .Tables(1) > > Set FirstCell = FirstTable.Rows(1).Cells(1) > > FirstCell.Select > > Selection.MoveUp unit:=wdLine, Count:=1 > > > > Selection.Move unit:=wdSentence, Count:=-1 > > Selection.Sentences(1).Select > > End With > > > > End Sub > > > > > > > > >
I believe that the issue is in the use of the selection object The following code run from Excel selects the same area of the document as running the Word code: Sub getlastline() Dim oWord As Word.Application Dim WordWasNotRunning As Boolean Dim oDoc As Word.Document Dim trange As Word.Range 'Get existing instance of Word if it's open; otherwise create a new one On Error Resume Next Set oWord = GetObject(, "Word.Application") If Err Then Set oWord = New Word.Application WordWasNotRunning = True End If On Error GoTo Err_Handler oWord.Visible = True oWord.Activate Set oDoc = oWord.Documents.Open(Filename:="c:\documents\tabletest.docx") Set trange = oDoc.Tables(1).Range trange.Start = trange.Start - 1 trange.Collapse wdCollapseStart trange.Move wdSentence, -1 trange.Sentences(1).Select If WordWasNotRunning Then oWord.Quit End If 'Make sure you release object references. Set oWord = Nothing Set oDoc = Nothing Set myDialog = Nothing 'quit Exit Sub Err_Handler: MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " _ & Err.Number If WordWasNotRunning Then oWord.Quit End If End Sub -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Joel" wrote in message news:E03DC2DD-4425-4A26-B92D-292D5ED06D69@microsoft.com... >I have the same problem with you code as mine. It works in word but >doesn't > work in excel. With youcode it doesn't like the "end". gives error > "Arguement not optional". > > Excel expects for end a parameter and word doesn't require one. I n Excel > > LastRow = Range("A" & Rows.Count).end(xlup).Row > > Even if I get rid of the compiling error by doing this > > WDDoc.Application.myrange.End = .Tables(1).Range.Start > > The line doesn't execute. > > "Doug Robbins - Word MVP" wrote: > >> Use something like: >> >> Dim myrange As Range >> With ActiveDocument >> Set myrange = .Range >> myrange.End = .Tables(1).Range.Start >> End With >> myrange.Sentences.Last.Select >> Set myrange = Selection.Range >> myrange.End = myrange.Paragraphs(1).Range.End >> myrange.Select >> >> >> -- >> Hope this helps. >> >> Please reply to the newsgroup unless you wish to avail yourself of my >> services on a paid consulting basis. >> >> Doug Robbins - Word MVP >> >> "Joel" wrote in message >> news:892882DF-B2AA-4974-96A8-07156E3CB484@microsoft.com... >> > I'm trying to understand differences between excel and word macros. >> > Trying >> > to >> > get Last sentence before first table in a document. I got the word >> > macro >> > to >> > run correctly. I tried applying same code to Excel macro and MoveUp >> > fails. >> > I posted both macro below. Can somebody explain????????????????? >> > >> > Word Macro - Works Correctly >> > ----------------------------------------------------------- >> > >> > Sub getlastline() >> > >> > With ActiveDocument >> > Set FirstTable = .Tables(1) >> > Set FirstCell = FirstTable.Rows(1).Cells(1) >> > FirstCell.Select >> > Selection.MoveUp unit:=wdLine, Count:=1 >> > >> > Selection.Move unit:=wdSentence, Count:=-1 >> > Selection.Sentences(1).Select >> > End With >> > >> > End Sub >> > >> > Excel Code - Move Up fails >> > ---------------------------------------------------------------- >> > >> > >> > Sub getlastline() >> > >> > Set WdObj = CreateObject("Word.Application") >> > WdObj.Visible = True >> > Set WDDoc = WdObj.Documents.Open(Filename:="c:\temp\table test.doc") >> > >> > With WDDoc >> > Set FirstTable = .Tables(1) >> > Set FirstCell = FirstTable.Rows(1).Cells(1) >> > FirstCell.Select >> > Selection.MoveUp unit:=wdLine, Count:=1 >> > >> > Selection.Move unit:=wdSentence, Count:=-1 >> > Selection.Sentences(1).Select >> > End With >> > >> > End Sub >> > >> > >> > >> >> >>
>I believe that the issue is in the use of the selection object Indeed - without qualification, Selection refers to Excel's Selection, not Word's. As does Range. This should make your original macro work: Sub getlastline() Set WdObj = CreateObject("Word.Application") WdObj.Visible = True Set WDDoc = WdObj.Documents.Open(Filename:="c:\temp\table test.doc") With WDDoc Set FirstTable = .Tables(1) Set FirstCell = FirstTable.Rows(1).Cells(1) FirstCell.Select WdObj.Selection.MoveUp unit:=wdLine, Count:=1 WdObj.Selection.Move unit:=wdSentence, Count:=-1 WdObj.Selection.Sentences(1).Select End With End Sub -- Enjoy, Tony www.WordArticles.com
