Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formatting Word Templates using Visual Basic Editor

Status
Not open for further replies.
trezraven

trezraven

Programmer
Jan 16, 2007
21
US
I am a beginner programmer and I am creating a template in Word 2007 using Visual Basic Editor. I am pulling information from an Access database. I know this is probably a stupid question, but for the life of me I can't figure out how I get my data to start on a new page and keep the formatting from the first page once a new record is encountered. I have attached a copy of my code. Any help will be greatly appreciated!!!

Code: 
Public blnCancelled As Boolean 

Private Sub btnCancel_Click() 
DCAMacro.blnCancelled = True 
Unload Me 
End Sub 

Private Sub btnGetData_Click() 
Dim conn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim lngConnectionState As Long 
Dim strSQL As String 
Dim Appellant As String 
Dim Appellee As String 
Dim DateMandateReleased As Date 
Dim MandateType As String 

'*****Set up the connection to the database***** 
conn.ConnectionString = "Provider=MSDAORA; Data Source=TSD1; " 
'removed login information 

'*****Open the connecton to the database***** 
conn.Open 
Set rs = New ADODB.Recordset 

'*****Check the state of the database***** 
lngConnectionState = conn.State 
'If lngConnectionState = adStateOpen Then 

'*****Set the datasource***** 
strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo, Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date " & _ 
            "From CMS.V_Macro4mandate " & _ 
            "Where Date_Mandate_Released = '25-April-2006' " 
             
'*****Open the recordset***** 
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic 

'*****Get the data if not end of the record set***** 
rs.MoveFirst 
If Not rs.EOF Then 
    Do Until rs.EOF 
DCAMacro.txtAppellant = rs.Fields("Appellant").Value & " " 
DCAMacro.txtAppellee = rs.Fields("Appellee").Value & " " 
DCAMacro.txtCaseNumber = rs.Fields("CaseNo").Value & " " 
DCAMacro.txtLowerTrib = rs.Fields("LT_Cases").Value & " " 
DCAMacro.txtOpinionDate = rs.Fields("Opinion_Date").Value & " " 


'*****Determine the type of mandate***** 
'If rs.Fields("Mandate_Type").Value = "DK09" Then 
'DCAMacro.txtMandate_Type = "Mandate" 
'End If 
' 
'If rs.Fields("Mandate_Type").Value = "MA1" Then 
'DCAMacro.txtMandate_Type = "Agency Mandate" 
'End If 
' 
'If rs.Fields("Mandate_Type").Value = "MA2" Then 
'DCAMacro.txtMandate_Type = "Circuit Court Mandate" 
'End If 
' 
'If rs.Fields("Mandate_Type").Value = "MA3" Then 
'DCAMacro.txtMandate_Type = "Worker's Compensation Mandate" 
'End If 
             
'If rs.Fields("CaseNo").Value = "1D" Then 
'DCAMacro.txtDistrict = "first district" 
'End If 

             
DCAMacro.Hide 'hide the form so the document can come up 
Set DCAMarco = Nothing 

'*****Add the generic text and properties that apply to all***** 

'*****formatting for heading***** 
Selection.ParagraphFormat.LineSpacingRule = wdLineSpaceSingle 
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter 
Selection.Font.Size = 38 
Selection.Font.Name = "Times New Roman" 
Selection.Font.Bold = wdToggle 
Selection.TypeText Text:="M A N D A T E" 
Selection.Font.Size = 12 
Selection.TypeParagraph 
Selection.TypeText Text:="From" 
Selection.TypeParagraph 
Selection.Font.Size = 14 
Selection.Font.AllCaps = True 
Selection.TypeText Text:="district court of appeal of florida" 
Selection.TypeParagraph 
Selection.TypeText Text:="first district" 
'Selection.TypeText Text:=txtDistrict.Value 
Selection.Font.AllCaps = False 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft 
Selection.Font.Size = 11 
Selection.TypeText Text:="To ~(name), ~(title), ~(agency)" 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeText Text:="WHEREAS, in the certain cause filed in this Court styled:" 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.Font.AllCaps = True 
Selection.TypeText Text:=txtAppellant.Value 
Selection.Font.AllCaps = False 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:="Case No : " 
Selection.TypeText Text:=txtCaseNumber.Value 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeText Text:="v." 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:="Lower Tribunal Case No : " 
Selection.TypeText Text:=txtLowerTrib.Value 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.Font.AllCaps = True 
Selection.TypeText Text:=txtAppellee.Value 
Selection.Font.AllCaps = False 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeText Text:="The attached opinion was issued on " 
Selection.TypeText Text:=rs.Fields("Opinion_Date").Value & " " 
Selection.TypeParagraph 
Selection.TypeText Text:="YOU ARE HEREBY COMMANDED that further proceedings, if required, be had in accordance" 
Selection.TypeParagraph 
Selection.TypeText Text:="with said opinion, the rules of Court, and the laws of the State of Florida." 
Selection.TypeParagraph 
Selection.SelectCurrentTabs 
Selection.Paragraphs.TabIndent (1) 
Selection.TypeText Text:="WITNESS the Honorable " 
Selection.TypeText Text:=rs.Fields("Chief_Judge").Value & " " 
Selection.TypeParagraph 
Selection.SelectCurrentTabs 
Selection.Paragraphs.TabIndent (1) 
Selection.TypeText Text:="of the District Court of Appeal of Florida, First District" 
'Selection.TypeText Text:=txtDistrict.Value 
Selection.TypeText Text:="," 
Selection.TypeParagraph 
Selection.SelectCurrentTabs 
Selection.Paragraphs.TabIndent (1) 
Selection.TypeText Text:="and the Seal of said Court done at Tallahassee, Florida," 
'Selection.TypeText Text:=txtCity.Text 
Selection.TypeParagraph 
Selection.SelectCurrentTabs 
Selection.Paragraphs.TabIndent (1) 
Selection.TypeText Text:="on this " 
Selection.TypeText Text:=rs.Fields("Mandate_Date").Value & " " 
Selection.ClearParagraphAllFormatting 

