462 Error (1 Viewer)

[mdnuts]

When I run this code to create a word doc - the first time no problem. Second time it pops the Error 462 error. Research on it shows it's usually when ActiveDocument is referenced without specifying the word.Application first. All that is fine I only have that in one location and it's referenced properly.

Where it is failing is the first width declaration (I copied the code up to the end of the Width settings).

  Dim oWord As Word.Application
  Dim oDoc As Word.Document
  Dim oTable As Table
  Dim oRow As Word.row
  
  
  Set oWord = New Word.Application
  oWord.Visible = True
  Set oDoc = oWord.Documents.Add
    ' Initiate the table
  Set oTable = oDoc.Tables.Add(oDoc.Paragraphs(1).Range, 1, 5)
  oTable.Borders.Enable = True
  oTable.Borders.OutsideColor = wdColorGreen
  oTable.Borders.InsideColor = wdColorGreen

    ' Set the head of the table
  oTable.Cell(1, 1).Range.Text = strWhatEmployee
  oTable.Cell(1, 1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
  oTable.Cell(2, 1).Width = InchesToPoints(1.75)
  oTable.Cell(2, 2).Width = InchesToPoints(0.8)
  oTable.Cell(2, 3).Width = InchesToPoints(2.66)
  oTable.Cell(2, 4).Width = InchesToPoints(0.66)
  oTable.Cell(2, 5).Width = InchesToPoints(1)

and I can't figure out why - any other references to oTable.Cell or oTable.Rows do not error - just that first width declaration. (Just for additional information, each time the word document is closed before trying to generate a second time).

any ideas?

 

[Rx_]

The automation of Word from Excel will be the same as for MS Access.
Just found this site to explain Early/Late binding. Do you have a reference set to MS Word in the menu Tools References - in the code module?

http://www.globaliconnect.com/excel...word-from-excel-using-vba&catid=79&Itemid=475

I suspect the Add(oDoc.Paragraphs(1).Range, 1, 5) is not working.
The first time run, it might create a temp reference. The second time, the orphaned object gets in the way.
Just suspecting the newer version of Word requires a bookmark object.
Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, 3, 5)
The Word object model changed from when I used it. Here is some code to evaluate that includes a Table being added. Add it into a subroutine, it should allow you to set break points and step through the code.

    Dim oWord As Word.Application    
Dim oDoc As Word.Document    
Dim oTable As Word.Table    
Dim oPara1 As Word.Paragraph, oPara2 As Word.Paragraph    
Dim oPara3 As Word.Paragraph, oPara4 As Word.Paragraph    
Dim oRng As Word.Range    
Dim oShape As Word.InlineShape    
Dim oChart As Object    
Dim Pos as Double    'Start Word and open the document template.    
Set oWord = CreateObject("Word.Application")    oWord.Visible = True    
Set oDoc = oWord.Documents.Add        'Insert a paragraph at the beginning of the document.    
Set oPara1 = oDoc.Content.Paragraphs.Add    
oPara1.Range.Text = "Heading 1"    
oPara1.Range.Font.Bold = True    
oPara1.Format.SpaceAfter = 24    '24 pt spacing after paragraph.    
oPara1.Range.InsertParagraphAfter        'Insert a paragraph at the end of the document.    '** \endofdoc is a predefined bookmark.    
Set oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)    
 
