Export to Word from Access

[mor]

Hello Everyone,

I'm trying to create an export to word but am having some trouble. I keep getting the following error;

Run time error '91'; variable or object of With block not defined.

This error occurs at Set TblWord = docNew.Tables.Add(Selection.Range, 3, 5)

I have a feeling its 'Selection' that is causing the problem but I can't work it out!

Any help would be much appreciated!

Thanks,

MOR

Dim AppWord As Word.Application
Dim DocWord As Word.Document
Dim TblWord As Word.Table
Dim MyRange As Range
Dim intX As Integer
Dim intY As Integer


Set AppWord = New Word.Application
AppWord.Visible = True

Set DocWord = AppWord.Documents.Add
DocWord.Activate

Set TblWord = docNew.Tables.Add(Selection.Range, 3, 5)
 With tblNew
 For intX = 1 To 3
 For intY = 1 To 5
 .Cell(intX, intY).Range.InsertAfter "Cell: R" & intX & ", C" & intY
 Next intY
 Next intX
 .Columns.AutoFit
 End With
 
 Set TblWord = Nothing
 Set DocWord = Nothing
 Set AppWord = Nothing

End Sub

 

[Rx_]

This worked for me running from an Excel module
There was one variable not defined, you can take it from there.

Public Sub TestWord()
Dim AppWord As Word.Application
Dim DocWord As Word.Document
Dim TblWord As Word.Table
Dim MyRange As Range
Dim intX As Integer
Dim intY As Integer
Set AppWord = New Word.Application
AppWord.Visible = True

Set DocWord = AppWord.Documents.Add
DocWord.Activate

'Set TblWord = docNew.Tables.Add(Selection.Range, 3, 5)
Set TblWord = Word.Selection.Tables.Add(Word.Selection.Range, 3, 5)
 'With tblNew  ' This was not defined so I had to comment it out
 'For intX = 1 To 3
 'For intY = 1 To 5
 '.Cell(intX, intY).Range.InsertAfter "Cell: R" & intX & ", C" & intY
 'Next intY
 'Next intX
 '.Columns.AutoFit
 'End With
 
 Set TblWord = Nothing
 Set DocWord = Nothing
 Set AppWord = Nothing

End Sub

 

[mor]

Thanks so much!

Now I seem to get every other run through of the code runtime error 462, which I know is when the objects from word aren't properly referenced if running from another instance of VBA (Access in this case). However I though I had properly referenced everything here!

Thanks for your help.

MOR

Dim AppWord As Word.Application
Dim DocWord As Word.Document
Dim TblWord As Word.Table
Dim MyRange As Range
Dim intX As Integer
Dim intY As Integer
Set AppWord = New Word.Application
AppWord.Visible = True

Set DocWord = AppWord.Documents.Add
DocWord.Activate

'Set TblWord = docNew.Tables.Add(Selection.Range, 3, 5)
Set TblWord = Word.Selection.Tables.Add(Word.Selection.Range, DealCount, 2)
With TblWord
For intX = 1 To 2
For intY = 1 To DealCount
.Cell(intY, intX).Range.InsertAfter "Cell: R" & intX & ", C" & intY
Next intY
Next intX
.Columns.AutoFit
End With
 
 Set TblWord = Nothing
 Set DocWord = Nothing
 Set AppWord = Nothing

 

[Rx_]

I think this code may help you. You can figure out the variable substitution. The line numbers in the code may help with questions.

This is from MS Excel but the code (except for the Excel chart) can be used from Access. I think it contains the Word cells you are trying to use.

To step through this code, in a new blank Excel, I pasted this in a New general Module (not a workbook module). In tools reference, I used Microsoft Word 2010.

In the Immediate (debug) window just enter the name of the subroutine.

Option Explicit

Public Sub RxIsTheWord()
       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 and instance of Word and open the document template.
10        Set oWord = CreateObject("Word.Application")
20        oWord.Visible = True  ' can be commented out - and added to the end
30        Set oDoc = oWord.Documents.Add
          
          'Insert a paragraph at the beginning of the document.
