Pasting in word table, below bookmarks

klutch · #1 06-15-2018, 01:43 PM

How do I post a picture of my table? I have screenshots of it

d4okeefe · #2 06-15-2018, 02:00 PM

No need. I think I get it. Try this on your word document. Ignore the spreadsheet for the moment. See if you get what you want. Use Ctrl-Z to undo if necessary.

Code:

Sub expand_bookmark_to_include_insertion()
    Dim bk As Bookmark
    Dim new_range As Range
    
    Set bk = ActiveDocument.Bookmarks(1)
    Dim bk_name As String: bk_name = bk.Name
    
    Set new_range = ActiveDocument.Range(bk.Start, bk.End)
    new_range.InsertAfter (vbCrLf & "Inserted text")
    bk.Delete

    Dim bk_new As Bookmark
    Set bk_new = Bookmarks.Add(bk_name, new_range)
End Sub

d4okeefe · #3 06-15-2018, 02:15 PM

There's a lot going on in your original code, so I'll do my best. I can't recreate it perfectly without the original files. Here goes:

Code:

Sub test_ok()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    Range("E" & i).Select
    Dim excel_selection As String
    excel_selection = Selection.Text
     
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'select the word range you want to paste into
    
    Dim bk As Bookmark, bk_new As Bookmark
    Dim new_range As Range
    Dim bk_name As String
    If Range("c2") = 22 Then
        Set bk = wdDoc.Bookmarks("d22")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
    If Range("c2") = 5 Then
        Set bk = wdDoc.Bookmarks("d5")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
    If Range("c2") = -20 Then
        Set bk = wdDoc.Bookmarks("d20")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
    
    new_range.InsertAfter (vbCrLf & excel_selection)
    bk.Delete
    Set bk_new = wdDoc.Bookmarks.Add(bk_name, new_range)
End Sub

klutch · #4 06-18-2018, 12:19 PM

Quote:

Originally Posted by d4okeefe

There's a lot going on in your original code, so I'll do my best. I can't recreate it perfectly without the original files. Here goes:

Code:

Sub test_ok()
    Dim myfile, wdApp As New Word.Application, wdDoc As Word.Document
    myfile = Application.GetOpenFilename(, , "Browse for Document")
    Dim i As Integer
    i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
    Range("E" & i).Select
    Dim excel_selection As String
    excel_selection = Selection.Text
 
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Open(myfile)
    'select the word range you want to paste into
 
    Dim bk As Bookmark, bk_new As Bookmark
    Dim new_range As Range
    Dim bk_name As String
    If Range("c2") = 22 Then
        Set bk = wdDoc.Bookmarks("d22")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
    If Range("c2") = 5 Then
        Set bk = wdDoc.Bookmarks("d5")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
    If Range("c2") = -20 Then
        Set bk = wdDoc.Bookmarks("d20")
        bk_name = bk.Name
        Set new_range = wdDoc.Range(bk.Start, bk.End)
    End If
 
    new_range.InsertAfter (vbCrLf & excel_selection)
    bk.Delete
    Set bk_new = wdDoc.Bookmarks.Add(bk_name, new_range)
End Sub

I am getting a mismatch error on this line

Code:

 Set new_range = wdDoc.Range(bk.Start, bk.End)

macropod · #5 06-15-2018, 04:56 PM

Cross-posted at: https://www.office-forums.com/thread...l-vba.2350340/
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

Guessed · #6 06-15-2018, 06:09 PM

Is the code meant to add a row under the row where the bookmark exists? If not, how do you know there is a cell there and it is empty?
Assuming there is always a cell below the bookmark, I would do the code like this. Read through this carefully because it is considerably different from what you were previously doing and includes some of the testing that is required to deal with the errors that you might encounter

Code:

Sub CopyToWord()
  Dim myfile As String, wdApp As New Word.Application, wdDoc As Word.Document
  Dim i As Integer, dblC22 As Double, wdRng As Word.Range, strAve As String
  
  i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
  strAve = Range("E" & i).Value
  dblC22 = Range("c2")
  
  myfile = Application.GetOpenFilename(, , "Browse for Document")
  wdApp.Visible = True
  Set wdDoc = wdApp.Documents.Open(myfile)
  
  Select Case dblC22
    Case 5, 22, -20
      If wdDoc.Bookmarks.Exists("D" & Abs(dblC22)) Then
        Set wdRng = wdDoc.Bookmarks.Exists("D" & Abs(dblC22))
        If wdRng.Information(wdWithInTable) Then
          wdRng.MoveEnd Unit:=wdCell, Count:=4
          wdRng.InsertAfter strAve
        Else
          wdRng.InsertAfter strAve
        End If
      End If
  End Select
End Sub

klutch · #7 06-18-2018, 12:18 PM

Quote:

Originally Posted by Guessed

Is the code meant to add a row under the row where the bookmark exists? If not, how do you know there is a cell there and it is empty?
Assuming there is always a cell below the bookmark, I would do the code like this. Read through this carefully because it is considerably different from what you were previously doing and includes some of the testing that is required to deal with the errors that you might encounter

