Change Word table format from Excel-CodePudding

I have a macro in Excel that creates a Word where some Excel tables are copied, and I have this code for format changing:

    Set WordTable = myDoc.Tables(i)
    With WordTable
        .AutoFitBehavior (wdAutoFitWindow)
        .Shading.Texture = wdTextureNone
        .Shading.BackgroundPatternColor = wdColorWhite
        .Range.Font.TextColor = wdColorBlack
        .Range.ParagraphFormat.SpaceAfter = 0
    End With

Everything works properly except when the reference "Microsoft Word Object Library" is not set. In this case, the shading turns black for some reason. Is there any way to solve it, apart from set this reference?

The problem is that this macro is part of a bigger Excel program that the user installs in their own PCs, so shouldn't be able to work with VBA.

CodePudding user response：

The issue is, if you use that code in Excel and "Microsoft Word Object Library" is not set, Excel does not know the Word constants wdAutoFitWindow, wdTextureNone, wdColorWhite and wdColorBlack. Instead Excel will treat them as variables and since you did not initialize them each of it has the value 0.

Make sure you use Option Explicit so you get notified if you use something that is not defined!

I recommend always to activate Option Explicit:
In the VBA editor go to Tools › Options › Require Variable Declaration.

To solve the issue, you need to either replace them with their actual value (you can find the values for the enumerations here: Enumerations (Word)) or define them as constants in Excel.

Option Explicit

Public Sub Example
    Const wdAutoFitWindow As Long = 1
    Const wdTextureNone As Long = 0
    Const wdColorWhite As Long = 16777215
    Const wdColorBlack As Long = 0

    Set WordTable = myDoc.Tables(i)
    With WordTable
        .AutoFitBehavior wdAutoFitWindow
        .Shading.Texture = wdTextureNone
        .Shading.BackgroundPatternColor = wdColorWhite
        .Range.Font.TextColor = wdColorBlack
        .Range.ParagraphFormat.SpaceAfter = 0
    End With
End Sub

You can also define them outside the scope of a procedure if you plan to use them in multiple procedures/functions. Make sure to use Option Explicit on top of every module to ensure all variables are declared properly or you will quickly run into issues again.