crvazquez954
New Member
- Joined
- Jul 9, 2019
- Messages
- 23
Hi,
I have a spreadsheet I am preparing for my boss that requires me to review column B (B28:B47) on the "Summary" sheet, determine which rows have data and transfer a portion of the rows (columns B, C, D, E & F) to a word template called "Test.docm". I have limited experience with VBA but I know I need a macro code in order to do this. Can anyone help me write an easy code that does this for me? Below is what I have so far but I would like to only export rows with data, change the formatting to match that of Word when it's imported and have word doc pop up with the report and stay open rather than pasting the table and keeping the doc closed.
Any help would be appreciated.
Sub Export_Table_Word()
'Name of the existing Word doc.
Const stWordReport As String = "Test.docm"
'Word objects.
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdbmRange As Word.Range
'Excel objects.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnReport As Range
'Initialize the Excel objects.
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Summary")
Set rnReport = wsSheet.Range("BidTable")
'Initialize the Word objects.
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(wbBook.Path & "" & stWordReport)
Set wdbmRange = wdDoc.Bookmarks("InsertHere").Range
'If the macro has been run before, clean up any artifacts before trying to paste the table in again.
On Error Resume Next
With wdDoc.InlineShapes(1)
.Select
.Delete
End With
On Error GoTo 0
'Turn off screen updating.
Application.ScreenUpdating = False
'Copy the report to the clipboard.
rnReport.Copy
'Select the range defined by the "Report" bookmark and paste in the report from clipboard.
With wdbmRange
.Select
.PasteSpecial Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False
End With
'Save and close the Word doc.
With wdDoc
.Save
.Close
End With
'Quit Word.
wdApp.Quit
'Null out your variables.
Set wdbmRange = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
'Clear out the clipboard, and turn screen updating back on.
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
MsgBox "The report has successfully been " & vbNewLine & _
"transferred to " & stWordReport, vbInformation
End Sub
I have a spreadsheet I am preparing for my boss that requires me to review column B (B28:B47) on the "Summary" sheet, determine which rows have data and transfer a portion of the rows (columns B, C, D, E & F) to a word template called "Test.docm". I have limited experience with VBA but I know I need a macro code in order to do this. Can anyone help me write an easy code that does this for me? Below is what I have so far but I would like to only export rows with data, change the formatting to match that of Word when it's imported and have word doc pop up with the report and stay open rather than pasting the table and keeping the doc closed.
Any help would be appreciated.
Sub Export_Table_Word()
'Name of the existing Word doc.
Const stWordReport As String = "Test.docm"
'Word objects.
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim wdbmRange As Word.Range
'Excel objects.
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnReport As Range
'Initialize the Excel objects.
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Summary")
Set rnReport = wsSheet.Range("BidTable")
'Initialize the Word objects.
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(wbBook.Path & "" & stWordReport)
Set wdbmRange = wdDoc.Bookmarks("InsertHere").Range
'If the macro has been run before, clean up any artifacts before trying to paste the table in again.
On Error Resume Next
With wdDoc.InlineShapes(1)
.Select
.Delete
End With
On Error GoTo 0
'Turn off screen updating.
Application.ScreenUpdating = False
'Copy the report to the clipboard.
rnReport.Copy
'Select the range defined by the "Report" bookmark and paste in the report from clipboard.
With wdbmRange
.Select
.PasteSpecial Link:=False, _
DataType:=wdPasteMetafilePicture, _
Placement:=wdInLine, _
DisplayAsIcon:=False
End With
'Save and close the Word doc.
With wdDoc
.Save
.Close
End With
'Quit Word.
wdApp.Quit
'Null out your variables.
Set wdbmRange = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
'Clear out the clipboard, and turn screen updating back on.
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
MsgBox "The report has successfully been " & vbNewLine & _
"transferred to " & stWordReport, vbInformation
End Sub
