Hello everyone,
I am developing an excel document that prints a table over to a memo on word. I currently have the whole process figured out except one thing... how to enable the word reference from script. I know that you can go into the tools and enable it, however this worksheet will be used across the whole business enterprise and some users will not know how to do that (nor should need to know how to access the VBA scripting). All of the computers run the same Office 365 and receive the same firmware/software patches through the IT infrastructure.
In an ideal scenario the worksheet would automatically enable the references similar to setting the environment in C++, however I would settle for being able to write it into the same VBA scripting as the print function. I currently have the worksheet set up with three buttons: (with associated macros) one to move certain items over to the new sheet, one to clear the whole sheet, and one to print to the memo template in word. I will paste all of the codes below (maybe someone can help me clean it up and make it better?) but the big question is how to set the reference for Word (Microsoft Word 16.0 Object Library). Thank you in advance for any help!!
Copy to Word Code:
Copy to sheet:
I am developing an excel document that prints a table over to a memo on word. I currently have the whole process figured out except one thing... how to enable the word reference from script. I know that you can go into the tools and enable it, however this worksheet will be used across the whole business enterprise and some users will not know how to do that (nor should need to know how to access the VBA scripting). All of the computers run the same Office 365 and receive the same firmware/software patches through the IT infrastructure.
In an ideal scenario the worksheet would automatically enable the references similar to setting the environment in C++, however I would settle for being able to write it into the same VBA scripting as the print function. I currently have the worksheet set up with three buttons: (with associated macros) one to move certain items over to the new sheet, one to clear the whole sheet, and one to print to the memo template in word. I will paste all of the codes below (maybe someone can help me clean it up and make it better?) but the big question is how to set the reference for Word (Microsoft Word 16.0 Object Library). Thank you in advance for any help!!
Copy to Word Code:
VBA Code:
Sub CopyToWord_GO()
'need to add how to add the reference*********
Dim wApp As Word.Application
Dim wDoc As Word.Document
Set wApp = CreateObject("Word.Application")
wApp.Visible = True
LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
Set wDoc = wApp.Documents.Open("C:\Users\CB\Desktop\EXCEL TEST\Memo Test\Go memo Temp.dotx")
'copy what you want to paste from excel
Range("A2:E" & LastRow).Copy
'select the word range you want to paste into
wDoc.Bookmarks("Table1").Select
'and paste the clipboard contents
wApp.Selection.Paste
End Sub
Copy to sheet:
VBA Code:
Sub copy18estay()
Dim r As Range, C As Range
With Worksheets("18E")
Set r = Range(.Range("E4"), .Range("E200").End(xlDown))
For Each C In r
If WorksheetFunction.IsNumber(C) Or WorksheetFunction.IsText(C) Then
Range(.Cells(C.Row, "A"), .Cells(C.Row, "E")).Copy
Else
GoTo nextc
End If
With Worksheets("STAY")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
nextc:
Next C
End With
Application.CutCopyMode = False
'adjusts column width
With Worksheets("STAY").Columns("E")
.ColumnWidth = 21
End With
'auto fit the rows
With Worksheets("STAY")
Rows("3:300").EntireRow.AutoFit
End With
'set all alignment
With Worksheets("STAY").Columns("A:E")
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Last edited by a moderator: