microsoft word automation through vb 6

I want to create a document in microsoft word 2003 using vb 6.

The document will consist mainly of information from an access database.

The general form of the report will be a table that shows Trip names, trip dates, passenger names for each trip and so on.

Does anyone have any coding samples I can use to use to figure out the Word.Application and Word.Document objects, and a general idea how to code this?

Take a look at the VB section of our Office Development FAQs (at the top of the Office Development forum)

I am getting an odd type match error in a button_click event. the compiler points to the sub routine header...

Private Sub cmdViewReport_Click()
Dim wordApp As Word.Application
Dim wordDoc As Word.Document
Dim wasSuccessful As Boolean

Set wordApp = CreateObject("Word.Application")
Set wordDoc = CreateObject("Word.Document")


Set wordDoc = wordApp.Documents.Add
OptimizePerformance (wordDoc)
wasSuccessful = AddTable(wordDoc, wordApp, 5, 5)
End Sub

All of the microsoft word objects are being passed around ByRef, so am I making the sub-rountine calls correctly?

I am posting the optimize function as well, where I think the problem may really be...


Private Sub OptimizePerformance(ByRef doc As Word.Document)
doc.Windows(1).View = wdNormalView
With doc.Application
.Options.Pagination = False
.ScreenUpdating = False
End With
End Sub

I got around the type mismatch problem by removing the need to pass the Word variables ByRef....

now I have another problem.... the line is colored red

the code to add the table fails.... not sure why


here is the call to the function and the function itself


Dim wasSuccessful As Boolean

wasSuccessful = AddTable(5, 5)

Public Function AddTable(ByVal iRows As Integer, ByVal iCols As Integer) As Boolean


Dim oApp As Word.Application
Dim odoc As Word.Document

Dim oTable As Word.Table

Dim oRowHeader As Word.Row

Dim X As Integer


Set oApp = CreateObject("Word.Application")
Set odoc = CreateObject("Word.Document")


Set odoc = oApp.Documents.Add

'open an exisiting document

'Set oDoc = oApp.Documents.Open("C:\Document1.doc")



odoc.Activate

'optimization code
odoc.Windows(1).View = wdNormalView
With odoc.Application
.Options.Pagination = False
.ScreenUpdating = False
End With


'Move to the end of the document

oApp.Selection.Move Unit:=wdStory

oApp.Selection.TypeParagraph

oApp.Selection.TypeParagraph

'Add a new table

Set oTable = odoc.Tables.Add(Range:=Selection.Range, NumRows:=iRows, NumColumns:=iCols, _
DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitWindow)

'Format the table and populate

With oTable

If .Style <> "Table Grid" Then

.Style = "Table Grid"

End If

.ApplyStyleHeadingRows = True

.ApplyStyleLastRow = True

.ApplyStyleFirstColumn = True

.ApplyStyleLastColumn = True

'Populate and format the table header

Set oRowHeader = oTable.Rows.Item(1)

oRowHeader.Shading.Texture = wdTextureNone

oRowHeader.Shading.ForegroundPatternColor = wdColorAutomatic

oRowHeader.Shading.BackgroundPatternColor = wdColorGray25

For X = 1 To iCols

'Bold on for the cell

.Cell(1, X).Range.Font.Bold = True

.Cell(1, X).Range.Text = "ColumnHeader" & X

Next

End With

'Clean up and leave document open

Set oRowHeader = Nothing

Set oTable = Nothing
Set odoc = Nothing

Set oApp = Nothing



End Function

You might want to check this FAQ in particular...
http://www.vbforums.com/showthread.php?t=402093

No need to create a document object like that as it should be done as shown in my FAQ.

do you see a reason why the creation of the table object fails? the code I am playing with came mostly from the helpful examples in your faq

thanks,

Try reading up on the Visual Basic For Applications, which comes with MS-Office 95 or later. By the way what kind of MS-Office are you using?

i am using office word 2003. I am going to create the report by hand this one time and macro it

selection.range is not fully qualified, it need to be qualified with your document or application object
also all the word constants may not be valid unless you have set them as constants in your code, otherwise use literals

Quote:

---

Try reading up on the Visual Basic For Applications, which comes with MS-Office 95 or later

---

word 95 did not have vba, it had wordbasic, vba started at word 97

I started fresh by first going into microsoft word and setting up a macro. then I manually created a sample report with sample data.

I am now trying to convert the vba macro that word gave me and turn it into a vb function.

The last time I tried to test it, the code ran through the debugger without error, but the document never displayed.

I am not sure why...

Also I will need to replace the hard coded data with ADO once I get it to display. Once I insert the ADO, the number of tables in the output could be much greater then in my sample.

I am attaching the function I am working on that is based on the VBA script.

Code:

---

Private Function horizons() As Boolean
On Error GoTo ErrorHandler
'
' horizons Macro
' Macro recorded 1/10/2009 by Acer
    'code added by shawn fultz
   
    Dim wordDoc As Word.Document
    Dim wordApp As Word.Application
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
   
    Set wordApp = CreateObject("Word.Application")
    Set wordDoc = wordApp.Documents.Add
   
   
    With wordApp
        .Selection.Font.Size = 24
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Selection.TypeText Text:="HORIZONS WITHOUT BOUNDARIES"
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Activity Report"
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
        .Selection.Font.Size = 12
        .ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
    End With
    With wordApp.Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    With wordApp
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Trip Name Bermuda"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=2
        .Selection.TypeText Text:="Trip Begin Date"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Trip End Date"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Trip Name"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Cost"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.TypeText Text:="$1,500.00"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=11
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Bermuda"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=9
        .Selection.TypeText Text:="09/22/2009"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="09/29/2009"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.Font.Size = 24
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Selection.TypeText Text:="Clients"
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.Delete Unit:=wdCharacter, Count:=1
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveRight Unit:=wdCharacter, Count:=7
        .Selection.TypeParagraph
        .Selection.Font.Size = 12
        .ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
    End With
    With wordApp.Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    With wordApp
        .Selection.TypeText Text:="Client First Name"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Client Last Name"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Client has PCA"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Amount Still Owed"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=10
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=3
        .Selection.TypeText Text:="Shawn "
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Fultz"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Yes"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="$0.00"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=3
        .Selection.TypeText Text:="John"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Smith"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="No"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="$750.00"
    End With
    horizons = True
Exit Function

ErrorHandler:
wordDoc.Close

Set wordDoc = Nothing
Set wordApp = Nothing
Set adoConn = Nothing
Set adoRS = Nothing
horizons = False
End Function

---

I added [code] tags to your post to make it easier to read ;)

Did you try showing it? :D

Code:

---

Private Function horizons() As Boolean
On Error GoTo ErrorHandler
'
' horizons Macro
' Macro recorded 1/10/2009 by Acer
    'code added by shawn fultz
   
    Dim wordDoc As Word.Document
    Dim wordApp As Word.Application
    Dim adoConn As ADODB.Connection
    Dim adoRS As ADODB.Recordset
   
    Set wordApp = CreateObject("Word.Application")
    Set wordDoc = wordApp.Documents.Add
   
    wordApp.Visible = True ' <-- ADDED

    With wordApp
        .Selection.Font.Size = 24
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Selection.TypeText Text:="HORIZONS WITHOUT BOUNDARIES"
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Activity Report"
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
        .Selection.Font.Size = 12
        .ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
    End With
    With wordApp.Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    With wordApp
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Trip Name Bermuda"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=2
        .Selection.TypeText Text:="Trip Begin Date"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Trip End Date"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Trip Name"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Cost"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.TypeText Text:="$1,500.00"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=11
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="Bermuda"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=9
        .Selection.TypeText Text:="09/22/2009"
        .Selection.MoveRight Unit:=wdCharacter, Count:=1
        .Selection.TypeText Text:="09/29/2009"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.Font.Size = 24
        .Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
        .Selection.TypeText Text:="Clients"
        .Selection.MoveUp Unit:=wdLine, Count:=1
        .Selection.Delete Unit:=wdCharacter, Count:=1
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveRight Unit:=wdCharacter, Count:=7
        .Selection.TypeParagraph
        .Selection.Font.Size = 12
        .ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=3, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
    End With
    With wordApp.Selection.Tables(1)
        If .Style <> "Table Grid" Then
            .Style = "Table Grid"
        End If
        .ApplyStyleHeadingRows = True
        .ApplyStyleLastRow = True
        .ApplyStyleFirstColumn = True
        .ApplyStyleLastColumn = True
    End With
    With wordApp
        .Selection.TypeText Text:="Client First Name"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Client Last Name"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Client has PCA"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Amount Still Owed"
        .Selection.MoveLeft Unit:=wdCharacter, Count:=10
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=3
        .Selection.TypeText Text:="Shawn "
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Fultz"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Yes"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="$0.00"
        .Selection.MoveDown Unit:=wdLine, Count:=1
        .Selection.MoveLeft Unit:=wdCharacter, Count:=3
        .Selection.TypeText Text:="John"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="Smith"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="No"
        .Selection.MoveRight Unit:=wdCell
        .Selection.TypeText Text:="$750.00"
    End With
    horizons = True
Exit Function

ErrorHandler:
wordDoc.Close

Set wordDoc = Nothing
Set wordApp = Nothing
Set adoConn = Nothing
Set adoRS = Nothing
horizons = False
End Function

---

2 questions.....1) How do I get the document to show? And 2) how do I use code tags when posting code here? is it <code> and /<code>?

1. Make sure you dont have any previous debugging attempts or word runing in the background. Look in taskmanager.
2. make sure you add that line of code.
3. Use[code] tags not <code>

:)

the code is failing when it tries to insert the first table....it is triggering the error handler....also in my error handler if i try to use err.raise I get a compiler error of "with block or variable not set"

Here is the code that is failing.......

Code:

---

With WordApp
.ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=2, NumColumns:= _
            4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
            wdAutoFitFixed
end With

---

and here is my current error handler

Code:

---

ErrorHandler:
'Err.Raise Err.Number,,Err.Description    compiler error

Set wordDoc = Nothing
Set wordApp = Nothing
Set adoConn = Nothing
Set adoRS = Nothing
horizons = False

---

You didnt fully qualify the Selection object.

that worked thank you for the help.


now I will replace the hard coded data with records from the database and that code will be part of some while loops. I will be careful with that, but now I am well on my way to creating a report that last week I never thought I could do

Cool, glad you have it working now :)

Ps, dont forget to Resolve your thread from the Thread Tools menu and select Mark Thread as Resolved. ;)

one last question...say I have a function like this

private function insertTable(byVal numColumns as integer, ByVal NumColumns as integer, ByRef wordApp as Word.Application)

how do I call it?

thanks,

Like...

Code:

---

InsertTable 3, 5, moApp

---

But if its Private then it will only be visible to the Form, Module or Class it is located in.

in your error handler you can put

vb Code:

msgbox "error number " & err.number & vbnewline & err.description
debug.assert false  ' cause code to break in ide only