40        Set oPara1 = oDoc.Content.Paragraphs.Add
50        oPara1.Range.Text = "Heading 1"
60        oPara1.Range.Font.Bold = True
70        oPara1.Format.SpaceAfter = 24    '24 pt spacing after paragraph.
80        oPara1.Range.InsertParagraphAfter
          
          'Insert a paragraph at the end of the document.
          '** \endofdoc is a predefined bookmark.
90        Set oPara2 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)
100       oPara2.Range.Text = "Heading 2"
110       oPara2.Format.SpaceAfter = 6
120       oPara2.Range.InsertParagraphAfter
          
          'Insert another paragraph.
130       Set oPara3 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)
140       oPara3.Range.Text = "This is a sentence of normal text. Now here is a table:"
150       oPara3.Range.Font.Bold = False
160       oPara3.Format.SpaceAfter = 24
170       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
180       Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, 3, 5)
             ' this is probably where you want to substitute the MS Access data to enter into the Word document - first row is bolded to act as a table. 
190       oTable.Range.ParagraphFormat.SpaceAfter = 6
200       For r = 1 To 3
210           For c = 1 To 5
220               oTable.Cell(r, c).Range.Text = "r" & r & "c" & c
230           Next
240       Next
250       oTable.Rows(1).Range.Font.Bold = True
260       oTable.Rows(1).Range.Font.Italic = True
          
          'Add some text after the table.
          'oTable.Range.InsertParagraphAfter
270       Set oPara4 = oDoc.Content.Paragraphs.Add(oDoc.Bookmarks("\endofdoc").Range)
280       oPara4.Range.InsertParagraphBefore
290       oPara4.Range.Text = "And here's another table:"
300       oPara4.Format.SpaceAfter = 24
310       oPara4.Range.InsertParagraphAfter
          
          'Insert a 5 x 2 table, fill it with data and change the column widths.
320       Set oTable = oDoc.Tables.Add(oDoc.Bookmarks("\endofdoc").Range, 5, 2)
330       oTable.Range.ParagraphFormat.SpaceAfter = 6
340       For r = 1 To 5
350           For c = 1 To 2
360               oTable.Cell(r, c).Range.Text = "r" & r & "c" & c
370           Next
380       Next
390       oTable.Columns(1).Width = oWord.InchesToPoints(2)   'Change width of columns 1 & 2.
400       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.
410       Pos = oWord.InchesToPoints(7)
420       oDoc.Bookmarks("\endofdoc").Range.InsertParagraphAfter
430       Do
440           Set oRng = oDoc.Bookmarks("\endofdoc").Range
450           oRng.ParagraphFormat.SpaceAfter = 6
460           oRng.InsertAfter "A line of text"
470           oRng.InsertParagraphAfter
480       Loop While Pos >= oRng.Information(wdVerticalPositionRelativeToPage)
490       oRng.Collapse (wdCollapseEnd)
500       oRng.InsertBreak wdPageBreak
510       oRng.Collapse wdCollapseEnd
520       oRng.InsertAfter "We're now on page 2. Here's my chart:"
530       oRng.InsertParagraphAfter
          
          'Insert a chart and change the chart.
540       Set oShape = oDoc.Bookmarks("\endofdoc").Range.InlineShapes.AddOLEObject( _
              ClassType:="MSGraph.Chart.8", Filename _
              :="", LinkToFile:=False, DisplayAsIcon:=False)
550       Set oChart = oShape.OLEFormat.Object
560       oChart.ChartType = 4 'xlLine = 4
570       oChart.Application.Update
580       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.
590       oShape.Width = oWord.InchesToPoints(6.25)
600       oShape.Height = oWord.InchesToPoints(3.57)
          
          'Add text after the chart.
610       Set oRng = oDoc.Bookmarks("\endofdoc").Range
620       oRng.InsertParagraphAfter
630       oRng.InsertAfter "THE END of a Demo."

End Sub

Try this in Excel code module first, set a break point and single step through. My guess is that you will understand how to apply this to your situation.

 

[Ashfaque]

Thanks for sharing excellent code Rx.

Regards,
Ashfaque