Export Excel Ranges to Word bookmarks using VBA and save word doc in same location as workbook

[CuddlyGoldfish]

Hi All,

I use MS 2007. I have an excel workbook with a number of named ranges and a word doc with a number of bookmarks. I am able to export the values in the named ranges to the corresponding bookmarks, and I can get the name of the doc to change to a specific name, but not the name I want, and it wont save to a location. I want the name of the doc to be the value in the range/bookmark "Name", followed by " Cert " followed by the current year.

My code is below, the save as part is at the end. Does anyone have any clever ideas?

Sub CertGenerator()

On Error GoTo errorHandler

Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Name As Excel.Range
Dim Start As Excel.Range
Dim Price As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

Set myDoc = wdApp.Documents.Add(Template:="H:\Administration\Documentation\Templates\Cert Template 2013.docx")
Set Name = Sheets("Data Sheet").Range("Name")
Set Start = Sheets("Data Sheet").Range("Start")
Set Price = Sheets("Data Sheet").Range("Price")

With myDoc.Bookmarks
.Item("Name").Range.InsertAfter Name
.Item("Start").Range.InsertAfter Start
.Item("Price").Range.InsertAfter Price
End With

With wdApp.ActiveDocument
.SaveAs ThisWorkbook.Path & "Cert"
End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing

End Sub

 

[shg]

Duplicate post.

 

[CuddlyGoldfish]

Hi @shg I'm not sure if your duplicate post comment is in reference to my other post, or a resolved post by someone else asking the same question. It is true I have another post asking a very similar question, however this post is looking for a way to save my document, whereas the other post is about setting the formatting in the document. I have read in other threads that this was the appropriate way to post new threads.

Anyway, a solution to this problem has been found, the code is as follows:


Sub CertGenerator()

Set Name = Sheets("Data Sheet").Range("Name")
Set Start = Sheets("Data Sheet").Range("Start")
Set Price = Sheets("Data Sheet").Range("Price")
With New Word.Application
.Visible = True
.WindowState = wdWindowStateMaximize
With .Documents.Add(Template:="C:\Administration\Documentation\Templates\Cert Template 2013.docx")
With .Bookmarks
.Item("Name").Range.InsertAfter Sheets("Data Sheet").Range("Name").Text
.Item("Start").Range.InsertAfter Sheets("Data Sheet").Range("Start").Text
.Item("Price").Range.InsertAfter Sheets("Data Sheet").Range("Price").Text
End With
.SaveAs ThisWorkbook.Path & "\" & Sheets("Data Sheet").Range("Name").Text & " Cert " & Format(Date, "yyyy")
End With
End With
End Sub

</PRE>

 

[LoganR91]

Hello,
I am a very basic user of excel and am trying to make things easier for my company with a form generator from an excel list we have.
I have created a word template to use for emails. What i need is to have the values from a spicific row to replace the bookmarks i have in place in my template.
I have a macro which will create the word document but I am having trouble with the transfering tthe cell data to the bookmarks.
Here is my macro to create the document:


Sub SampleRequest()
Dim wd As Word.Application
Dim Request As Word.Document
Set wd = New Word.Application
With wd
.Visible = True
Set Request = .Documents.Add(Template:="I:\Sourcing Requests\Sample Request Form.dotx")


End With
Set wd = Nothing
End Sub


If I use the macro you wrote at the top of this thread I can get it to do what I want but only for the spicific defined cells which does not work since I need this for a different row 5 times a day.

Thank you for any help,
Ill be on here a lot more often