Word Formatting by Excel VBA

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • 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;

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

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

    So, any help please?

  • 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.

  • 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?

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

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!