Using Word File as Ole Object from Excel VBA

Photo by Adeolu Eletu on Unsplash

I am sure that you know how to open a Word Document from Excel VBA and manipulate this Word document depending on the need within the Excel Workbook. The most basic way to open the Word document from Excel VBA is the code snap below:

Dim WordApp As Word.Application 
Dim WordDoc As Word.Document 
Set WordApp = New Word.Application 
Set WordDoc = WordApp.Documents.Open(ThisWorkbook.Path & "FileName.docx", ReadOnly:=True)

However, this only allows you to open the file from your computer. I am absolutely not a master using the VBA in every area but I have recently discovered an option for opening the Word Document and this way really helped me a lot to share the Workbook that I created with third users and allowed me to manipulate the Word document depending on the information provided in the Excel Sheet. The way I discovered is that you can embed a Word file inside of the Excel Worksheet and use this embedded file to open and manipulate later on.

For instance, imagine that you want to create a Contract Generator Tool that helps people to create the contracts without worrying about changing the context from the Word Document but they only need to select pre-populated options using drop-downs. These drop-downs will decide which text should appear in the final contract and which shouldn’t. In case you share the Excel file containing the VBA code together with a separate Word file, it is sure that at some point the third user will accidentally delete the Word file that you call and open within your Excel VBA code. However, if you embed this Word Document inside of your Excel sheet and hide the column or row containing this document so that the third user can not see it, it is almost impossible that they will remove or change anything inside of the Word document unless you don’t open with ReadOnly option. This way you give them a very easy experience . What they need to do is to open the Excel file you shared, provide the information using drop-downs and click the button that calls your Macro.

For this purpose, what I do is that I embed the Word Document inside of the Worksheet I use to gather the information, name this embedded file to call in VBA (let’s say “embedded_word_file”) and fix it into any cell that is going to be hidden. Afterwards, I use the code below to open this “embedded_word_file” in ReadOnly mode, manipulate the text as necessary and save as PDF, which makes the life of the third user very easy:

Dim WordApp As Word.Application 
Dim WordDoc As Word.Document 
Dim WDObj As Object 
Set WDObj = WORKSHEETCODENAME.OLEObjects("embedded_word_file") WDObj.Activate 
WDObj.Object.Application.Visible = False 
Set WordApp = GetObject(, "Word.Application") 
Set WordDoc = WordApp.ActiveDocument 
WordApp.Visible = False

The text that I marked bold needs to be changed as necessary.

I hope this information will help someone at some point since it literally saved my life during my VBA journey. Now I can hide all the necessary Word files embedded inside of the Excel file, create a macro to manipulate this Word file (change, remove, add some text) and then share with any third user, who can easily use the Macro that I created using only single Excel file.

In case you need any further information or you are curious about the project that I worked on, please leave a comment so that we can discuss.

Cheers

Ozan Tellioglu

Source: https://www.ozan.pl/blog/post/1