megant
08-09-2017, 11:18 PM
Hello,
I am new to using VBA but have found it very helpful with autofilling bookmarks in word from data contained in excel.
I managed to use the following code to successfully input cell data into a report in word:
Sub test()
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Class Teacher Responses")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "G:\REPORT\TEST_FILE.docx"
With objWord.ActiveDocument
.Bookmarks("TR_1").Range.Text = ws.Range("B2").Value
.Bookmarks("TR_2").Range.Text = ws.Range("C2").Value
.Bookmarks("TR_3").Range.Text = ws.Range("D2").Value
.Bookmarks("TR_4").Range.Text = ws.Range("E2").Value
.Bookmarks("TR_5").Range.Text = ws.Range("F2").Value
.Bookmarks("TR_6").Range.Text = ws.Range("G2").Value
.Bookmarks("TR_7").Range.Text = ws.Range("H2").Value
.Bookmarks("TR_8").Range.Text = ws.Range("I2").Value
.Bookmarks("TR_9").Range.Text = ws.Range("J2").Value
.Bookmarks("TR_10").Range.Text = ws.Range("K2").Value
.Bookmarks("TR_11").Range.Text = ws.Range("L2").Value
.Bookmarks("TR_12").Range.Text = ws.Range("M2").Value
.Bookmarks("TR_13").Range.Text = ws.Range("N2").Value
.Bookmarks("TR_14").Range.Text = ws.Range("O2").Value
.Bookmarks("TR_15").Range.Text = ws.Range("P2").Value
.Bookmarks("TR_16").Range.Text = ws.Range("Q2").Value
.Bookmarks("TR_17").Range.Text = ws.Range("R2").Value
.Bookmarks("TR_18").Range.Text = ws.Range("S2").Value
.Bookmarks("TR_19").Range.Text = ws.Range("T2").Value
.Bookmarks("TR_20").Range.Text = ws.Range("U2").Value
.Bookmarks("TR_21").Range.Text = ws.Range("V2").Value
.Bookmarks("TR_22").Range.Text = ws.Range("W2").Value
.Bookmarks("TR_23").Range.Text = ws.Range("X2").Value
.Bookmarks("TR_24").Range.Text = ws.Range("Y2").Value
.Bookmarks("TR_25").Range.Text = ws.Range("Z2").Value
.Bookmarks("TR_26").Range.Text = ws.Range("AA2").Value
.Bookmarks("TR_27").Range.Text = ws.Range("AB2").Value
.Bookmarks("TR_28").Range.Text = ws.Range("AC2").Value
End With
Set objWord = Nothing
End Sub
Now, I need to do essentially the same thing but with charts.
I have a master excel file with multiple charts across three excel sheets.
I need to be able to copy the charts (about 50 in all) into the word document at the bookmarked locations to prepare a series of reports.
I have tried other codes off this site and the web, but have been unable to get the syntax right.
If anyone is able to point me in the right direction, I'd be very grateful. I have asked around in the office but unfortunately nobody has the answer.
I am new to using VBA but have found it very helpful with autofilling bookmarks in word from data contained in excel.
I managed to use the following code to successfully input cell data into a report in word:
Sub test()
Dim objWord As Object
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Class Teacher Responses")
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Documents.Open "G:\REPORT\TEST_FILE.docx"
With objWord.ActiveDocument
.Bookmarks("TR_1").Range.Text = ws.Range("B2").Value
.Bookmarks("TR_2").Range.Text = ws.Range("C2").Value
.Bookmarks("TR_3").Range.Text = ws.Range("D2").Value
.Bookmarks("TR_4").Range.Text = ws.Range("E2").Value
.Bookmarks("TR_5").Range.Text = ws.Range("F2").Value
.Bookmarks("TR_6").Range.Text = ws.Range("G2").Value
.Bookmarks("TR_7").Range.Text = ws.Range("H2").Value
.Bookmarks("TR_8").Range.Text = ws.Range("I2").Value
.Bookmarks("TR_9").Range.Text = ws.Range("J2").Value
.Bookmarks("TR_10").Range.Text = ws.Range("K2").Value
.Bookmarks("TR_11").Range.Text = ws.Range("L2").Value
.Bookmarks("TR_12").Range.Text = ws.Range("M2").Value
.Bookmarks("TR_13").Range.Text = ws.Range("N2").Value
.Bookmarks("TR_14").Range.Text = ws.Range("O2").Value
.Bookmarks("TR_15").Range.Text = ws.Range("P2").Value
.Bookmarks("TR_16").Range.Text = ws.Range("Q2").Value
.Bookmarks("TR_17").Range.Text = ws.Range("R2").Value
.Bookmarks("TR_18").Range.Text = ws.Range("S2").Value
.Bookmarks("TR_19").Range.Text = ws.Range("T2").Value
.Bookmarks("TR_20").Range.Text = ws.Range("U2").Value
.Bookmarks("TR_21").Range.Text = ws.Range("V2").Value
.Bookmarks("TR_22").Range.Text = ws.Range("W2").Value
.Bookmarks("TR_23").Range.Text = ws.Range("X2").Value
.Bookmarks("TR_24").Range.Text = ws.Range("Y2").Value
.Bookmarks("TR_25").Range.Text = ws.Range("Z2").Value
.Bookmarks("TR_26").Range.Text = ws.Range("AA2").Value
.Bookmarks("TR_27").Range.Text = ws.Range("AB2").Value
.Bookmarks("TR_28").Range.Text = ws.Range("AC2").Value
End With
Set objWord = Nothing
End Sub
Now, I need to do essentially the same thing but with charts.
I have a master excel file with multiple charts across three excel sheets.
I need to be able to copy the charts (about 50 in all) into the word document at the bookmarked locations to prepare a series of reports.
I have tried other codes off this site and the web, but have been unable to get the syntax right.
If anyone is able to point me in the right direction, I'd be very grateful. I have asked around in the office but unfortunately nobody has the answer.