Excel cell value with space between paragraphs with excel to word bookmark

[molesy01]

Hi
I have a userform in excel that copies text from a cell and puts it within a bookmark in a word template. I have created code which works however the issue i have is that i require more than 1 cell value to populate into a single bookmark with a space between the paragraphs. Hope someone can help. Thanks

sub ExportButton(control As IRibbonControl)

Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Testing")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate
objWord.Documents.Open "\\Desktop\Testing.dotm"
With objWord.ActiveDocument
.Bookmarks("RPD").Range.Text = ws.Range("E10").Value
.Bookmarks("RPD").Range.Text = ws.Range("E12").Value
.Bookmarks("RPD").Range.Text = ws.Range("E14").Value

End With

Set objWord = Nothing

End Sub

 

[NdNoviceHlp]

Hi again Steve. It seems like you want to put those 3 cells into the 1 bookmark with a space in between the paragraphs. You can trial this. If CHR(11) doesn't work, trial CHR(13), .typeparagraph or VbCrlf. HTH. Dave

With objWord.ActiveDocument
.Bookmarks("RPD").Range.Text = CStr(ws.Range("E10").Value) & Chr(13) & Chr(11) _
& CStr(ws.Range("E12").Value) & Chr(13) & Chr(11) _
& CStr(ws.Range("E14").Value)
End With

 

[molesy01]

Hi Dave. Thanks for your reply. Think i may have to abort what im trying to achieve. I have come across a major issue and im not knowledgeable enough to remedy. Its always hard to explain what i need to achieve
but ill have a wiz.

As i explained before, i have 9 sheets, lets call them sheet1, sheet2 etc. to Sheet9.
Each sheet will have text in cells from E10, there is no definitive number of cells within the column, i could enter text in numerous cells with a gap between rows, so each cell with have an even number, E10, E12 etc.

I need to copy and paste each cell into a bookmark in word. Now here is where it gets complicated. we are unsure of how many rows in "E" that need to be copied into a particular bookmark, the amount of cells in column "E" associated to a bookmark is variable. If it was constant then i could have used the method i was already using. I was trying to use the .End(x1Down) but im not really sure of what i am doing.

Lets say Sheet1 E10 is associated with "Bookmark1" thats easy, however, If E12, plus other rows, is associated with "Bookmark2" which is variable, is difficult. These would also need to be pasted in the bookmark with a space between as mentioned earlier.

Is there a way of searching for a bookmark in E associated with a row and pasted into the bookmark in word in order of sequence. Or am i asking too much of excel?

Really appreciate your help so far, if its a pain to understand not to worry.

Thanks Dave.

 

[NdNoviceHlp]

I'm sorry Steve, but I just don't understand why you can't indicate in the sheet to what bookmark each cell is supposed to go to? "we are unsure of how many rows in "E" that need to be copied into a particular bookmark, the amount of cells in column "E" associated to a bookmark is variable" It's easy to code for every other cell in "E" from row 10 to the lastrow for 1 sheet going to one bookmark. Did you trial the code I posted? Does it combine the cells as you wanted or did I misunderstand? If more than 1 bookmark is associated with the rows in "E" for 1 sheet then there must be some logic or indication that whatever rows go to whatever bookmark. You can get the sequential bookmarks from a document like this and load them into an array (but you still have to have some rule for placing data into them)....

For i = 1 To objWord.activedocument.Bookmarks.Count
MsgBox objDoc.Range.Bookmarks(i)
Next i

This is doable. You just need to set up your data for success. Dave

 

[molesy01]

Hi Dave. Yes, me again Haha. I really admire your knowledge with VBA, I can only dream about being as good as you. Thinking of enrolling on a beginners course.
I know its not you job to teach but to be honest I do struggle understanding most of the code and what it means, if I could pull this off then It would surely be bonus points at work. Been trying to complete this application since I started a few years ago and just having to get over this part to the finish line. There has been a lot of work getting to this stage, and getting it to transfer into word I thought would be easy, however, its not. Well for me it isn't. As I said its not your job to teach but it would be great if you were to help me stage by stage, lets say i start at kindergarten and work my way up to 11th grade.
Ha-ha. Lets start from the sheet1, do i need to put the bookmark "Name" anywhere on the sheet? if so where do you recommend?

Thanks

 

[NdNoviceHlp]

