Applying bookmarks to multiple documents

klutch · #1 06-18-2018, 01:02 PM

Hello, I am wondering if it is possible to apply bookmarks to multiple documents (several hundred in this case)? I have a macro that selects a bookmarked cell in a word table and pastes it there. However the document I am pasting into is unique to each performance review. Meaning that technically, each time I run the macro it is pasting into a different document.

Code:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    'select truck report file
   ChDrive "E:\"
ChDir "E:\WG\TVAL\"
myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
   'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    'copies the cell
    Range("E" & i).Select
    Selection.Copy
 
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'selects the paste range in the performance review table, depending on the set point
    If Range("c2") = 22 Then wdDoc.Bookmarks("d22").Select
    If Range("c2") = 5 Then wdDoc.Bookmarks("d5").Select
    If Range("c2") = -20 Then wdDoc.Bookmarks("d20").Select
 
    'and paste the clipboard contents
    wdApp.Selection.Collapse wdCollapseEnd
    wdApp.Selection.Paste
End Sub

My code is dependent on pasting to the bookmarks, as I need to paste into a specific cell within the table.

Does anyone know if applying bookmarks can be done universally? and if not, does anyone have a better solution to pasting into a table cell?
Here are my previous threads working on this problem:
https://www.mrexcel.com/forum/genera...ine-table.html
https://www.ozgrid.com/forum/forum/h...ile-within-vba

But if you could post solutions in this thread that would be great!

gmayor · #2 06-18-2018, 08:45 PM

