Automation Error -- I Need a Guru!

[ieJasonW]

I'm having trouble creating a report, in Word, from Excel. Every other time I try to execute the macro, I get an error 426. I did a search and found someone else with the same problem:

http://www.mrexcel.com/forum/showthread.php?t=124028&highlight=remote+server+machine

And his problem was answered with this:

http://support.microsoft.com/kb/q189618/

My problem is I don't understand where in my code "Visual Basic has established a reference to Word due to a line of code that calls a Word object, method, or property without qualifying it with a Word object variable". I declared the Application and the document. I'm obviously overlooking something because the macro, when it runs, creates 3 different documents, the last one being the one I was hoping to create. haha. Anyways, just hoping someone could clear this all up for me....

You guys rock!

ieJasonW

Sub Report_Click()
Dim WordApp As Object
Dim WordDoc As Object
Dim SaveAsName As String
Dim filename As String
Dim ButtonOnRed As String
Dim ButtonOnGreen As String
Dim ButtonOffBlue As String
Dim ButtonOffRed As String
Dim i As Integer

ButtonOnRed = RGB(205, 92, 92) 'red
ButtonOnGreen = RGB(155, 187, 89) 'green
ButtonOffBlue = RGB(75, 172, 198) 'blue
ButtonOffRed = RGB(205, 92, 92) 'red

'***********Key to Colors for this section**************
'.Font.Color=-738132071 is Light blue
'.Font.Color=-738131969 is Medium blue
'.Font.Color = -738148353 is Medium Dark blue
'.Font.Color=-738164481 is Dark blue

'Start Word and create an oject
Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.Documents.Add(Template:="Normal", NewTemplate:=False, DocumentType:=0)

If Word.Application.Visible = False Then
    Word.Application.Visible = True
End If

'Send commands to Word
With WordApp
    
    .Documents.Add.PageSetup.OddAndEvenPagesHeaderFooter = True
    .Documents.Add.PageSetup.DifferentFirstPageHeaderFooter = True
    
    
    With .Selection
        .Font.Size = 18
        .Font.Bold = False
        'Medium Blue
        .Font.Color = -738131969
        .ParagraphFormat.Alignment = 1
        
        'Format and Enter the Report's Title
        .TypeText Text:="VIRTUAL LAB - Treatment Summary"
        .TypeParagraph
        
        'Enter the Date of the Treatment Summary
        .Font.Size = 12
        .Font.Color = wdAuto
        .ParagraphFormat.Alignment = 0
        .Font.Bold = False
        .TypeText Text:="Date:" & vbTab & vbTab & _
            Format(Date, "mmmm d, yyyy")
            
        'Enter the Well's Name
        .TypeParagraph
        .TypeText Text:="Well Name:" & vbTab
        .TypeParagraph
        
        
        '*************************************************
        '*********Well Conditions and Orientation*********
        '*************************************************
        
        'Begining of the Well Conditions, which include several sub-sections such as
        'Minerology and Temperature, Well Fluids and Tubulars and Orientation
        
        With .Font
            .Name = "+Headings"
            .Size = 14
            .Bold = True
            .Italic = False
            .Underline = wdUnderlineNone
            .UnderlineColor = wdColorAutomatic
            .Smallcaps = True
            .Color = -738148353
            .Subscript = False
            .Spacing = 0.25
            .Scaling = 100
        End With
         
        .ParagraphFormat.SpaceBefore = 24
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Well Conditions and Orientation"
        .TypeParagraph
        
        '***************************************
        '******Mineralogy and Temperature*******
        '***************************************
        
        'This is a series of If...Then statements that checks what buttons are clicked green
        'and then prints a simple statement to the Treatment Summary Word file.
        'Also included are many statements that format the color and allignment of the statements
        'printed to the Treatment Summary Word document.
        
        'Chr$(13) is code for a carriage return
        
        With .Font
            .Color = -738131969
            .Smallcaps = False
            .Size = 12
        End With
        
        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Mineralogy and Temperature"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0
        
        'This formats all following text outputs to the color black
        With .Font
            .Color = wdAutomatic
            .Bold = False
        End With
              
        'This is the flag to see if anything has been printed to the Word file
        i = 0
        
        If Application.ActiveSheet.Shapes("Carbonate").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Carbonate Rock"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("PotassicSS").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potassium Rich Sandstone"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("IronSS").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Iron Rich Sandstone"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("MobileSS").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Sandstone with Mobilizing Clays"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("HgInFormation").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Mercury in Formation"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("OilWet").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Oil Wet Rock Matrix"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("HighTemp").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="In situ Formation Temperature Over 250degF"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("LowTemp").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="In situ Formation Temperature Under 185degF"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("CoolingEffects").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="The job is long enough to cool the wellbore temperature during treatment."
            .TypeParagraph
            i = 1
        End If
        
        'If there is nothing printed to the Word file, then print "None"
        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        'Reset the flag for the next section.
        i = 0
        
        '***************************************
        '**************Well Fluids**************
        '***************************************
        With .Font
            .Color = -738131969
            .Bold = True
        End With

        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Well Fluids"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0
        
        With .Font
            .ColorIndex = wdAuto
            .Bold = False
        End With
        
        If ActiveSheet.Shapes("Oil").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Oil"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Condensate").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Condenstate"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Gas").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Gas"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("H2S").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="H2S"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("PreviousO2Scavenger").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Previous O2 Scavenger"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("VES").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Viscoelastic Surfactant"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("SeaWater").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Sea Water was Injected into the Well"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("CarbonDioxide").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="[CO2]"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("FormateBrines").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Formate Brine"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("XanthamGum").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Xantham Gum"
            .TypeParagraph
            i = 1
        End If

        If ActiveSheet.Shapes("Starch").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Starch"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("ManganeseTetraOxide").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="MnO4"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("IronThreeOxide").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Fe2O3"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("PipeDope").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="PipeDope"
            .TypeParagraph
            i = 1
        End If
    
        If ActiveSheet.Shapes("MillScale").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Mill Scale"
            .TypeParagraph
            i = 1
        End If
        
        'If nothing has printed, then the flag will be 0 and "None" is printed to the Word file.
        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        'Resetting the flag for the next section
        i = 0
        
        '***************************************
        '******Tubulars and Orientation*********
        '***************************************
        With .Font
            .Color = -738131969
            .Bold = True
        End With
        
        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Tubulars and Orientation"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0
        
        With .Font
            .ColorIndex = wdAuto
            .Bold = False
        End With
        
        If ActiveSheet.Shapes("CrBased").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Chromium (tubulars)"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("NiBased").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Nickel (tubulars)"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("LowCSteel").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Low Carbon Steel Tubulars"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("OpenHole").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Completion:Open Hole"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Horizontal").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Completion:Orientation::Horizontal"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Vertical").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Completion:Orientation::Vertical"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Deviated").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Completion:Orientation::Deviated"
            .TypeParagraph
            i = 1
        End If
        
        'If nothing was printed in this section, then "None" is printed to the word file.
        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        'Resetting the flag for the next section.
        i = 0
        
        '***************************************
        '******Existing Formation Damage********
        '***************************************
        
        With .Font
            .Color = -738131969
            .Bold = True
        End With
        
        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Existing Formation Damage"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0

        
        With .Font
            .ColorIndex = wdAuto
            .Bold = False
            
        End With
        
        If ActiveSheet.Shapes("Existing_MudCake").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Barite-based Mud Cake"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_Sludge").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Sludge"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_Wettability").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Undesirable Wettability Change"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_CarbonateScale").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Carbonate Scale"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_SulfateScale").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Sulfate Scale"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_Fines").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Fines Migration"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_SRBs").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Sulfate Reducing Bacteria"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_WaterBlockage").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Water Blockage"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_Emulsions").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Formation Damaging Emulsions"
            .TypeParagraph
            i = 1
        End If
        
        'Printing "None" to the report in case nothing is printed.
        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        'Resetting the flag for the next section.
        i = 0
        
        '*************************************************
        '*******Existing Formation Damage Location********
        '*************************************************
        
        With .Font
            .Color = -738131969
            .Bold = True
        End With
        
        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Existing Damage Location"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0
        
        With .Font
            .Color = wdAutomatic
            .Bold = False
        End With
        
        If ActiveSheet.Shapes("Existing_MatrixShallow").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Matrix Damage is Shallow"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_MatrixDeep").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Matrix Damage is Deep"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_PerfOrSlots").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Damage is Located in the Perfs of Liner Slots"
            .TypeParagraph
            i = 1
        End If
        
        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        i = 0
        
        '**************************************
        '**********Downhole Hardware***********
        '**************************************
        
        With .Font
            .Color = -738131969
            .Bold = True
        End With
        
        .ParagraphFormat.SpaceBefore = 10
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Downhole Hardware"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0

        
        With .Font
            .Color = wdAutomatic
            .Bold = False
        End With
        
        If ActiveSheet.Shapes("Existing_ArtificialLiftSystem").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Downhole Artificial Lift System"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Existing_DownholeTubulars").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Production Tubing"
            .TypeParagraph
            i = 1
        End If

        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        i = 0
        
        '*************************************************
        '***********Formation Damage Concerns************
        '*************************************************
        
        'Begining of the Formation Damage Concerns
        .InsertBreak Type:=wdPageBreak
        
        With .Font
            .Color = -738148353
            .Bold = True
            .Smallcaps = True
            .Size = 14
        End With
        
        .ParagraphFormat.SpaceBefore = 24
        .ParagraphFormat.SpaceAfter = 0
        .TypeText Text:="Formation Damage Concerns"
        .TypeParagraph
        .ParagraphFormat.SpaceBefore = 0
        .ParagraphFormat.SpaceAfter = 0
        
        With .Font
            .ColorIndex = wdAuto
            .Bold = False
            .Smallcaps = False
            .Size = 12
        End With
        
        If ActiveSheet.Shapes("Future_MudCake").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Incomplete Removal of Mud Cake"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_Sludge").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Formation of Sludge"
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_Wettability").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="There is a potential for damaging wettability changes."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_CarbonateScale").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for carbonate scale formation."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_SulfateScale").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for sulfate scale formation."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_IronSulfideScale").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for iron sulfide scale formation."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_HgSulfideScale").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for mercury sulfide scale formation."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_Fines").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for fines migration."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_SRBs").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for Sulfate Reducing Bacteria damage."
            .TypeParagraph
            i = 1
        End If
        
        If ActiveSheet.Shapes("Future_WaterBlockage").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for water blockage-related damage."
            .TypeParagraph
            i = 1
        End If
        
        
        If ActiveSheet.Shapes("Future_Emulsions").TextEffect.FontBold = True Then
            .TypeText Text:=vbTab
            .TypeText Text:="Potential for formation damaging emulsions."
            .TypeParagraph
            i = 1
        End If
            
        If ActiveSheet.Shapes("Future_TubularCorrosion").TextEffect.FontBold = True And _
        ActiveSheet.Shapes("LowCSteel").Fill.ForeColor.RGB = ButtonOnGreen Then
            'This is typed if Tubular Corrosion is highlighted CO2 destroys low-c steel
            .TypeText Text:=vbTab
            .TypeText Text:="There is a potential for dramatic tubular corrosion because high CO2 levels will corrode low carbon steel. Consider Cr-based tubulars instead."
            .TypeParagraph
            i = 1
        ElseIf ActiveSheet.Shapes("Future_TubularCorrosion").TextEffect.FontBold = True And _
        ActiveSheet.Shapes("CrBased").Fill.ForeColor.RGB = ButtonOnGreen Then
            'This is typed if Tubular Corrosion is highlighted but its because of Cr tubulars interacting with HCl
            .TypeText Text:=vbTab
            .TypeText Text:="There is a potential for dramatic tubular corrosion because Cr-based tubulars dissolve in HCl. Maybe consider chelating agents instead."
            .TypeParagraph
            i = 1
        End If

        If i = 0 Then
            .TypeText Text:=vbTab
            .TypeText Text:="None"
            .TypeParagraph
        End If
        
        i = 0
                
    End With
End With

Set WordDoc = Nothing
Set WordApp = Nothing

    
End Sub

 

[Norie]

Can you tell us which of the approx 700 lines of code is causing the error?

Is it intermittent or does it only happen under certain circumstances.

One thing I do notice is that after creating it you never use the wrdDoc reference to the new document(s).

I've seen a lot of that recently and I don't understand it - in Word you are putting text in, applying formats to etc the document not the application.

Just like in Excel you wouldn't usually use Excel.Application or similar, you'd use Workbook/Worksheet etc.

Also you say it creates 3 documents?

Is it meant to do that?

One thing I've just spotted that doesn't really make sense is code like this.

        If ActiveSheet.Shapes("Existing_MatrixShallow").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Matrix Damage is Shallow"
            .TypeParagraph
            i = 1
        End If

Now that looks to me as though the With is referencing a shape on a worksheet, which I assume is an Excel worksheet.

But then there is .TypeParagraph which is a Word method.

 

[Norie]

I've actually just ran the code and you might want to see what the result of this little snippet is that I copied and slightly modified.

Set WordApp = CreateObject("Word.Application")
Set WordDoc = WordApp.documents.Add(Template:="Normal", NewTemplate:=False, DocumentType:=0)
If WordApp.Application.Visible = False Then
    WordApp.Application.Visible = True
End If
'Send commands to Word
With WordApp
    
    .documents.Add.PageSetup.OddAndEvenPagesHeaderFooter = True
    .documents.Add.PageSetup.DifferentFirstPageHeaderFooter = True
    
    MsgBox "You've just created " & WordApp.documents.Count & " Word documents."
End With

That's where you are creating the 'extra' 2 documents.

Another problem is here, and this might be the one that's causing the specific error you are reporting.

If Word.Application.Visible = False Then
    Word.Application.Visible = True
End If

Shouldn't it be WordApp not Word.Application?

 

[ieJasonW]

Awesome!

I knew I had to be something fairly straightforward. I'm not a very advanced user.

As for the error, I didn't even think to set my specific document as visible. In my mind I just wanted to make sure Word was visible. So that's what I typed! Haha. That cleared that up.

Also, I commented out the two lines about the header and footer. Each started with:

WordApp.Documents.Add.PageSetup....

I think those are the two lines that created the unwanted word documents.

Thanks for the help. Really. Those were really driving me nutty.

ieJasonW

 

[ieJasonW]

Can you tell us which of the approx 700 lines of code is causing the error?

Is it intermittent or does it only happen under certain circumstances.

One thing I do notice is that after creating it you never use the wrdDoc reference to the new document(s).

I've seen a lot of that recently and I don't understand it - in Word you are putting text in, applying formats to etc the document not the application.

Just like in Excel you wouldn't usually use Excel.Application or similar, you'd use Workbook/Worksheet etc.

Also you say it creates 3 documents?

Is it meant to do that?

One thing I've just spotted that doesn't really make sense is code like this.

        If ActiveSheet.Shapes("Existing_MatrixShallow").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Matrix Damage is Shallow"
            .TypeParagraph
            i = 1
        End If

Now that looks to me as though the With is referencing a shape on a worksheet, which I assume is an Excel worksheet.

But then there is .TypeParagraph which is a Word method.

Yeah, the With refers to the Word document -- like you noticed with the .TypeParagraph. The reason the shape is referred to is because that's what the program is supposed to do. If a particular shape has been highlighted, then the report needs to include a line or two of information to convey that shape's color. I know its a little confusing, but that's the way I understood it. I'm still starting out, as you can see.

ieJasonW

 

[ieJasonW]

Can you tell us which of the approx 700 lines of code is causing the error?

Is it intermittent or does it only happen under certain circumstances.

One thing I do notice is that after creating it you never use the wrdDoc reference to the new document(s).

I've seen a lot of that recently and I don't understand it - in Word you are putting text in, applying formats to etc the document not the application.

Just like in Excel you wouldn't usually use Excel.Application or similar, you'd use Workbook/Worksheet etc.

Also you say it creates 3 documents?

Is it meant to do that?

One thing I've just spotted that doesn't really make sense is code like this.

        If ActiveSheet.Shapes("Existing_MatrixShallow").Fill.ForeColor.RGB = ButtonOnGreen Then
            .TypeText Text:=vbTab
            .TypeText Text:="Matrix Damage is Shallow"
            .TypeParagraph
            i = 1
        End If

Now that looks to me as though the With is referencing a shape on a worksheet, which I assume is an Excel worksheet.

But then there is .TypeParagraph which is a Word method.

I wanted to address your comments about WordDoc, as well. I did refer to it -- its just hard to notice. Most of my code is individual if..then statements. But they are all couched within one With statement that spans almost the entire 700 lines.

ieJasonW

 

[Norie]

Eh, the with doesn't refer to the Word document it refers to the Word applicaton =WordApp.

Now I don't know exactly how Word works for this sort of thing but in Excel you would refer to the worksheet or workbook not the application.

Maybe in Word you'll be fine with the application, especially if you only have 1 document to refer to.

By the way, and you can totally ignore this, have you considered some other way of doing this.

The way I understand it is that you want to generate some kind of report in Word based on the colours of some shapes on a worksheet in Excel.

Is that about right?

If it is have you considered rather than all those Ifs checking the button colour/status putting the data the report needs somewhere?

For example on a spare worksheet or two.

You could have a worksheet for each section (you do have sections don't you) of the report which only have the required text.

Does that make any sense?

 

[ieJasonW]

Hrm. That's actually a great question. I never even thought about it that way. It would be pretty easy to have a second worksheet that contains a column for yes/no, and a column of the report relevant info for that button. Then, write a macro to test for a shape's color and change the status of the yes/no column accordingly. Then, after all the button colors have been examined, I could just copy the contents of all the 'yes' cells into the report. That, actually, might be a ton easier than what I'm doing right now.

This is what I get for just jumping in head first! Haha.

Thanks for the thoughts though. I really do appreciate you guys' advice. There's no way I could do this project for graduate school without this board!

ieJasonW