Let's start with your template document. Do you have it set up with all the bookmarks located in the document where you want them? By the way, if every sheet doesn't have data for every bookmark, what happens with the empty bookmark location's emptiness? You will need to decide your method for imputing data to the bookmarks. I don't understand. Will there only be 1 bookmark per sheet (that may include multiple cells), some of the bookmarks or all of the bookmarks in each sheet? If there may be more than 1 cell per bookmark, or there are multiple bookmarks per sheet, you will need to indicate somewhere in the sheet which "E" cell goes to which bookmark... VBA is not psychic. I'm not sure how you place your data in "E", but when you place it, use a blank column somewhere and indicate on the same row to what bookmark you want the "E" data to go. Then it's straight forward. For example, using "Q" as an indicator column, if "E" rows 10,12 & 14 are part of "Bookmark1" then rows 10,12 & 14 of Column "Q" would indicate "Bookmark1"which could then be gathered together and placed in the Word doc in the "Bookmark1" location. I've got some farming to do so I may not get back to this until later today.... but it does look like rain. Dave

 

[molesy01]

Hi Dave. Yes the template is setup and all of the bookmarks are in. I've uploaded screen shots.
AllSheets has all of the data. Cells are shaded in colour as they have the same bookmark.
The Bookmark column is not coded or anywhere else, they are there so I can identify the cells.
Config has the category names and codes
This userform extracts the data from “AllSheets” and “config” and the listbox “commentary” puts it in column E, of “Section A etc.”
Section A, B, C. D, E, F, G, H, I, etc, Column E is copied to the bookmarks in the Template Doc.
I've uploaded these to try and give you an idea of what im trying to achieve.
Thanks for your help.

 

[NdNoviceHlp]

Lunch break. Unfortunately the screen shots won't zoom large enough for me to see the details. Did you add the shading just for illustration? Seems fairly straight forward. When you extract the data to whatever row in "E", add a bit of code to add the name of the bookmark to the same row in a different column. When you say sheets Section A.... Section I etc. do you mean there are more sheets than just Section A-Section I? Dave

 

[molesy01]

Hi Dave
Thanks for your reply. Yes there are 9 sheets A-I, I have managed to get the name of the bookmark to the same row in a different column ("F")
The shading is just for illustration, it just breaks up the rows showing which bookmark group they are in.
thanks

 

[NdNoviceHlp]

You can give this a go Steve. It's hard to say what will happen without the wb. If the code errors, you will need to use the task manager to end the Word process before testing again. Let me know what happens (or on what line of code it errors). Dave

Sub ExportButton()
Dim objWord As Object, WdStart As Boolean, LastRow As Integer, i As Integer, BCnt As Integer
Dim ws As Worksheet, BkMkCnt As Integer, ShtCnt As Integer, RowCnt As Integer
Dim ShtArr() As Variant, BkMkArr() As Variant, TempStr As String, BKName As String
'sheet names
ShtArr = Array("Section A", "Section B", "Section C", "Section D", "Section E", _
                "Section F", "Section G", "Section H", "Section I")

'start Word app
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Set objWord = CreateObject("Word.Application")
WdStart = True
End If
'open template *********change path to suit
objWord.Visible = False
objWord.Documents.Open "C:\Users\Desktop\HBRTest.dotx"

'load bookmarks into an array
ReDim BkMkArr(objWord.activedocument.Bookmarks.Count)
For i = 1 To objWord.activedocument.Bookmarks.Count
BkMkArr(i - 1) = objWord.activedocument.Range.Bookmarks(i)
Next i

'loop sheets
For ShtCnt = LBound(ShtArr) To UBound(ShtArr)
'clear bookmarks
For i = 1 To objWord.activedocument.Bookmarks.Count
objWord.activedocument.Bookmarks(i).Range.Text = vbNullString
Next i

'set ws and lastrow "E"
Set ws = ThisWorkbook.Sheets(ShtArr(ShtCnt))
With ws
    LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With

'loop every other "E" entry (10 to lastrow)
For RowCnt = 10 To LastRow Step 2
TempStr = CStr(ws.Cells(RowCnt, "E"))
BKName = CStr(ws.Cells(RowCnt, "F"))

'if more than one "E" row in same bookmark make string
For BkMkCnt = (RowCnt + 2) To LastRow Step 2
If CStr(ws.Cells(RowCnt, "F")) = CStr(ws.Cells(BkMkCnt, "F")) Then
TempStr = TempStr & Chr(11) & CStr(ws.Cells(BkMkCnt, "E"))
Else
Exit For
End If
Next BkMkCnt

'put "E" string in bookmark
With objWord.activedocument
'search for bookmark
For BCnt = LBound(BkMkArr) To UBound(BkMkArr)
If .Bookmarks(BkMkArr(BCnt)) = BKName Then
.Bookmarks(BkMkArr(BCnt)).Range.Text = TempStr
Exit For
End If
Next BCnt
End With

Next RowCnt

'save *********change path to suit
objWord.activedocument.SaveAs2 (ThisWorkbook.Path & "\" & ShtArr(ShtCnt) & ".docx")
Next ShtCnt
'close template/clean up
objWord.activedocument.Close savechanges:=False
If WdStart Then
objWord.Quit
Set objWord = Nothing
End If
End Sub