I have a table that is laid out like so (and I have no control over how it’s just given to me that way).
Header 1 rule1
Header1 rule 2
Header1 rule 3
Header2 rulea
Header 2 ruleb
Header 2 rulec
Header3 ruled
I’m trying to send a data to the body of a word file. I have a bookmark called “bodystart” I have a query that sorts this table by header first and then by rule name. I can’t use a totals query because there are rare circumstances where the rule is actually in there twice intentionally.
I’m hooking up to a Word file ok and finding the book mark OK and I’m even putting the data out OK. However the rules come out under the incorrect header. Header r1 has rulea, ruleb, rulec under it and header 2 has rule1, rule2, rule 3 under it. Everything else is printing correctly and is spaced correctly.
I’m wondering if my code logic is really right… Have tried a large variety of options.
The query on this table sorts the header column first and the rule column second.
the selection.goto is not my favorite but it's there because if I don't then the line put there dissapears and type paragraph doesn't seem to help. neither did insert paragraph. Insert after doesn't appear to help move things down much either.
My main questions are: Is the logic of the loop really right? Also what's the best way to "move down" after a line has been written so it doesn't get over written or replaced as it seems to be happening.
This is sort of a continuation of a previous thread but a completely different section of the project and nearly the last step!
On Error Resume Next
'Set WordObj = GetObject(, "Word.Application")
'If Err <> 0 Then
Set WordObj = CreateObject("Word.Application")
'End If
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("C:\Documents and Settings\" & MyUserName & "\My Documents\NoticeTemp.DOC")
WordObj.Visible = True
'ActiveDocument.Bookmarks.Item("HeaderBookmark").Range.Text = MyFacilityName
Set DB = CurrentDb
Set RST1 = DB.OpenRecordset("select finalheader, firstofnovnrelanguage from Q_Letter_Generate_NOV_WordOutput", dbOpenDynaset)
RST1.MoveLast
MyRecCount = RST1.RecordCount
RST1.MoveFirst
MyHeader = RST1!finalheader
WordObj.Documents("Noticetemp.doc").Bookmarks("bodystart").Select
WordObj.Documents("Noticetemp.doc").Bookmarks("bodystart").Range.Text = MyHeader & vbCr
Selection.Goto what:=wdGoToLine, which:=wdGoToNext
For n = 1 To MyRecCount
Selection.Range.InsertAfter RST1!FirstOfNOVNRELanguage & vbCr
RST1.MoveNext
If finalheader <> RST1!finalheader Then
finalheader = RST1!finalheader
' Selection.Collapse Direction:=wdCollapseEnd
Selection.Range.InsertAfter RST1!finalheader & vbCr
End If
Next n
ActiveDocument.SaveAs ("C:\Documents and Settings\" & MyUserName & "\My Documents\NEWNOTICE.DOC")
WordObj.Visible = True
WordObj.Quit
Set WordObj = Nothing
MsgBox "File Created"
Set WordObj = Nothing
End Sub
Header 1 rule1
Header1 rule 2
Header1 rule 3
Header2 rulea
Header 2 ruleb
Header 2 rulec
Header3 ruled
I’m trying to send a data to the body of a word file. I have a bookmark called “bodystart” I have a query that sorts this table by header first and then by rule name. I can’t use a totals query because there are rare circumstances where the rule is actually in there twice intentionally.
I’m hooking up to a Word file ok and finding the book mark OK and I’m even putting the data out OK. However the rules come out under the incorrect header. Header r1 has rulea, ruleb, rulec under it and header 2 has rule1, rule2, rule 3 under it. Everything else is printing correctly and is spaced correctly.
I’m wondering if my code logic is really right… Have tried a large variety of options.
The query on this table sorts the header column first and the rule column second.
the selection.goto is not my favorite but it's there because if I don't then the line put there dissapears and type paragraph doesn't seem to help. neither did insert paragraph. Insert after doesn't appear to help move things down much either.
My main questions are: Is the logic of the loop really right? Also what's the best way to "move down" after a line has been written so it doesn't get over written or replaced as it seems to be happening.
This is sort of a continuation of a previous thread but a completely different section of the project and nearly the last step!
On Error Resume Next
'Set WordObj = GetObject(, "Word.Application")
'If Err <> 0 Then
Set WordObj = CreateObject("Word.Application")
'End If
Set WordObj = CreateObject("Word.Application")
WordObj.Documents.Open ("C:\Documents and Settings\" & MyUserName & "\My Documents\NoticeTemp.DOC")
WordObj.Visible = True
'ActiveDocument.Bookmarks.Item("HeaderBookmark").Range.Text = MyFacilityName
Set DB = CurrentDb
Set RST1 = DB.OpenRecordset("select finalheader, firstofnovnrelanguage from Q_Letter_Generate_NOV_WordOutput", dbOpenDynaset)
RST1.MoveLast
MyRecCount = RST1.RecordCount
RST1.MoveFirst
MyHeader = RST1!finalheader
WordObj.Documents("Noticetemp.doc").Bookmarks("bodystart").Select
WordObj.Documents("Noticetemp.doc").Bookmarks("bodystart").Range.Text = MyHeader & vbCr
Selection.Goto what:=wdGoToLine, which:=wdGoToNext
For n = 1 To MyRecCount
Selection.Range.InsertAfter RST1!FirstOfNOVNRELanguage & vbCr
RST1.MoveNext
If finalheader <> RST1!finalheader Then
finalheader = RST1!finalheader
' Selection.Collapse Direction:=wdCollapseEnd
Selection.Range.InsertAfter RST1!finalheader & vbCr
End If
Next n
ActiveDocument.SaveAs ("C:\Documents and Settings\" & MyUserName & "\My Documents\NEWNOTICE.DOC")
WordObj.Visible = True
WordObj.Quit
Set WordObj = Nothing
MsgBox "File Created"
Set WordObj = Nothing
End Sub
