#1
|
|||
|
|||
Word GoTo From Excel
Hi all, I am wondering how to go to a bookmark in Word when its macro is ran from Excel. This is what I have in Excel at the moment: Code:
Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim i As Integer Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True Set wrdDoc = wrdApp.Documents.Open(directory) With wrdDoc If .Bookmarks.Exists("g") Then MsgBox "yes" wrdDoc.GoTo What:=wdGoToBookmark, Name:="g" Else MsgBox "Not Found" End If Thank you, Matt |
#2
|
||||
|
||||
Hi Matt,
If you're simply updating a bookmarked range, there is no need for the GoTo - simply address the range: Code:
Dim wrdApp As Word.Application Dim wrdDoc As Word.Document, WrdRng As Word.Range Dim i As Integer Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True Set wrdDoc = wrdApp.Documents.Open(directory) With wrdDoc If .Bookmarks.Exists("g") Then Set WrdRng = .Bookmarks("g").Range .Tables.Add Range:=WrdRng, NumRows:=2, NumColumns:=2 ' Reapply the bookmark to the table .Bookmarks.Add Name:="g", Range:=WrdRng End If End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul,
Thanks for the response. I should've clarified. What I'm looking to do is be able to go to a bookmark in Word, from Excel. This bookmark is associated with a table in Word that I want to work with. So, the macro from Excel opens the Word file, goes to the bookmark, and then performs certain things on the table which I can code myself. Thanks, Matt |
#4
|
||||
|
||||
You still don't need to 'GoTo' the table. Indeed, it seems like you're trying to use 'GoTo' as a surrogate for 'Select'.
Neither is necessary for vba processing. Furthermore, if you know which table it is in the document, you don't even need a bookmark. For example: Code:
With wrdDoc.Tables(4).Range .Cells(2, 3).Text = "My Text" End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Unfortunately the table number won't be constant as more tables will keep being added to the document and will require the same functionality as I am trying to produce with this Excel macro, each is just referenced differently under bookmark names. Here is the skeleton of what I'm trying to do:
If this excel file X, does not have a table in document Y Then add a table with bookmark X and input certain contents ElseIf excel file X does have a table in document Y (bookmark X exists), Then go to that bookmark (hence the table) and modify contents Thanks again for the response. |
#6
|
||||
|
||||
It seems, then, your code should be structured along the lines of:
Code:
With wrdDoc If .Bookmarks.Exists("g") Then With .Bookmarks("g").Range If .Tables.Count > 0 Then With .Tables(1) ' Do whatever you need to with the table End With Else ' Do whatever you need to if the table doesn't exist End If End With Else ' Do whatever you need to if the bookmark doesn't exist End If End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Thanks Paul, I will give that a try as soon as I can. If the bookmark does not exist, the table will be added to the top of the document.
Thanks. |
#8
|
|||
|
|||
Everything's good!
|
#9
|
|||
|
|||
One more minor question. What is the equivalent code when running this
Code:
Selection.HomeKey Unit:=wdStory Selection.EndKey Unit:=wdLine Selection.TypeParagraph from Excel, onto word? Essentially I want to point to the top of the document, skip some content until the end of the line, and then do a Return. Thanks. |
#10
|
||||
|
||||
That sounds rather like what you want is:
Code:
With wrdDoc .... .Paragraphs.First.Range.InsertAfter vbCr End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
I tried it but unfortunately it didn't seem to do what I wanted. What should happen is that I have a title at the top of the document. Since there is no bookmark for a certain excel file, there should be a table added underneath that title. What actually happened was that the title got overwritten by the table.
Suggestions appreciated. Thank you. |
#12
|
||||
|
||||
What is the code you're using to insert/create the table?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#13
|
|||
|
|||
I have replaced some text with three dots (...) since they contain info I do not want to release
Code:
Dim wrdApp As Word.Application Dim wrdDoc As Word.Document Dim i As Integer Set wrdApp = CreateObject("Word.Application") wrdApp.Visible = True Set wrdDoc = wrdApp.Documents.Open(directory) With wrdDoc If .Bookmarks.Exists(table_name) Then With .Bookmarks(table_name).Range If .Tables.Count > 0 Then ... Else ... End If End With Else ... With .Tables(1) If .Style <> "Table Grid" Then .Style = "Table Grid" End If .ApplyStyleHeadingRows = True .ApplyStyleLastRow = False .ApplyStyleFirstColumn = True .ApplyStyleLastColumn = False .ApplyStyleRowBands = True .ApplyStyleColumnBands = False End With .Bookmarks.Add Name:=table_name With .Tables(1) With .Cell(1, 1) .Range.Text = ... .Select .Application.Run MacroName:=... End With .Cell(1, 2).Range.Text = ... .Cell(1, 3).Range.Text = ... With .Cell(2, 1) .Select .Range.Bookmarks.Add Name:=... .Range.Text = ... End With With .Cell(2, 2) .Shading.Texture = wdTextureNone .Shading.ForegroundPatternColor = wdColorAutomatic .Shading.BackgroundPatternColor = -721354753 End With With .Cell(2, 3) .Shading.Texture = wdTextureNone .Shading.ForegroundPatternColor = wdColorAutomatic .Shading.BackgroundPatternColor = -721354753 End With End With End If End With |
#14
|
||||
|
||||
Hi Bill,
I understand your need for privacy re what you can post and have no problems with that. Unfortunately, though, the code I most needed to see hasn't been included. Since you want the table inserted immediately after the title, try: Code:
With wrdDoc 'All the other code before the insertion of an empty paragraph after the title .Paragraphs.First.Range.InsertAfter vbCr .Tables.Add Range:=.Paragraphs.First.Next.Range, NumRows:=4, NumColumns:=3 End With
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#15
|
|||
|
|||
Hi Paul,
That was what I was looking for. Your help was greatly appreciated! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can this be fix in excel or should I go to word | lostsoul62 | Excel | 1 | 04-18-2012 01:00 AM |
From Excel to Word? | lostsoul62 | Word | 1 | 03-19-2012 06:20 PM |
Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248 | Joe Patrick | Word VBA | 2 | 01-30-2012 07:23 AM |
Word to Excel | rynman | Office | 5 | 04-19-2009 06:50 AM |
Excel to Word | retrospect1984 | Excel | 1 | 02-18-2009 06:41 AM |