Extract detail from a word doc using excel vba 2

[scootgp200]

Hi, I want to automate a web site where word documents can be completed following a set format and an excel spreadsheet would read the document/s and build web pages basedon the word doc formating but need a pointer in the right direction when it comes to interogating the word file.

I have code to read the doc and copy the story.

Dim Word As New Word.Application
Dim WordDoc As New Word.Document
Dim Doc As String
Dim wb1 As Workbook
Dim Fname2 As String
Doc = "E:\work spreadsheets\test1.doc"
Set WordDoc = Word.Documents.Open(Doc)
Word.Selection.WholeStory
Word.Selection.Copy
Sheets("Sheet1").Select
Range("A2").Select
ActiveSheet.Paste
WordDoc.Close
Word.Quit

However what I want to do is read the doc line by line so I can for example pick up the heading 1 and work with that then the heading 2, paragraphs etc and build the web page. I would then hold the heading types in variables to use in building the site navigation.

Can anyone give me some excel vba code that would get me started.

Thanks

 

[fumei]

Why on earth would you use Excel, to read from a Word file, to create web pages.

Bleeeech.

However....

what I want to do is read the doc line by line

If you mean paragraph by paragraph, then use a Paragraph object. You can test the style used by the paragraph.

"Can anyone give me some excel vba code that would get me started."

If you are asking about Excel VBA code to build a web site and "building the site navigation." - oh boy, not me. Not in my league at all.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[scootgp200]

The plan is that content owners who are not web publishers write the page in word following a set format and these files are then placed into a folder. The spreadsheet is then populated with the file names and when run reads each document and builds an html page based on the content and fomatting. Values held in the spreadsheet would allow metadata and navigation in the form of includes files to be created separatly to be created as well.

Building html files using Excel is not the problem, it's the interaction between excel and word that I have not tried before.

I have done some more work and can now access a word doc and extract the data. But when the code ends it gives an error '91 object variable or with block variable not set' I can't see for looking what is causing this so can anyone help.

Thanks
Bryan

Here is the code:

Sub Main()
Dim oWord As Word.Application
Dim oWdoc As Word.Document
Dim myDocName As String
Dim rowcount As Integer
Dim iParagraph As Word.Paragraph
Dim iCounter As Long

On Error GoTo ErrHandler

Set oWord = New Word.Application

rowcount = 1

Do While Excel.Sheets("Sheet2").Range("A" & rowcount) <> ""

myDocName = Excel.Sheets("Sheet2").Range("A" & rowcount).Value
MsgBox myDocName

Set oWdoc = Word.Documents.Open(myDocName)

Word.Selection.WholeStory
For Each iParagraph In Word.Selection.Paragraphs

If iParagraph.Style = ActiveDocument.Styles("Heading 1") Then
' MsgBox "heading 1"
' Write heading as html
Excel.Sheets("Sheet3").Range("a1000").End(xlUp).Offset(1, 0).Value = "<H1>" & iParagraph.Range.Text & "</H1>"
End If
If iParagraph.Style = ActiveDocument.Styles("Heading 2") Then
' MsgBox "heading 2"
End If
If iParagraph.Style = ActiveDocument.Styles("Heading 3") Then
' MsgBox "heading 3"
End If
If iParagraph.Style = ActiveDocument.Styles("Normal") Then
' MsgBox "normal"
End If

iCounter = iCounter + 1
Excel.Sheets("Sheet1").Select
Range("A" & iCounter).Value = iParagraph.Range.Text
Next iParagraph


Cleanup:
oWdoc.Close
Set oWdoc = Nothing
oWord.Quit
Set oWord = Nothing

rowcount = rowcount + 1

Loop

Exit Sub 'Exit the sub or the Error Handler is invoked

ErrHandler:
Select Case Err
'Err is already defined in Excel
'to hold the numeric code for errors
'but as an error occurs add text to explain solution.
Case 91
MsgBox "There is no active cell"
Case 1005
MsgBox "The worksheet is protected."
Case Else
MsgBox Error(Err) & Err.Number '& Err.Description
End Select

End Sub

 

[TonyJollans]

Your code is like this (without all the detail)

[blue][tt]Create Word Instance

Do
Some stuff with Word
Destroy Word instance
Loop[/tt][/blue]

There are other things wrong - references to "Word" instead of "oWord", for example - that could also cause problems.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

:an error '91 object variable or with block variable not set'"

That is because you are using an object...that is not properly set. In this case, it appears to be a spelling error. I hope you are using Option Explicit!

You have:

    Dim oWord As Word.Application
    Dim oWdoc As Word.Document

but then use:

Set oWdoc = [COLOR=red]Word[/color red].Documents.Open(myDocName)

This should be:

Set oWdoc = [b]oWord[/b].Documents.Open(myDocName)

You MUST use the object as you declared it. In this case, the instance of Word is named oWord...not Word. You are using oWord.

oWord (the Word instance)...not Word.

Further, Word.Selection.WholeStory, will also fail, for the same error, and the same reason.

