Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-04-2012, 12:34 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default 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
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
Reply With Quote
  #2  
Old 12-04-2012, 02:35 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #3  
Old 12-04-2012, 02:59 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 12-04-2012, 03:28 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 12-04-2012, 04:07 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 12-04-2012, 04:27 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-04-2012, 04:34 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 12-05-2012, 10:50 AM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

Everything's good!
Reply With Quote
  #9  
Old 12-05-2012, 02:53 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 12-05-2012, 03:35 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #11  
Old 12-07-2012, 12:00 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 12-07-2012, 02:38 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What is the code you're using to insert/create the table?
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 12-07-2012, 03:10 PM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 12-07-2012, 07:35 PM
macropod's Avatar
macropod macropod is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #15  
Old 12-10-2012, 09:36 AM
mata30s mata30s is offline Word GoTo From Excel Windows 7 64bit Word GoTo From Excel Office 2007
Novice
Word GoTo From Excel
 
Join Date: Nov 2012
Posts: 13
mata30s is on a distinguished road
Default

Hi Paul,

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

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
Word GoTo From Excel 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 GoTo From Excel Word to Excel rynman Office 5 04-19-2009 06:50 AM
Word GoTo From Excel Excel to Word retrospect1984 Excel 1 02-18-2009 06:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:46 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft