Create Word document from Access?

[Richard M]

Hi everyone, I have the below code working to build a Word document from Access. It works, but I want to justify/distribute the text in the Do loop. I have not found an way to do it on the WEB or the forum. I figure that the code would be like word.App.selection.distribute = true/false.

Can this be done?

Richard

[FONT=Times New Roman][SIZE=3]Public Sub CreateReport()[/SIZE][/FONT]
 
[SIZE=3][FONT=Times New Roman] Dim curDatabase As dao.database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Dim tbl As dao.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Dim strAns As String  'rjm[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Dim strAns2 As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Set curDatabase = CurrentDb[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Set Recordset = curDatabase.OpenRecordset("LandTable", dbOpenSnapshot)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] Dim Owner As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Owner= FilterOwner[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] If Owner = "" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     MsgBox "No Owner selected"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman] Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Recordset.Filter = "[Owner] = '" & Owner & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Set Recordset = Recordset.OpenRecordset[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     Dim wordApp As Object[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     Set wordApp = CreateObject(Class:="Word.Application")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wordApp.Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     wordApp.Documents.Add[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     wordApp.selection.typetext _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "Land Report" & vbNewLine & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "Attached to and made a part of that certain ___________" & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "dated _______, by and between My Company and" & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "______________________" & vbNewLine & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     UCase(Recordset!Owner) & " Owner" & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     UCase(Recordset!County) & " COUNTY, TEXAS" & vbNewLine & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "I" & vbNewLine & vbNewLine & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     "My Comany" & vbNewLine & vbNewLine & vbNewLine[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     Do Until Recordset.EOF[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         Dim INSOFARClause As String 'Only added if Owner Survey is not null[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         If Nz(Recordset![Land Ownership], "") = "" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]             INSOFARClause = ""[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                         Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]             INSOFARClause = _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]                 "INSOFAR AND ONLY INSOFAR as said covers " & Recordset![Land Acres] & " acres, more or less, out of the " & Recordset![Land Ownership] & "." & vbNewLine[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         strAns = IIf(INSOFARClause = "", vbNewLine, "")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         'Recordset![Lease Name]  Recordset![Lessee] & ", as Lessee, covering " &  - strAns2 & ", as Lessor, and " & _[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         strAns2 = "" & Recordset.[Lease Name] & ""[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         'builds Word document[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         wordApp.selection.typetext _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Recordset![Instrument] & " dated " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Format(Recordset![Sale Date]) & ", by and between "[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         wordApp.selection.Font.Bold = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         wordApp.selection.typetext strAns2[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         wordApp.selection.Font.Bold = False[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         wordApp.selection.typetext _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         " as a Lessor and " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Recordset![Lessee] & ", as Lessee, covering " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Format(Recordset![Land Acres]) & " acres, more or less, out of the " & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         Trim(Recordset![Survey]) & ", " & Recordset![County] & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         " County, Texas, and recorded " & Recordset![Record] & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         " of the Official Public Records of " & Recordset![County] & _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         " County, Texas. " & strAns[/FONT][/SIZE]
 
 
[SIZE=3][FONT=Times New Roman]         wordApp.selection.Font.Bold = True[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         wordApp.selection.typetext INSOFARClause & vbNewLine[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         wordApp.selection.Font.Bold = False[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]         Recordset.MoveNext[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]     Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman] End If[/FONT][/SIZE]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[stopher]

Like this...?

WordApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphDistribute

 

[Richard M]

Thanks stopher for the help on distribute.

Is there any site on the web or a book that shows all the wordapp.selections?

Have a great day,

Richard

 

[stopher]

You can take a look here. Note that the methods and properties are show at the bottom of the page. So you can navigate to the and view the ParagraphFormat object.

You can also look at the classes in the Object browser in the VBA window. Type in a search string then drill down through the classes. e.g. type ParagraphFormat and search.

Also, you are using late binding in you code. If you use early binding then the editor will list all the methods/properties as you type (intellisense). See here about late and early binding.

hth
Chris

 

[Richard M]

Thanks again,

I put all this into my favorites and it will inhance my programming experience.

Richard

 

[Richard M]

Well I have put this in my code in various places and I do not think I am doing it right because The paragraph does not format Distribute.

WordApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphDistribute

I will be reading more on the link you gave me because now I have underline and hanging indent.

So Chris, if you have so hints to shorten my learning curve, I sure would appreciate it. But for now its back to the books.

Richard

 

[stopher]

Are you saying your code has caused underline and hanging indent? Or are you asking what the code should be?

I can't see why the code I gave does not work. It tested it and it worked fine. Have you selected a range of text? I'm not sure where in your code you are using the line I gave you. If nothing is selected then I guess you won't see any effect.

You could also try (assuming you know which paragraph and you've defined wordDoc):

WordDoc.Paragraphs(3).Format.Alignment = wdAlignParagraphDistribute


Chris

 

[Richard M]

Chris,​

I am having a problem with selecting then center, underline and hanging indent. I look and tried a lot of things with the link you provided. And because nothing is working. I have been given this code and trying to correct it so the Word document does not have to be edited.​

In the below code the (red) selection has to be centered. The body I want to be hanging Indent with the (orange) text being underlined while the body (green) is indented.​

I used wordapp.selection and activedocument.paragraph (1).tabhangingindent (1) and I get a error message that tabhangingindent is not the correct name or invalid sub etc.​

Don't know what to do next.​

Richard​

[LEFT][FONT=Times New Roman][SIZE=3]Public Sub CreateReport()[/SIZE][/FONT] 
[SIZE=3][FONT=Times New Roman]Dim curDatabase As dao.database[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim tbl As dao.Recordset[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim strAns As String  'rjm[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim strAns2 As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Dim ctrCtr as String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set curDatabase = CurrentDb[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set Recordset = curDatabase.OpenRecordset("LandTable", dbOpenSnapshot)[/FONT][/SIZE] [SIZE=3][FONT=Times New Roman]Dim Owner As String[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]Owner= FilterOwner[/FONT][/SIZE] [SIZE=3][FONT=Times New Roman]If Owner = "" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]     MsgBox "No Owner selected"[/FONT][/SIZE][SIZE=3][FONT=Times New Roman] Else[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]     Recordset.Filter = "[Owner] = '" & Owner & "'"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set Recordset = Recordset.OpenRecordset[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Dim wordApp As Object[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Set wordApp = CreateObject(Class:="Word.Application")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]wordApp.Visible = True[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]     wordApp.Documents.Add[/FONT][/SIZE] [/LEFT]
 
[LEFT][SIZE=3][FONT=Times New Roman][COLOR=darkgreen]wordApp.selection.typetext _[/COLOR][/FONT][/SIZE]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]"Land Report" & vbNewLine & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]"Attached to and made a part of that certain ___________" & vbNewLine & _[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]     "dated _______, by and between My Company and" & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]"______________________" & vbNewLine & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]UCase(Recordset!Owner) & " Owner" & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]UCase(Recordset!County) & " COUNTY, TEXAS" & vbNewLine & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]"I" & vbNewLine & vbNewLine & _[/FONT][/SIZE][/COLOR]
[COLOR=darkgreen][SIZE=3][FONT=Times New Roman]"My Comany" & vbNewLine & vbNewLine & vbNewLine[/FONT][/SIZE][/COLOR] [/LEFT]
 
