PDA

View Full Version : Automation: Help! Access to Word table



wlgzhang
10-17-2004, 01:06 PM
Hi All,

I am developing a BDMS by using Access, have been asked to publish data to a word table, I inserted some bookmarks into this table. I try to pull data form access and insert these data onto mapped bookmarks. When I run the code, I get a runtime error: You entered multiple destinations for a page, line, footnode, endnote or document. I do not have much experience with word, and I may not use right methods to do what I needs. I am stuck here, could anybody help!

the following are some of my code:

Sub ProduceSheetBody(wordApp As Word.Application, DesiredSubject As String, SpecifiedSession As String)

Dim ExamInfoRst As Recordset
Dim ExamInfoStr As String

ExamInfoStr = GetExamMarkingInfo(DesiredSubject, SpecifiedSession)
Set ExamInfoRst = CurrentDb.OpenRecordset(ExamInfoStr)
' MsgBox "Get Exam Marking App Info: " & CStr(ExamInfoRst!FirstName) & _
" " & CStr(ExamInfoRst!LastName) & " " & CStr(ExamInfoRst!ExamCode)
With wordApp.Selection
For Num = 1 To 20
If Not ExamInfoRst.EOF Then
.Goto what:=wdGoToBookmark, Name:="ApplicantName" & Num 'at the second loop I got the error message
.TypeText ExamInfoRst!FirstName & " " & ExamInfoRst!LastName
.Goto what:=wdGoToBookmark, Name:="Code" & Num
.TypeText ExamInfoRst!ExamCode
ExamInfoRst.MoveNext
If Not ExamInfoRst.EOF Then
' MsgBox "Get Exam Marking App Info: " & CStr(ExamInfoRst!FirstName) & " " & CStr(ExamInfoRst!LastName) _
& " " & CStr(ExamInfoRst!ExamCode)
End If
Else
.Goto what:=wdGoToBookmark, Name:="ApplicantName" & Num
.TypeBackspace
.Goto what:=wdGoToBookmark, Name:="Code" & Num
.TypeBackspace
End If
MsgBox "in the loop " & CStr(Num)
Next Num
End With
ExamInfoRst.Close
Set ExamInfoRst = Nothing

End Sub


In Loop 2, I got this Error message: You entered multiple destinations for a page, line, footnode, endnote or document.

And tha following is a incomplete out put to the word template (red color are bookMarks, blue text are the output data )

Examiner: Mullan Subject: Administrative Law

Applicants? Name Code Pass Fail Mark*

wafik Abadir Adm02

ApplicantNam2 Code2

ApplicantName3 Code3

ApplicantName4 Code4



......

ApplicantName19 Code19

ApplicantName20 Code20

Note: when I paste the table, its borders are disappeared.

Any help is much appreciated.

Wei Zhang

Bilby
10-17-2004, 03:26 PM
I basically know only enough about Access to be dangerous so bear with me for a moment.

In this section of your code;
If Not ExamInfoRst.EOF Then
' MsgBox "Get Exam Marking App Info: " & CStr(ExamInfoRst!FirstName) & " " & CStr(ExamInfoRst!LastName) _
& " " & CStr(ExamInfoRst!ExamCode)
End If
Do you get the correct Message?

.Goto what:=wdGoToBookmark, Name:="ApplicantName" & Num
'at the second loop I got the error message

Also, I've found that one of a Bookmarks most irritating habits is to get deleted, particularly if you bookmarked a range rather than a collapsed position. If this error occurs on your second record then maybe your first pass has deleted the bookmark?

wlgzhang
10-17-2004, 05:14 PM
Hi Bilby,
Thank you for your reply.
This line of code is my debug message so that I know my qeury is working fine.

'MsgBox "Get Exam Marking App Info: " & CStr(ExamInfoRst!FirstName) & " " & CStr(ExamInfoRst!LastName) _
& " " & CStr(ExamInfoRst!ExamCode)
Also, I have checked the template after I got the run time error message, the bookmarks are still there. I did Programs with the same fishion to pull the data form access to word letters template (without any table), the book marks work just fine. However, this way does not work in word tables. May be I need to change the code style and try work with table rows, columns, cells? I am not so sure, any suggection?

Wei

TonyJollans
10-18-2004, 01:22 AM
Hi Wei,

You can get this message if the bookmark doesn't exist. I notice in your post you have misspelt ApplicantNam2 - is this just a typo here or is it also a mistake in the document?

wlgzhang
10-18-2004, 06:44 AM
Hi Tony,
The typo is just here, not in the document. I checked.
Since this void function is just a part of the program, I'll check other functions and the driver. May be the cuases of the error is introduced in some where else.

Thank you for your help.

Wei

Bilby
10-18-2004, 07:26 PM
Greetings Wei,

Being self taught I generally look for the easiest method.
My normal method is to store a one row formatted table as AutoText. The macro inserts this table then I just write and move to the next cell, as long as the table has the same number of columns as your data source its ok.

There are a number of variations. If your table has cells with labels such as
Student: <insert data> Class: <insert data>
then you'll have to move two column before a write.

Alternatively you can check the record & column counts, create the required table and then loop through the cells but I generally opt of simplicity if at all possible.

have fun
Bilby

wlgzhang
10-19-2004, 09:40 AM
:yes Hi Bilby,

I tried the same way as you thought. I go to Microsoft website to read the online version of programmer guide and follow the guideline working with objects of rows Columns cells in the table directly. I delete all the book marks in the table. Now I solve my problem and get the output right the way to the document by change my original code to:

Sub ProduceSheetBody(wordApp As Word.Application, DesiredSubject As String, SpecifiedSession As String)
Dim ExamInfoRst As Recordset
Dim ExamInfoStr As String
Dim myCell As Word.Cell
Dim myRow As Integer
Dim myCol As Integer

ExamInfoStr = GetExamMarkingInfo(DesiredSubject, SpecifiedSession)

Set ExamInfoRst = CurrentDb.OpenRecordset(ExamInfoStr)

' MsgBox "Get Exam Marking App Info: " & CStr(ExamInfoRst!FirstName) & " " & CStr(ExamInfoRst!LastName) _
& " " & CStr(ExamInfoRst!ExamCode)
ExamInfoRst.MoveFirst
With wordApp.ActiveDocument
For myRow = 2 To 23

For myCol = 1 To 2
Set myCell = ActiveDocument.Tables(1).Cell(myRow, myCol)

If Not ExamInfoRst.EOF Then

'myCell.Select
'With Selection.Font
'.Size = 14
'.Bold = True
If myCol = 1 Then
myCell.Range.Text = CStr(ExamInfoRst!FirstName & " " & ExamInfoRst!LastName)

Else
myCell.Range.Text = Right(ExamInfoRst!ExamCode, 2)
ExamInfoRst.MoveNext
End If
'End With
Else

myCell.Range.Text = " "
End If

Next myCol
'MsgBox "in the loop " & CStr(myRow)
Next myRow


End With
If ExamInfoRst.EOF Then
ExamInfoRst.close
Set ExamInfoRst = Nothing

End If
End Sub

However, when i try to control the format of the out put, such assign font size, font weight to the data, I get the runtime error. You can see common out code.

Further, I should make the operation more effective by check if the data record set is EOF then exit for loop immediately.

Anyway, I am happy that I get some progress in my work.

I will do some further study on how to control the document format to get more professional look.



Nice to have your help.

Wei

Bilby
10-25-2004, 02:14 PM
Greetings Wei,

Have you tried moving the formatting out of the loop? Unless your changing the format for different cells I'd be inclined to set the style once then loop through the data.