oPara2.Range.Text = "Heading 2"    oPara2.Format.SpaceAfter = 6    
oPara2.Range.InsertParagraphAfter        'Insert another paragraph.    Set 
oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)    
oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:"    
oPara3.Range.Font.Bold = False    
oPara3.Format.SpaceAfter = 24    
oPara3.Range.InsertParagraphAfter        'Insert a 3 x 5 table, fill it with data and make the first row    'bold,italic.    
Dim r As Integer, c As Integer    
Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, 3, 5)    
oTable.Range.ParagraphFormat.SpaceAfter = 6    
For r = 1 To 3        
For c = 1 To 5            
oTable.Cell(r, c).Range.Text = "r" & r & "c" & c        
Next    
Next    
oTable.Rows(1).Range.Font.Bold = True    
oTable.Rows(1).Range.Font.Italic = True        'Add some text after the table.    'oTable.Range.InsertParagraphAfter    
Set oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)    
oPara4.Range.InsertParagraphBefore    
oPara4.Range.Text = "And here's another table:"    
oPara4.Format.SpaceAfter = 24    
oPara4.Range.InsertParagraphAfter        'Insert a 5 x 2 table, fill it with data and change the column widths.    
Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, 5, 2)    
oTable.Range.ParagraphFormat.SpaceAfter = 6    
For r = 1 To 5        
For c = 1 To 2            
oTable.Cell(r, c).Range.Text = "r" & r & "c" & c        
Next    
Next    
oTable.Columns(1).Width = oWord.InchesToPoints(2)   'Change width of columns 1 & 2.    
oTable.Columns(2).Width = oWord.InchesToPoints(3)        'Keep inserting text. When you get to 7 inches from top of the    'document, insert a hard page break.    
Pos = oWord.InchesToPoints(7)    
oDoc.Bookmarks("\endofdoc").Range.InsertParagraphAfter    
Do        
Set oRng = oDoc.Bookmarks("\endofdoc").Range        
oRng.ParagraphFormat.SpaceAfter = 6        
oRng.InsertAfter "A line of text"        
oRng.InsertParagraphAfter    
Loop While Pos >= oRng.Information(wdVerticalPositionRelativeToPage)    
oRng.Collapse (wdCollapseEnd)    
oRng.InsertBreak wdPageBreak    
oRng.Collapse wdCollapseEnd    
oRng.InsertAfter "We're now on page 2. Here's my chart:"    
oRng.InsertParagraphAfter        'Insert a chart and change the chart.    
Set oShape = oDoc.Bookmarks("\endofdoc").Range.InlineShapes.AddOLEObject( _        ClassType:="MSGraph.Chart.8", FileName _        :="", LinkToFile:=False, DisplayAsIcon:=False)    
Set oChart = oShape.OLEFormat.Object    
oChart.charttype = 4 'xlLine = 4    
oChart.Application.Update    
oChart.Application.Quit    '... If desired, you can proceed from here using the Microsoft Graph     'Object model on the oChart object to make additional changes to the    'chart.    
oShape.Width = oWord.InchesToPoints(6.25)   
 
oShape.Height = oWord.InchesToPoints(3.57)        'Add text after the chart.    
Set oRng = oDoc.Bookmarks("\endofdoc").Range    
oRng.InsertParagraphAfter    
oRng.InsertAfter "THE END."

 

[mdnuts]

yours is definitely working. I'll have to step through yours and mine and see what's up. or more likely rebuild mine.

 

[Rx_]

Please, Be sure and post your solution as well. There isn't a lot of Word vba for Tables. This post will probably get a fair share of hits for future searches. You will help someone else out and get a THANKS from me.
A THANKS from me and a US Dollar will get you.... never mind, it use to get you a cup of coffee. Now it is just loose change.

 

[The_Doc_Man]

I'm going to suggest that if you really want to quit the Word application, you might help yourself by also doing a

SET OWord = NOTHING

(just after your OWord.QUIT)

Based on that book "Everything I Needed to Know I Learned in Kindergarten." In particular, this set of rules applies: If you take it out, put it back. If you make a mess, clean it up. If you leave it out you'll just trip over it.

 

[mdnuts]

I've narrowed the problem down. It's farting in a similar manner as my original post when also trying to use Rx's code.

The problem specifically is when you create rows in the table then enter a recordset loop to create more rows. Or vice versa if you create rows in the loop and later try to add rows once you are out of the recordset loop. It just doesn't like it. Which makes me believe it's got to do with properly referring to the table itself. Been trying all manner of methods to not much success yet.

Still working it.

 

[mdnuts]

actually I was completely wrong in my assumption. What it was was the needed reference to the word object when setting InchesToPoints.

This

  'set column widths
  oTable.Columns(1).Width = InchesToPoints(1.75)
  oTable.Columns(2).Width = InchesToPoints(0.8)
  oTable.Columns(3).Width = InchesToPoints(2.66)
  oTable.Columns(4).Width = InchesToPoints(0.66)
  oTable.Columns(5).Width = InchesToPoints(1)

Turned to this works.

    'set column widths
  oTable.Columns(1).Width = oWord.InchesToPoints(1.75)
  oTable.Columns(2).Width = oWord.InchesToPoints(0.8)
  oTable.Columns(3).Width = oWord.InchesToPoints(2.66)
  oTable.Columns(4).Width = oWord.InchesToPoints(0.66)
  oTable.Columns(5).Width = oWord.InchesToPoints(1)

A full detail of what works is as follows.