Assuming your bookmarks d5, d20 & D22 refer to the column and row of the word table then also assuming that the value of 'i' is correctly resolved (we don't have your sheet to test) then the following should work . Depending on what is in Range("E" & i) you don't have to select and copy it. Just write its content to the Word table.

The code uses late binding to Word and so does not need a reference to Word.

Code:

Sub CopyAndPaste()
Dim myfile, wdApp As Object, wdDoc As Object
Dim oRng As Object
Dim i As Integer, iRow As Integer
    'select truck report file
    ChDrive "E:\"
    ChDir "E:\WG\TVAL\"
    
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    'copies the cell - not necessary if you simply write the range
    Range("E" & i).Copy
    
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    iRow = Range("c2")
    If iRow < 0 Then iRow = Range("c2") * -1
    Set oRng = wdDoc.Tables(1).Cell(iRow, 5).Range
    oRng.End = oRng.End - 1
    oRng.Text = Range("E" & i)
    'or
    'oRng.Paste
End Sub

macropod · #3 06-18-2018, 11:26 PM

Quote:

Originally Posted by klutch

Hello, I am wondering if it is possible to apply bookmarks to multiple documents (several hundred in this case)? I have a macro that selects a bookmarked cell in a word table and pastes it there.

Perhaps you could explain exactly what it is you're trying to achieve. Aside from the fact that working with selections is very inefficient, as is any unnecessary copying & pasting, this and your other thread suggests that whatever you're trying to do might be achieved more efficiently by other means than those you're now trying to implement.

klutch · #4 06-19-2018, 06:11 AM

@macropod Okay here is what my project consists of.
In my department, we are responsible for the testing of our units. There is a performance review sheet for every single unit, this is the document I am pasting into. There is a different document for every unit, it isn't a master document with all the units on it. We use a 3rd party software to collect our data, and then export it into excel. I am trying to write a macro which will automate the process of taking a single cell (the average mean) and pasting it into the performance review doc. This will eliminate the chance of human error. What I have achieved so far is finding the cell I wish to put into excel, open a specific file (I can browse for different files), copy that cell and paste it into one of three bookmarks (depending on a variable in the excel sheet) that are within a table in the doc. The problem I am worried about is having to go through and add the same bookmarks to every single document for each unit.
Let me know if you need more information

klutch · #5 06-19-2018, 06:15 AM

Quote:

Originally Posted by gmayor

Assuming your bookmarks d5, d20 & D22 refer to the column and row of the word table then also assuming that the value of 'i' is correctly resolved (we don't have your sheet to test) then the following should work . Depending on what is in Range("E" & i) you don't have to select and copy it. Just write its content to the Word table.

The code uses late binding to Word and so does not need a reference to Word.

Code:

Sub CopyAndPaste()
Dim myfile, wdApp As Object, wdDoc As Object
Dim oRng As Object
Dim i As Integer, iRow As Integer
    'select truck report file
    ChDrive "E:\"
    ChDir "E:\WG\TVAL\"
 
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    'copies the cell - not necessary if you simply write the range
    Range("E" & i).Copy
 
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    iRow = Range("c2")
    If iRow < 0 Then iRow = Range("c2") * -1
    Set oRng = wdDoc.Tables(1).Cell(iRow, 5).Range
    oRng.End = oRng.End - 1
    oRng.Text = Range("E" & i)
    'or
    'oRng.Paste
End Sub

I am getting an ActiveX can't create object error on this line

Code:

Set wdApp = GetObject(, "Word.Application")

klutch · #6 06-19-2018, 09:44 AM

@macropod I found one of your posts that I think could be used instead of bookmarks.
https://www.msofficeforums.com/word-...addresses.html
How would I implement this into my macro to function as it does now?
Also, how do I identify the table I want to paste into if there are multiple tables in the doc?

gmayor · #7 06-19-2018, 08:23 PM

Quote:

Originally Posted by klutch

I am getting an ActiveX can't create object error on this line

Code:

Set wdApp = GetObject(, "Word.Application")

Sorry. I missed the reference that you were using the Mac version

. However the part of the code that references the table and its cells should still be valid for insertion into your original macro.

As for which table, you can select the table by its number i.e. in the code wdDoc.Tables(1) 1 refers to the first table.

If the table in question varies according to the data then you will have to describe how the table to be processed is determined.

macropod · #8 06-19-2018, 08:47 PM

And, as discussed here: https://www.msofficeforums.com/word-...tml#post129660, if you've bookmarked any part of a table, you can address any cell in that table via the bookmark.

klutch · #9 06-20-2018, 07:32 AM

If I wanted to use word table cell selection, how would I implement it? I know Macropod has suggested using bookmarks to select any cell, but from what it seems I will not be able to use bookmarks for this and instead am trying to identify the table cells to paste into. Here is what I have

Code:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    'select truck report file
   ChDrive "E:\"
ChDir "E:\WG\TVAL\"
myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
   'searches for row with "avg" then selects column E(avg of temperature mean) of that row.
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    'copies the cell
    Range("E" & i).Select
    Selection.Copy
 
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'selects the paste range in the performance review table, depending on the set point
    If Range("c2") = 22 Then wdoc.tables(7).Cell("B4").Select
    If Range("c2") = 5 Then wdoc.tables(7).Cell("C4").Select
    If Range("c2") = -20 Then wdoc.tables(7).Cell("D4").Select
    
    'and paste the clipboard contents
    wdApp.Selection.Collapse wdCollapseEnd
    wdApp.Selection.Paste
End Sub

With this code I am getting a "object required" error on this line: If Range("c2") = 5 Then wdoc.tables(7).Cell("C4").Select

gmayor · #10 06-20-2018, 08:02 PM

Code:

If Range("c2") = 22 Then wdoc.Tables(7).Cell(4, 2).Select
If Range("c2") = 5 Then wdoc.Tables(7).Cell(4, 3).Select
If Range("c2") = -20 Then wdoc.Tables(7).Cell(4, 4).Select

macropod · #11 06-20-2018, 08:12 PM

Quote:

Originally Posted by klutch

If I wanted to use word table cell selection, how would I implement it? I know Macropod has suggested using bookmarks to select any cell, but from what it seems I will not be able to use bookmarks for this

Aside from the fact there is rarely any need to select anything - and no need to do so for what you've posted so far - there is also nothing about what you've posted to indicate why you wouldn't be able to employ a bookmark.

klutch · #12 06-20-2018, 08:23 PM

Quote:

Originally Posted by macropod

Aside from the fact there is rarely any need to select anything - and no need to do so for what you've posted so far - there is also nothing about what you've posted to indicate why you wouldn't be able to employ a bookmark.

I can not use bookmarks because per my original question in this thread was how to apply bookmarks to ALL the word docs that I will be using this macro for. As I never got an answer if I would be able to apply bookmarks across an entire folder of doc templates, I am not going to be able to use them to reference cells.

macropod · #13 06-20-2018, 08:46 PM

Quote:

Originally Posted by klutch

I can not use bookmarks because per my original question in this thread was how to apply bookmarks to ALL the word docs that I will be using this macro for.

Doing that is trivial - but so too is referencing tables without recourse to bookmarks. Since nothing is already bookmarked and you'd have to know which table to bookmark anyway, the bookmarking seems pointless. Either way, there is no need to select anything...

klutch · #14 06-21-2018, 05:40 AM

@macropod Okay, how do I copy/paste it into the cell I want then?

macropod · #15 06-21-2018, 03:52 PM

With code like:
ActiveDocument.Tables(7).Cell(r,c).Range.Text = Sheet1.Range("A1").Value
where r is the Word table row # and c is the Word table column #.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Having Issues Applying a Macro Across Multiple Files. Changes Just Don't Seem To Save.	Kajex	Word VBA	2	09-08-2017 06:37 AM
Applying heading style to multiple pages at once	jane.bugai	Word	5	02-24-2017 04:05 PM
Form updating Bookmarks - writes to the bookmarks multiple times	PeterPlys	Word VBA	13	01-14-2015 06:41 AM
Applying a conditional format to multiple rows	secoo140	Excel	1	10-12-2013 07:19 PM
Publisher 2000 - Applying multiple styles	Jerb	Publisher	0	02-15-2009 10:06 AM