Selection is a property of the application (not the ActiveDocument), so it should be:

oWord.Selection.WholeStory

However, I would strongly recommend you do NOT use Selection, as it slows things down a lot. You can change:

   Set oWdoc = Word.Documents.Open(myDocName)

   Word.Selection.WholeStory
   For Each iParagraph In Word.Selection.Paragraphs

to:

   Set oWdoc = Word.Documents.Open(myDocName)
   For Each iParagraph In oWdoc.Paragraphs()

You do not need to select the document.


Further, it would be better to use a Select Case, rather than multiple If..Then statements. Each If...Then will execute, regardless of whether a previous one returns true.

   Select Case iParagraph.Style
       Case "Heading 1"
            ' your heading 1 stuff
       Case "Heading 2"
            ' your heading 2 stuff
       Case "Heading 3"
            ' your heading 3 stuff
       Case "Normal"
            ' your Normal stuff
       Case Else
            ' if it is something else?
   End Select

You appear to have a Reference to Word, so why are you using New?

Set oWord = New Word.Application

In any case, Error 91 means exactly what it says. Something somewhere is not fully, properly, Set.

You need to make sure things are fully qualified, where required.

Lastly, please use the TGML code tags when posting code. Thanks.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[fumei]

Couple of other things.

As this is a VBA issue, if you have further questions/issues, please repost into the VBA forum.

Also be aware that with:

Range("A" & iCounter).Value = iParagraph.Range.Text

.Range.Text includes the paragraph mark. That may, or may not, be an issue for you. If it is, then you need to strip off the last character (the paragraph mark).



"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry

 

[scootgp200]

Many Thanks Gerry, your comments have resolved my problems and once I moved the oWord.quit to the end the error 91 went.

This is my first time I have tried getting excel to talk to word through vba so your help is much appreciated.

Bryan

 

[scootgp200]

Just when I thought it was cracked I've noticed something strange.
If Outlook is open and I open the spreadsheet everything works fine.
If I close Outlook and run the code I get this error

"462 The remote server does not exist or is unavailable"

If I do not open Outlook and open the spreadsheet and run the code I get the error

"429 ActiveX component can't create object"

Sub Main()
    Dim oWord As Word.Application
    Dim oWdoc As Word.Document
    Dim myDocName As String
    Dim rowcount As Integer
    Dim iParagraph As Word.Paragraph
    Dim iCounter As Long
    
    On Error GoTo ErrHandler
    
    Set oWord = Word.Application
    
rowcount = 1

Do While Sheets("Sheet2").Range("A" & rowcount) <> ""

myDocName = Sheets("Sheet2").Range("A" & rowcount).Value


Set oWdoc = oWord.Documents.Open(myDocName)

    For Each iParagraph In oWdoc.Paragraphs()
    
       Select Case iParagraph.Style
       Case "Heading 1"
       ' heading 1 stuff
           Sheets("Sheet3").Range("a1000").End(xlUp).Offset(1, 0).Value = "<H1>" & iParagraph.Range.Text & "</H1>"
       Case "Heading 2"
       ' heading 2 stuff
       Case "Heading 3"
       ' heading 3 stuff
       Case "Normal"
       ' Normal stuff
       Case Else
       ' if it is something else?
       End Select

    iCounter = iCounter + 1
    Sheets("Sheet1").Select
    Range("A" & iCounter).Value = iParagraph.Range.Text
    Next iParagraph

Cleanup:
    oWdoc.Close
    Set oWdoc = Nothing

rowcount = rowcount + 1

Loop
    
    oWord.Quit
    Set oWord = Nothing

Exit Sub        'Exit the sub or the Error Handler is invoked.
    
ErrHandler:
        Select Case Err
'Err is already defined in Excel to hold the numeric code for errors
            Case 1000
                MsgBox "Cannot assign a value to the selection."
            Case 1004
                MsgBox Err & " " & Error(Err) & Chr(13) & Chr(13) _
        & "The method you specified cannot be used on the object."
            Case 1005
                MsgBox "The worksheet is protected."
            Case Else
                MsgBox Error(Err) & Err.Number  '& Err.Description
        End Select
    
End Sub

 

[TonyJollans]

I suspect this is the problem:

[blue]Set oWord = Word.Application[/blue]

and you are probably using Word as your Outlook editor.

Use, instead:

[blue]Set oWord = CreateObject("Word.Application")[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[scootgp200]

Your a star Tony, That worked fine. Many thanks to Gerry and yourself for your help.

 

[fumei]

BTW: Tony's suggestion of:

Set oWord = CreateObject("Word.Application")

relates to my question of why you were using New.

Set oWord = New Word.Application

IMO, unless you really need to (and there are circumstances that require it), it is better to use early binding and CreateObject, rather than using New. You may want to do some error trapping with it though. There are a number of examples of how to so that.

"A little piece of heaven
without that awkward dying part."

advertisment for Reese's Peanut Butter Cups (a chocolate/peanut butter confection)

Gerry