repeat header rows after VBA paste

Homegrownandy · #1 08-17-2018, 07:24 AM

If i enter data manually into the table it will repeat header rows (as it should).

But im using code that will use the pasteappend function.

When this code is ran the headers will not repeat.

Note: I Can't change settings inside "smart cut and paste". Ifi change these settings i get a VBA error saying the command is not available.

I can manually fix this by clicking the table > turning off the "repeat header rows" setting then turn it back on. That works fine.

Is there a way to cycle through that process with VBA?

Andy.

macropod · #2 08-17-2018, 03:59 PM

What is the code you're using?

Homegrownandy · #3 08-20-2018, 01:16 AM

Code:

Sub ExcelDataToWord()
Dim objWord As Object
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch
lngLastRow = Sheets("RISKS").Range("A65535").End(xlUp).Row
Set ws = ThisWorkbook.Sheets("RISKS")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
ws.Range("A4" & ":H" & lngLastRow).Copy
'------------browse---------------
Dim fileExplorer As FileDialog
Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)
'--------------------defaulting folder------------------------
With fileExplorer
  .Title = "Select a Folder"
  .AllowMultiSelect = False
  .ButtonName = "Select"
  .InitialView = msoFileDialogViewList
  .InitialFileName = "\\server\general\RAMS\RAM_RAMS"
  If Right(strName, 1) <> "\" Then
    strFolder = strFolder
  End If
  If .Show <> -1 Then
    Exit Sub
  Else
    strFolder = .SelectedItems(1)
  End If
 End With
'--------------------defaulting folder------------------------
'------------browse---------------
'open the word doc
'objWord.Documents.Open "C:\Users\name\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required
objWord.Documents.Open FileName:=strFolder  'pastes the value of cell at the bookmark
With objWord.ActiveDocument.Bookmarks("RISKS").Range.Characters.Last.Next.PasteAppendTable
  HeadingFormat = True
End With
'Optimize Code
Set objWord = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
Exit Sub
Errorcatch:
Debug.Assert False
MsgBox Err.Description
' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
Resume
End Sub

I'm not sute if you can read the code ive put in here as it's putting it in a small code box. I can paste it all without that if you need.

Its taking data from access in an excel table (only way to avoid formatting errors when using the paste append) and then putting it into word.

The repeating headers isn't its just a nice to have htat should be working.

Thanks, Andy.

macropod · #4 08-20-2018, 02:42 AM

Your code doesn't say what 'HeadingFormat = True' applies to.
Try:

Code:

Sub ExcelDataToWord()
Dim objWord As Object, objDoc As Object
Dim strFolder As String, strName As String
Dim ws As Worksheet
Dim lngLastRow As Long
On Error GoTo Errorcatch
'------------browse---------------
'--------------------defaulting folder------------------------
With Application.FileDialog(msoFileDialogFilePicker)
  .Title = "Select a Folder"
  .AllowMultiSelect = False
  .ButtonName = "Select"
  .InitialView = msoFileDialogViewList
  .InitialFileName = "\\server\general\RAMS\RAM_RAMS"
  If Right(strName, 1) <> "\" Then
    strFolder = strFolder
  End If
  If .Show <> -1 Then
    Exit Sub
  Else
    strFolder = .SelectedItems(1)
  End If
 End With
'--------------------defaulting folder------------------------
'------------browse---------------

Set ws = ThisWorkbook.Sheets("RISKS")
lngLastRow = ws.Range("A65535").End(xlUp).Row
Set objWord = CreateObject("Word.Application")
ws.Range("A4" & ":H" & lngLastRow).Copy

'open the word doc
'objWord.Documents.Open "C:\Users\name\Desktop\RAMS AUTOMATION\Import table test.docx" 'change as required
With objWord
  .Visible = True
  Set objDoc = .Documents.Open(strFolder)
  'pastes the value of cell at the bookmark
  With objDoc.Bookmarks("RISKS").Range
    .Characters.Last.Next.PasteAppendTable
    .Tables(1).Rows(1).HeadingFormat = True
  End With
  .Activate
End With
Set objWord = Nothing: Set objDoc = Nothing
'Clear The Clipboard
Application.CutCopyMode = False
Exit Sub
Errorcatch:
Debug.Assert False
MsgBox Err.Description
' This is temporary, if you leave it in it will go into an endless loop so do not forget to remove
Resume
End Sub

Note the other modifications I've made to your code.

PS: When posting code, please ensure it's properly formatted - yours didn't even have line breaks.

Homegrownandy · #5 08-20-2018, 03:43 AM

Thanks Paul,

I had issues posting code here. The code "tag" was half way into my code over lapping it and i was unable to move. Normally I just copy code and paste it on here (or other forums) and the formatting is okay. I dont know how to add line breaks once I've pasted here.

I just tried copying the code you provided and pasting here and it seems ok, if it happens again ill refresh or make a post about the error. (unless I'm doing something wrong).

Thanks for fixing it anyway and your suggestion. I'll go and try it out.

Homegrownandy · #6 08-20-2018, 04:43 AM

okay, well I now have an error and something to look at. Thats progress!

"cannot access individual rows in this collection because the table has vertically merged cells"

I'll have a google and see if i can find anything.

macropod · #7 08-20-2018, 04:52 AM

Evidently, your table has vertically-merged cells. If you attach a document to a post with a copy of the table, I'll have a look at what might be possible. You can attach a document to a post via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

Homegrownandy · #8 08-20-2018, 04:57 AM

office_forum_test.docx

Thanks. This docuement only contains the table im dealing with, but the document im using contains a lot of different tables.

If this is an issue and you would prefer the whole document (maybe for the table number) i can take out the sensative info and put it on here.

This is the one im testing on currently.

really appreciate you looking at this too!

Andy

Homegrownandy · #9 08-20-2018, 05:50 AM

Paul,

I've took out the line:
.Tables(1).Rows(1).HeadingFormat = True
It now works perfect with header repeating enabled. Clearly your changes did something that it likes!

Thanks a lot.

macropod · #10 08-20-2018, 03:11 PM

The table into which you were pasting already had the HeadingFormat property applied to the shaded rows. Accordingly, there's no need to try to redo that in code.

Homegrownandy · #11 08-21-2018, 03:12 AM

I agree, but with my origional code it didnt do the repeated headers. Your "tidy" code works fine.

Thanks a lot.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Paste an image and have it repeat elsewhere in doc automatically	sryan	Word VBA	1	07-06-2015 01:39 PM
Repeat header of a table, what about captions ?	Yankel	Word Tables	3	08-01-2014 05:18 PM
Repeat rows--NOT	markg2	Excel	0	03-01-2014 11:21 AM
repeat selected table row as header	eNGiNe	Word	2	10-15-2013 11:16 PM
how to repeat row in header	gsrikanth	Excel	10	06-28-2012 02:31 AM