Code:

Sub CopyToWord()
  Dim myfile As String, wdApp As New Word.Application, wdDoc As Word.Document
  Dim i As Integer, dblC22 As Double, wdRng As Word.Range, strAve As String
 
  i = Application.Match("Avg", Sheet1.Range("A1:A20"), 0)
  strAve = Range("E" & i).Value
  dblC22 = Range("c2")
 
  myfile = Application.GetOpenFilename(, , "Browse for Document")
  wdApp.Visible = True
  Set wdDoc = wdApp.Documents.Open(myfile)
 
  Select Case dblC22
    Case 5, 22, -20
      If wdDoc.Bookmarks.Exists("D" & Abs(dblC22)) Then
        Set wdRng = wdDoc.Bookmarks.Exists("D" & Abs(dblC22))
        If wdRng.Information(wdWithInTable) Then
          wdRng.MoveEnd Unit:=wdCell, Count:=4
          wdRng.InsertAfter strAve
        Else
          wdRng.InsertAfter strAve
        End If
      End If
  End Select
End Sub

I am getting a mismatch error on line

Code:

Set wdRng = wdDoc.Bookmarks.Exists("D" & Abs(dblC22))

d4okeefe · #8 06-18-2018, 02:08 PM

Let me know where you're at in the process.
What does your subroutine look like right now?
Btw, this line of code

Code:

Set new_range = wdDoc.Range(bk.Start, bk.End)

shouldn't give a mismatch error.

This, however, which was posted by guessed,

Code:

Set wdRng = wdDoc.Bookmarks.Exists("D" & Abs(dblC22))

would give you a type mismatch. "wdDoc.Bookmarks.Exists" returns a boolean (true or false) and not a range object. Change it to

Code:

Set wdRng = wdDoc.Bookmarks.("D" & Abs(dblC22)).Range

macropod · #9 06-18-2018, 07:22 PM

Why are you messing around with the bookmark range when as I've already said, you can address anywhere in the table without doing so?

d4okeefe · #10 06-19-2018, 07:47 AM

That is a good point, Paul. I did not think of that, and will use in the future.

klutch · #11 06-25-2018, 11:21 AM

OK, I have decided that pasting through bookmarks will not work for what I am trying to accomplish. I have found this code that allows the user to select the range they want to paste into mid-macro.

Code:

Sub GetUserRange()
    Dim UserRange As Range   
    Output = 565
    Prompt = "Select a cell for the output."
    Title = "Select a cell"
 
'   Display the Input Box
    On Error Resume Next
    Set UserRange = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8) 'Range selection
 
'   Was the Input Box canceled?
    If UserRange Is Nothing Then
        MsgBox "Canceled."
    Else
        UserRange.Range("A1") = Output
    End If
End Sub

This also

Code:

  Dim userResponce As Range
On Error Resume Next
Set userResponce = Application.InputBox("select a range with the mouse", Default:=Selection.Address, Type:=8)
On Error GoTo 0
If userResponce Is Nothing Then
    MsgBox "Cancel clicked"
Else
    MsgBox "You selected " & userResponce.Address

This is for pasting into excel tables. Is there an identical way to select cells in a word table via the user selection?

macropod · #12 06-26-2018, 04:16 AM

We seem to be chasing an ever-moving target here. How about posting a copy of both the workbook and the destination document (delete anything sensitive), together with a description of what goes where. You can do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

klutch · #13 06-26-2018, 12:33 PM

Quote:

Originally Posted by macropod

We seem to be chasing an ever-moving target here. How about posting a copy of both the workbook and the destination document (delete anything sensitive), together with a description of what goes where. You can do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

I am sorry for the wild goose chase, I am picking vba up on the go and the more I learn the more I realize what I really want to do with my macro.
At this point, here is what I am trying to accomplish:
Create 3 macros, one for each column of the word table that I am trying to paste into (the -20,5,22).
These macros will be near identical, except for the paste location.
I want to open up a user selected file, copy a cell that is the avg of the data I am analyzing, and paste it into the first blank cell of the correlating column in the word table. Does this make sense? Let me know what other information you would like.
Macropod thank you for bearing with me, I am sure new users can cause frustration for pros like you! Hoping to get better with this skill.

klutch · #14 06-26-2018, 05:41 AM

The PR file is only the table I am pasting into, there is too much sensitive data in the rest of the doc to upload it. There is also the excel file. Hope this works!

macropod · #15 06-26-2018, 05:49 AM

There seems to be something wrong with your Excel file - Excel reports that it is corrupt.

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pasting table data so that it will wrap around the table	rebmaboss	Word	1	11-25-2016 02:30 AM
Pasting text from Excel cell into word without creating a table, and keeping the in-cell formatting	hanvyj	Excel Programming	0	08-28-2015 01:15 AM
How to insert a table using bookmarks in a document	Catty	Word VBA	3	05-04-2015 03:05 AM
Table of Bookmarks	Niy	Word	3	03-28-2012 12:18 AM
Pasting table in Photoshop cutting off table	azdolfan	Word Tables	0	05-16-2010 01:52 PM