#1
|
|||
|
|||
repeat header rows after VBA paste
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. |
#2
|
||||
|
||||
What is the code you're using?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
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 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. Last edited by macropod; 08-20-2018 at 02:25 AM. Reason: Added code formatting |
#4
|
||||
|
||||
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 PS: When posting code, please ensure it's properly formatted - yours didn't even have line breaks.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
|
#8
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
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 |
#10
|
|||
|
|||
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. |
#11
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
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 |