Word Formatting by Excel VBA

Magdoulin · Jul 17th 2018

Hi guys,
How is everything?
I hope you’ll be able to assist me with the following
I’m trying to write a code which will open for me an existing word file, create for me a new one, and make the background colour of this newly created file as black and its font colour as automatic.
So far I’ve accomplished the first and the second step with the following code;

Code

Sub TrialHere()
Dim wkb As Workbook
Dim WordApp As Object
Dim WordDoc As Object
Dim objWord
Dim objDoc
Set WordApp = CreateObject("word.Application")
Set WordDoc = WordApp.Documents.Open(Filename:="File Path", ReadOnly:=False)
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Add
objWord.Visible = True
Set WordApp = Nothing
Set WordDoc = Nothing
End Sub

Display More

I’ve tried to execute the third step by the following code but it didn’t work though:

Code

ActiveDocument.Background.Fill.ForeColor.ObjectThemeColor = _
wdThemeColorText1
ActiveDocument.Background.Fill.ForeColor.TintAndShade = 0#
ActiveDocument.Background.Fill.Visible = msoTrue
ActiveDocument.Background.Fill.Solid

So, any help please?

XenoCode · Jul 17th 2018

Your code is difficult to follow. Why create both the WordApp &; objWord objects? They both refer to Word and you only need 1 instance of Word. Not sure, either, why you load one workbook and then add a new blank workbook. You don't have any interaction between them - perhaps there is in code you didn't post, but it's a little confusing as it stands.

The 2nd code snippet referencing ActiveDocument cannot work in Excel. You need to add a reference to whichever Word object it relates to.

You also use a Word constant (wdThemeColorText1). This does not exist in Excel unless you have added a reference to Word using the Project/References dialog or else declared it as a variable/constant with it's underlying value (13) in Excel. If you did add a reference to Word, why use late binding (CreateObject) rather than early binding?

And finally, Word will only display a page background colour in Web view... so maybe it does work after all but you can;t see it with the current view in Word.

Magdoulin · Jul 17th 2018

Well, using the two variables because I'm opening the existing file and creating a new blank one
I've tried to use the same variable to execute both but it didn't work
that's why I added this extra one.

So, you mean that formatting the word file through the Excel VBA isn't doable?

XenoCode · Jul 18th 2018

I didn't say it can't be done, it just may not be able to be done in the way you expected.

Code

Sub TrialHere()


' Using Late Binding so Word Constants defined here
Const wdWebView As Long = 6
Const wdThemeColorText1 As Long = 13
Const wdWindowStateMaximize As Long = 1


Dim wkb As Workbook
Dim WordApp As Object
Dim WordDoc As Object
Dim WordDoc2 As Object


Set WordApp = CreateObject("word.Application")
WordApp.Visible = True


' Open first document
Set WordDoc = WordApp.Documents.Open(Filename:="c:\temp\x.docx", ReadOnly:=False)
' Then add a new, blank document to the same instance of Word
Set WordDoc2 = WordApp.Documents.Add
' and maximise
WordApp.ActiveWindow.WindowState = wdWindowStateMaximize


' Set the formatting - the default view is 'Print Layout' (Usually - not bothering
' to code for others), so these changes will not be visible
With WordDoc2
.Background.Fill.ForeColor.ObjectThemeColor = wdThemeColorText1
.Background.Fill.ForeColor.TintAndShade = 0#
.Background.Fill.Visible = True
.Background.Fill.Solid
End With


' Just to slow things down so you can see the before/after effects of the change to View type
MsgBox "Click 'OK' to change to Web Layout view...", vbInformation, "Web layout"
WordApp.ActiveWindow.View.Type = wdWebView
MsgBox "Et voilà...", vbInformation, "Web layout"


' All done.
Set WordApp = Nothing
Set WordDoc = Nothing
Set WordDoc2 = Nothing


End Sub

Display More

And there is absolutely no problem in loading 2 documents to the same instance of Word. If you are moving information between them it makes things simpler all around.

Word Formatting by Excel VBA

Participate now!

Share