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

--

--

--

https://www.ozan.pl/aboutme/

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Creating Enemy Explosions

Create a Transition

NKN Monthly Report November 2020

LeetCode | Reverse Integer | Google Interview Questions | Geek Hacker

Snowflake: 10 Things Every Snowflake Admin Should be Doing to Optimize Credits

Create a pdf2text Microservice Using Python

Pass By Object Reference in Python

Testnet Weekly Tech Update.

Top 9 smartphone hacks you should know!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Ozan Tellioğlu

Ozan Tellioğlu

https://www.ozan.pl/aboutme/

More from Medium

How to draw with SQL?

You don’t need VBA to create Excel reports in 2022. Use Microsoft PowerBI Report Builder instead.

What is an index in SQL?

Career in Microsoft Power BI