Pasting in word table, below bookmarks

klutch · #31 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.

macropod · #32 06-26-2018, 04:09 PM

It's not apparent to me how anything in your workbook relates to the table in your document.

klutch · #33 06-27-2018, 05:45 AM

how is it not apparent? Do you mean my code, or my task in general?
From the excel file, I want to take the average mean, which for this instance is located in cell e4, and for each SET POINT, which are -20,5,22, I want to paste it into that column of the word table.
So if the set point is -20 I want to paste into the first empty cell of the -20 column,
If it is 5 I want to paste into the first empty cell of the 5 column
If it is 22 I want to paste into the first empty cell of the 22 column.
To differentiate the set points, I have been using if statements, but that has been messy so now I am trying to write three separate macros for each set point. I will be using these macros on the same word table continuously and wish not to change the paste location in the macro every time it is run. I want to find the first empty cell in the specified column, and then select/paste into it or write it in.

klutch · #34 06-27-2018, 06:08 AM

Code:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wDoc 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
    'makes the file appear
    wdApp.Visible = True
    Set wDoc = wdApp.Documents.Open(myfile)
 
    With wDoc
 
    'selects the paste range in the performance review table, depending on the set point
If Range("c2") = 22 Then wDoc.Tables(8).Cell(4, 1).Select
If Range("c2") = 5 Then wDoc.Tables(8).Cell(4, 2).Select
If Range("c2") = -20 Then wDoc.Tables(8).Cell(4, 3).Select
 
 
    'and paste the clipboard contents
    wdApp.Selection.Collapse wdCollapseEnd
    wdApp.Selection.Paste
    wdApp.Selection.Font.Name = "Times New Roman"
    wdApp.Selection.Font.Size = 12
    wdApp.Selection.Font.Bold = wdToggle
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
 
wDoc.Tables(8).Cell(4, 0).Select
wdApp.Selection.TypeText Text:="Performance Review"
wDoc.Tables(8).Rows(4).Select
    wdApp.Selection.Shading.Texture = wdTextureNone
    wdApp.Selection.Shading.ForegroundPatternColor = wdColorAutomatic
    wdApp.Selection.Shading.BackgroundPatternColor = -603914241
   End With
    wDoc.Save
 
 
End Sub

This is what I have right now. It works, but the problem is that if I wish to run it again it just pastes over the previous data. Which is why I need to search for the first blank cell, or have the user select where the macro will paste.

macropod · #35 06-27-2018, 04:52 PM

Quote:

Originally Posted by klutch

how is it not apparent? Do you mean my code, or my task in general?

I would have thought:

Quote:

Originally Posted by macropod

It's not apparent to me how anything in your workbook relates to the table in your document.

was plain enough. It's not about your code but how anything in your Excel workbook relates to the table in your document. It's all very well for you to know

Quote:

Originally Posted by klutch

I want to take the average mean, which for this instance is located in cell e4, and for each SET POINT, which are -20,5,22, I want to paste it into that column of the word table.

but there's no way anyone else could have known that. Why do you suppose you were asked to provide:

Quote:

Originally Posted by macropod

a description of what goes where.

Kindly also correct your user profile; it's impossible to know what we're coding for when it says you're using Office 2016 for Mac on a Windows 7 platform...

klutch · #36 06-28-2018, 06:41 AM

Okay sorry for not being clear before.
Here is what I have now and it works!

Code:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wDoc 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)
   
    'makes the file appear
    wdApp.Visible = True
    Set wDoc = wdApp.Documents.Open(myfile)
    
    With wDoc
Dim oRow As Row
For Each oRow In wDoc.Tables(8).Rows
  If Len(oRow.Range) = 16 Then
    With oRow
      .Cells(1).Range.Text = "Performance Review"
      .Cells(2).Range.Text = ""
      .Cells(3).Range.Text = Range("e" & i)
      .Cells(4).Range.Text = ""
      .Cells(5).Range.Text = ""
    End With
    Exit For
  End If
Next
 
  
  End With
   
    wDoc.Save
    
    
End Sub

I will create 2 more identical macros to this so that I have a macro for each Cells(2),(3),(4).
All that is left that I would like to do is to change the background color if there is text in a row. I was thinking something like

Code:

 If Len(oRow.Range) >= 16 Then
With oRow
Shading.Texture = wdTextureNone
    Change.Shading.ForegroundPatternColor = wdColorAutomatic
    Change.Shading.BackgroundPatternColor = -603914241
  End If
  End With

I got the code from recording a macro in word and changing the background color to white. I am not sure if this is compatible with the rest of my code however, because I keep getting "Block end if without if" even though there is an if.
All together it looks like this

Code:

Sub CopyAndPaste()
    Dim myfile, wdApp As New Word.Application, wDoc 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)
    Range("E" & i).Select
    Selection.Copy
   
    'makes the file appear
    wdApp.Visible = True
    Set wDoc = wdApp.Documents.Open(myfile)
    
    With wDoc
Dim oRow As Row
For Each oRow In wDoc.Tables(8).Rows
  If Len(oRow.Range) = 16 Then
    With oRow
      .Cells(1).Range.Text = "Performance Review"
      .Cells(2).Range.Text = Range("e" & i)
      .Cells(3).Range.Text = ""
      .Cells(4).Range.Text = ""
      .Cells(5).Range.Text = ""
    End With
    
  End If
Next
If Len(oRow.Range) >= 16 Then
With oRow
Shading.Texture = wdTextureNone
    Change.Shading.ForegroundPatternColor = wdColorAutomatic
    Change.Shading.BackgroundPatternColor = -603914241
  End If
  End With
   
    wDoc.Save
    
    
End Sub

Again, all I need to do now is to change the background color to white and then I am set!

klutch · #37 06-28-2018, 09:02 AM

Okay I noticed something about my code that will not get me quite where I want it to be. when I run each one, I want it to be placed into the first blank cell per the column. Essentially, we are running 3 variables to test the overall quality of a unit.
So I do not want each macro to enter into a new row, I only want the first macro (the 22) to type in "Performance Review" and enter the excel data into the first empty cell in the "22" column. I want the second macro to only enter the excel data into the first empty cell in the "5" column and the third macro I want it to enter data into the first empty cell of the "-20" column.

Lastly, if there is no data to report, I would like to make a 4th macro that will take the last cell with data in it and copy it down to the first empty cell of that column.

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