The following pulls information from a variable access table, creates a word document with it, formatting the top two rows with header type information, sets the fonts, merges the top row and sets column widths.

  Dim sSQL As String
  Dim strDupTable As String
  Dim strWhatEmployee
  
  'set temporary table names
  strDupTable = "tblEMPDup_" & [TempVars]![sUserName]
  ' get employee name information
  strWhatEmployee = DLookup("[First Name] & ' ' & [Last Name]", "tblEmployee", "[employeeID]=" & [Forms]![Home].[txtEmployeeID])

  'prepare the word doc
  Dim oWord As Word.Application
  Dim oDoc As Word.Document
  Dim oTable As Word.Table
  'setting the Dims for the recordset fields
  Dim fldProject As DAO.Field, fldPriority As DAO.Field, fldHistory As DAO.Field, fldTime As DAO.Field, fldStatus As DAO.Field
  Dim r As Integer 'row count
  Dim strRowCount As Long
  
  Set oWord = CreateObject("Word.Application")
  oWord.Visible = True
  Set oDoc = oWord.Documents.Add
 
  'pull from the above inserted table
  sSQL = "Select * from " & strDupTable & " order by fldOrder"
  Set rs = db.OpenRecordset(sSQL)
  rs.MoveFirst
  rs.MoveLast
  strRowCount = (rs.RecordCount + 2) 'getting the table row count and adding two for the two header rows
  rs.MoveFirst
  r = 0 'current row counter  
  'insert word table however many records long plus two and 5 wide.
  Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, strRowCount, 5)
  oTable.Range.ParagraphFormat.SpaceAfter = 6
  oTable.Range.Style = oWord.ActiveDocument.Styles("No Spacing")
  oTable.Range.Font.Name = "Times New Roman"
 
  'start the recordset loop reserving the top two rows for header type stuff.
  r = 0
  Do Until rs.EOF
    Set fldProject = rs.Fields("fldTitle")
    Set fldPriority = rs.Fields("TheFieldPriority")
    Set fldHistory = rs.Fields("FirstOfHistory")
    Set fldTime = rs.Fields("fldTimeSpent")
    Set fldStatus = rs.Fields("fldStatus")

    r = r + 1  'just dictate where we are at with it all
    If r = 1 Then ' top header row
      oTable.Cell(r, 1).Range.Text = strWhatEmployee
      oTable.Cell(r, 2).Range.Text = ""
      oTable.Cell(r, 3).Range.Text = ""
      oTable.Cell(r, 4).Range.Text = ""
      oTable.Cell(r, 5).Range.Text = ""
      r = r + 1
    End If
    If r = 2 Then 'second header row
      oTable.Cell(r, 1).Range.Text = "Project"
      oTable.Cell(r, 2).Range.Text = "Priority"
      oTable.Cell(r, 3).Range.Text = "Status / Issues"
      oTable.Cell(r, 4).Range.Text = "Time"
      oTable.Cell(r, 5).Range.Text = "Suspense / Status"
      r = r + 1
    End If
     ' and all the data rows
      oTable.Cell(r, 1).Range.Text = fldProject
      oTable.Cell(r, 2).Range.Text = fldPriority
      oTable.Cell(r, 3).Range.Text = fldHistory
      oTable.Cell(r, 4).Range.Text = fldTime
      oTable.Cell(r, 5).Range.Text = fldStatus
    rs.MoveNext
  Loop
  
  'Formatting
  'set column widths
  oTable.Columns(1).Width = oWord.InchesToPoints(1.75)
  oTable.Columns(2).Width = oWord.InchesToPoints(0.8)
  oTable.Columns(3).Width = oWord.InchesToPoints(2.66)
  oTable.Columns(4).Width = oWord.InchesToPoints(0.66)
  oTable.Columns(5).Width = oWord.InchesToPoints(1)
  
  'put a border on it and color
  oTable.Borders.Enable = True
  oTable.Borders.OutsideColor = wdColorGreen
  oTable.Borders.InsideColor = wdColorGreen
  
  'merge top row color top two rows.
  oTable.Rows(1).Cells.Merge
  oTable.Rows(1).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
  oTable.Rows(1).Range.Font.Bold = True
  oTable.Rows(1).Range.Shading.BackgroundPatternColor = RGB(182, 215, 168)
  oTable.Rows(2).Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
  oTable.Rows(2).Range.Shading.BackgroundPatternColor = RGB(182, 215, 168)
  
  'exit out of everything
  Set oTable = Nothing
  Set oDoc = Nothing
  oWord.Quit
  Set oWord = Nothing
  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing

 

[Rx_]

Once you get this refined, I suggest you post it to this forum's Code Example.
There just are not that many good examples of Word with what you have accomplished.
You will probably get a lot of "internet Hits" for what that is worth.

Thanks for posting your code and marking it solved.