[LEFT][SIZE=3][FONT=Times New Roman]Do Until Recordset.EOF[/FONT][/SIZE] 
[SIZE=3][FONT=Times New Roman]Dim INSOFARClause As String 'Only added if Owner Survey is not null[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]If Nz(Recordset![Land Ownership], "") = "" Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]         INSOFARClause = ""[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]INSOFARClause = _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]"INSOFAR AND ONLY INSOFAR as said covers " & Recordset![Land Acres] & " acres, more or less, out of the " & Recordset![Land Ownership] & "." & vbNewLine[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]End If[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]strAns = IIf(INSOFARClause = "", vbNewLine, "")[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]         'Recordset![Lease Name]  Recordset![Lessee] & ", as Lessee, covering " &  - strAns2 & ", as Lessor, and " & _[/FONT][/SIZE]     
strCtr = 0 [/LEFT]
 
[LEFT][SIZE=3][FONT=Times New Roman]    strAns2 = "" & Recordset.[Lease Name] & ""[/FONT][/SIZE][/LEFT]
 
[LEFT][SIZE=3][FONT=Times New Roman]'builds Word document[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]wordApp.selection.typetext _[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman][COLOR=sienna]"[U]Lease No. " & ctrCtr & ".[/U]     "[/COLOR] [COLOR=blue]& Recordset![Instrument] & " dated " & _[/COLOR][/FONT][/SIZE][COLOR=blue][SIZE=3][FONT=Times New Roman]         Format(Recordset![Sale Date]) & ", by and between "[/FONT][/SIZE] [/COLOR][/LEFT]
 
[LEFT][COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.Font.Bold = True[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.typetext strAns2[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.Font.Bold = False[/FONT][/SIZE] [/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.typetext _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]" as a Lessor and " & _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]Recordset![Lessee] & ", as Lessee, covering " & _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]Format(Recordset![Land Acres]) & " acres, more or less, out of the " & _[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]         Trim(Recordset![Survey]) & ", " & Recordset![County] & _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]" County, Texas, and recorded " & Recordset![Record] & _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]" of the Official Public Records of " & Recordset![County] & _[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]" County, Texas. " & strAns[/FONT][/SIZE]  [/COLOR][/LEFT]
 
[LEFT][COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.Font.Bold = True[/FONT][/SIZE][/COLOR]
[COLOR=blue][SIZE=3][FONT=Times New Roman]wordApp.selection.typetext INSOFARClause & vbNewLine[/FONT][/SIZE][SIZE=3][FONT=Times New Roman]         wordApp.selection.Font.Bold = False[/FONT][/SIZE][/COLOR] [/LEFT]
 
[LEFT][SIZE=3][FONT=Times New Roman]Recordset.MoveNext[/FONT][/SIZE] [SIZE=3][FONT=Times New Roman]Loop[/FONT][/SIZE] [SIZE=3][FONT=Times New Roman]End If[/FONT][/SIZE] [FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT][/LEFT]

 

[stopher]

The reason nothing is happening when you do the paragraph alignment is because you haven't selected anything.

Select a paragraph like this (selects the first paragraph):
WordApp.ActiveDocument.Paragraphs(1).Range.Select

Then align:
WordApp.Selection.ParagraphFormat.Alignment = wdAlignParagraphJustify


You can select specific words like this (selects the first word):
WordApp.ActiveDocument.Words(1).Select

Also note that you are using a technique called "late binding" to reference the Word Object library. The benefit is you don't have to worry about what version of Word is available. But the downside is that you don't get intelliSense or visibility of the class library in the VBA browser.

Early binding provides Intellisence which helps a lot because when you type WordApp. for example, after typing the dot, you get a list of methods etc for that object. Also with early binding, when you highlight an object and press F1 you'll get help on that object/methods etc. Consider using Early Binding while developing and the switching to late binding when you deploy your project.

hth
Chris

 

[Richard M]

Thanks Chris

I will be working on this Monday or tuesday.

Have a great week.

Richard