rs.MoveNext 

    Loop 
End If 
    
MsgBox "Done.", vbOKOnly 


' rs.Close 
' conn.Close 
' Set conn = Nothing 
' Set rs = Nothing 
    
End Sub
 
Sort by date Sort by votes
Have you tried to follow the MailMerge wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
And create your text structure in an actual template file (.DOT) and use that. Use Styles.

Use Range rather than Selection. Further, all those individual, and separate, Selection instructions are an immense waste of resources and code. Your code (for example):
Code: 
Selection.TypeText Text:="To ~(name), ~(title), ~(agency)" 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeText Text:="WHEREAS, in the certain cause filed in this Court styled:" 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.Font.AllCaps = True 
Selection.TypeText Text:=txtAppellant.Value 
Selection.Font.AllCaps = False 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:="Case No : " 
Selection.TypeText Text:=txtCaseNumber.Value 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeText Text:="v." 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:=vbTab 
Selection.TypeText Text:="Lower Tribunal Case No : " 
Selection.TypeText Text:=txtLowerTrib.Value 
Selection.TypeParagraph 
Selection.TypeParagraph 
Selection.TypeParagraph
could be changed to:
Code: 
With Selection
  .TypeText Text:="To ~(name), ~(title), ~(agency)" & _
     vbCrLf & vbCrLf & _
     "WHEREAS, in the certain cause filed in this Court styled:" & _
     vbCrLf & vbCrLf
     .Font.AllCaps = True
  .TypeText Text:=txtAppellant.Value
  .Font.AllCaps = False
  .TypeText Text:=vbTab & bTab & vbTab & vbTab & _
     "Case No : " & txtCaseNumber.Value & _
     vbCrLf & vbCrLf & vbCrLf & _
     "v." & _
     vbTab & bTab & vbTab & vbTab & vbTab & bTab & vbTab & _
     "Lower Tribunal Case No : " & _
     txtLowerTrib.Value & vbCrLf & vbCrLf & vbCrLf
End With

As for making a new page:
Code: 
rs.MoveFirst 
If Not rs.EOF Then 
    Do Until rs.EOF 
       ' yadda yadda all that code
       Selection.InsertBreak Type:=wdPageBreak
       rs.MoveNext 
   Loop 
End If

Finally, use AutoText. Make an AutoText entry (named Whereas) for:

"WHEREAS, in the certain cause filed in this Court styled:"

Then you can simply use Whereas to insert the text.


Gerry
My paintings and sculpture
 
Upvote 0 Downvote
Thanks a lot. I have one other problem. I am trying to pull information from the database using the date that is entered on my form. Here is the SQL I am using:
Code: 
strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo, Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date " & _
            "From CMS.V_Macro4mandate " & _
            "Where Date_Mandate_Released = txtStart.value & "" "
I get error message ORA-00933: SQL command not properly ended. Before getting this error message I would get error message ORA-00904: Invalid column name. Date_Mandate_Released is the name of the column in my database. I want to pull information from the database based on the date that is entered on my form in the txtStart field.

Any advice will be greatly appreciated!
 
Upvote 0 Downvote
I am not an SQL person, so someone else will have to make comments/corrections regarding this.

It sounds like it may be the format of the string from txtStart is incorrect. While .Value should work, have you tried txtStart.Text? It may be that you need some specific format. In which case you can probably do it with Format(txtStart.Text ######) - whatever is the correct format.

Gerry
My paintings and sculpture
 
Upvote 0 Downvote
Thanks Gerry. I modified my SQL to
Code: 
 '*****Set the datasource*****
strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo, Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date " & _
            "From CMS.V_Macro4mandate " & _
            "Where Date_Mandate_Released = '" & txtStart & "'"

'*****Open the recordset*****
rs.Open strSQL, conn, adOpenKeyset, adLockOptimistic

'*****Get the data if not end of the record set*****
rs.MoveFirst
    If Not rs.EOF Then
    Do Until rs.EOF

DCAMacro.txtAppellant = rs.Fields("Appellant").Value & " "
DCAMacro.txtAppellee = rs.Fields("Appellee").Value & " "
DCAMacro.txtCaseNumber = rs.Fields("CaseNo").Value & " "
DCAMacro.txtLowerTrib = rs.Fields("LT_Cases").Value & " "
DCAMacro.txtOpinionDate = rs.Fields("Opinion_Date").Value & " "

Now I am getting error message Runtime error 3021: Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record. If I comment out my rs.MoveFirst the database is searched w/o an error message, but nothing is returned.
 
Upvote 0 Downvote
Resolved. After some playing around I changed my SQL to
Code: 
strSQL = "Select Mandate_Type, Parm1, Agency_Description, CaseNo, Appellant, Appellee, Lt_Cases, Opinion_Date, Chief_Judge, Mandate_Date " & _
            "From CMS.V_Macro4mandate " & _
                "Where Date_Mandate_Released = to_date('" & DCAMacro.txtStart.Value & "', 'mm/dd/yyyy')" & _
                "Order by Appellant "
and that fixed the problem.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

office365
  • Locked
  • Question
app visual basic excel
Replies
4
Views
168
office365
office365
remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
527
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
325
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
588
remeng
remeng
remeng
  • Locked
  • Question
Using Err.Raise question
Replies
2
Views
392
mintjulep
M

Part and Inventory Search

Sponsor

Back
Top