Word GoTo From Excel

mata30s · #1 12-04-2012, 12:34 PM

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

It all works, except the Word document does not go to the bookmark. After this I am hoping to expand on the code (such as adding Word Tables from the Excel code). Input appreciated.

Thank you,

Matt

macropod · #2 12-04-2012, 02:35 PM

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

mata30s · #3 12-04-2012, 02:59 PM

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

macropod · #4 12-04-2012, 03:28 PM

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

populates cell C2 in the 4th table in the document.

mata30s · #5 12-04-2012, 04:07 PM

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.

macropod · #6 12-04-2012, 04:27 PM

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

What isn't clear, though, is how you're going to determine where to add the table in the absence of the bookmark, which you appear to suggest could be the case.

mata30s · #7 12-04-2012, 04:34 PM

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.

mata30s · #8 12-05-2012, 10:50 AM

Everything's good!

mata30s · #9 12-05-2012, 02:53 PM

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.

macropod · #10 12-05-2012, 03:35 PM

That sounds rather like what you want is:

Code:

With wrdDoc
 ....
    .Paragraphs.First.Range.InsertAfter vbCr
End With

mata30s · #11 12-07-2012, 12:00 PM

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.

macropod · #12 12-07-2012, 02:38 PM

What is the code you're using to insert/create the table?

mata30s · #13 12-07-2012, 03:10 PM

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

macropod · #14 12-07-2012, 07:35 PM

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

The above code will replace the empty paragraph after the title with a 4-row*3-column table.

mata30s · #15 12-10-2012, 09:36 AM

Hi Paul,

That was what I was looking for. Your help was greatly appreciated!

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