M
mrpauly
Guest
Folks -
Please bear with me as I have not done much Access to Word procedures. What
I'm trying to do is use Access to generate a report in Word based off of
data in a recordset. So far, I got Access to open a word document, create a
table, and populate the data off a recordset. The first time I run the
procedure everything works fine. After closing the document without saving
I run the procedure again and I receive a strange error called "Run Time
Error 462", "The remote server machine does not exist or is unavailable."
This error happens when I get down to adding a table to the word document.
I've also seen another type of error such as object not set..which happens
when I add the table. Once I close the error box and document (it opened
but did not complete the process) everything works fine. Basically, it goes
back and forth from working to not working to working, etc. Personally, I
do not think it is about adding the table because it works fine the first
time....but, then again who am I to say what's correct.
My question is to you folks is: Am I creating the word object and closing it
correctly? In addition, what is causing this problem (switches back and
forth from working to not working), or if you have any suggestions?
Thanks
Paul Millard
San Jose, CA
-------code------------
Public Sub WordExport()
Dim sSQL As String
Dim objWord As Word.Application
Dim db As Database, rst As Recordset
Dim vTitle, vAttendance, vAccount, vPresenter, vIssue, vCommodity As
Variant
Dim vAnnualRevenue, vLocations, vRequest, vRecommendation, vDecision As
Variant
'open recordset
sSQL = "SELECT * FROM tblData"
Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenForwardOnly)
'get header values
vTitle = rst!Title
vAttendance = rst!Attendance
'open word object
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add
objWord.Visible = True
'insert text and table
objWord.Selection.Font.Bold = True
objWord.Selection.TypeText Text:=vTitle
objWord.Selection.Font.Bold = False
objWord.Selection.TypeParagraph
objWord.Selection.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
'format table
With objWord.Selection.Tables(1)
.Columns(1).PreferredWidth = InchesToPoints(2)
.Columns(2).PreferredWidth = InchesToPoints(4.5)
.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Borders(wdBorderTop).LineStyle = wdLineStyleNone
.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With
'insert data into table
objWord.Selection.TypeText Text:="Attendance"
objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
objWord.Selection.TypeText Text:=vAttendance
'insert table
objWord.Selection.MoveDown Unit:=wdLine, Count:=5
objWord.Selection.TypeParagraph
objWord.Selection.Tables.Add Range:=Selection.Range, NumRows:=8,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
With objWord.Selection.Tables(1)
.Columns(1).PreferredWidth = InchesToPoints(2)
.Columns(2).PreferredWidth = InchesToPoints(4.5)
End With
objWord.Selection.TypeText Text:="Account"
... (some more code not shown)
Set objWord = Nothing
Set db = Nothing
End Sub
Please bear with me as I have not done much Access to Word procedures. What
I'm trying to do is use Access to generate a report in Word based off of
data in a recordset. So far, I got Access to open a word document, create a
table, and populate the data off a recordset. The first time I run the
procedure everything works fine. After closing the document without saving
I run the procedure again and I receive a strange error called "Run Time
Error 462", "The remote server machine does not exist or is unavailable."
This error happens when I get down to adding a table to the word document.
I've also seen another type of error such as object not set..which happens
when I add the table. Once I close the error box and document (it opened
but did not complete the process) everything works fine. Basically, it goes
back and forth from working to not working to working, etc. Personally, I
do not think it is about adding the table because it works fine the first
time....but, then again who am I to say what's correct.
My question is to you folks is: Am I creating the word object and closing it
correctly? In addition, what is causing this problem (switches back and
forth from working to not working), or if you have any suggestions?
Thanks
Paul Millard
San Jose, CA
-------code------------
Public Sub WordExport()
Dim sSQL As String
Dim objWord As Word.Application
Dim db As Database, rst As Recordset
Dim vTitle, vAttendance, vAccount, vPresenter, vIssue, vCommodity As
Variant
Dim vAnnualRevenue, vLocations, vRequest, vRecommendation, vDecision As
Variant
'open recordset
sSQL = "SELECT * FROM tblData"
Set db = CurrentDb
Set rst = db.OpenRecordset(sSQL, dbOpenForwardOnly)
'get header values
vTitle = rst!Title
vAttendance = rst!Attendance
'open word object
Set objWord = CreateObject("Word.Application")
objWord.Documents.Add
objWord.Visible = True
'insert text and table
objWord.Selection.Font.Bold = True
objWord.Selection.TypeText Text:=vTitle
objWord.Selection.Font.Bold = False
objWord.Selection.TypeParagraph
objWord.Selection.Tables.Add Range:=Selection.Range, NumRows:=1,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
'format table
With objWord.Selection.Tables(1)
.Columns(1).PreferredWidth = InchesToPoints(2)
.Columns(2).PreferredWidth = InchesToPoints(4.5)
.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
.Borders(wdBorderRight).LineStyle = wdLineStyleNone
.Borders(wdBorderTop).LineStyle = wdLineStyleNone
.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
.Borders.Shadow = False
End With
'insert data into table
objWord.Selection.TypeText Text:="Attendance"
objWord.Selection.MoveRight Unit:=wdCharacter, Count:=1
objWord.Selection.TypeText Text:=vAttendance
'insert table
objWord.Selection.MoveDown Unit:=wdLine, Count:=5
objWord.Selection.TypeParagraph
objWord.Selection.Tables.Add Range:=Selection.Range, NumRows:=8,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed
With objWord.Selection.Tables(1)
.Columns(1).PreferredWidth = InchesToPoints(2)
.Columns(2).PreferredWidth = InchesToPoints(4.5)
End With
objWord.Selection.TypeText Text:="Account"
... (some more code not shown)
Set objWord = Nothing
Set db = Nothing
End Sub