Solved

Need help utilizing left indent in Word VBA - after export from Access

Posted on 2009-04-14
5
1,566 Views
Last Modified: 2012-05-06
In my Access database, I'm currently exporting information to Word to create a report (so that it can be combined with other departments, etc. - could not use reporting in Access). Anyway, I've almost got it completed (thanks to much help from EE...since I know nothing about Word VBA), but I'm stuck on one issue:

I need the table cells for the Description [WeeklyUpdDes] to indent if there is a second line of text.

I've perused the site, found some information but did not know how to apply it in my case (using .LeftIndent = InchesToPoints(0.11)).  I also recorded a macro and tried to utilize that information but it failed.  I'm either on the totally wrong track or not placing the code in the right place because nothing happens w.r.t. to indention.  Any help is most appreciated. My current code is pasted below.
Private Sub cmdNewExport_Click()
 

Dim Word 'the Word application

Dim doc 'the Word document

Dim db As DAO.Database

Dim rs As DAO.Recordset

Dim sql As String

Set db = CurrentDb

Dim objTable As Word.Table

Dim objRange As Word.Range

Dim intTable As Integer, intRow As Integer

 

sql = "SELECT tbl_WeeklyCategory.CategoryName, tbl_WeeklyUpdate.WeeklyUpdName, tbl_WeeklyUpdate.WeeklyUpdDes"

sql = sql & " FROM tbl_WeeklyUpdate INNER JOIN tbl_WeeklyCategory ON tbl_WeeklyUpdate.WCatID = tbl_WeeklyCategory.WCatID"

sql = sql & " WHERE tbl_WeeklyUpdate.Date = Date()"

sql = sql & " ORDER BY tbl_WeeklyUpdate.WCatID, tbl_WeeklyUpdate.WeeklyUpdName;"

 

Set rs = db.OpenRecordset(sql)

 

Set Word = CreateObject("word.application")

Set doc = Word.Documents.Add

  

With Word

    .Visible = True

    Set doc = .Documents.Open("c:\PDE_Weekly_Report.doc", , False)

End With

 

doc.GoTo what:=wdGoToBookmark, Name:="Start"
 

    With Word.Selection
 

        .TypeText Text:="Pre-Development Evaluation Weekly Summary"

        .TypeParagraph

        .TypeText Text:="Week Ending "

        doc.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _

        "DATE  \@ ""dddd d MMM, yyyy"" ", PreserveFormatting:=True

        .MoveUp Unit:=wdLine, Count:=1, Extend:=wdExtend

        .MoveLeft Unit:=wdCharacter, Count:=33, Extend:=wdExtend

        .Font.Bold = wdToggle

        .ParagraphFormat.Alignment = wdAlignParagraphCenter

        .MoveDown Unit:=wdLine, Count:=1

    

    End With
 

doc.Tables.Add Range:=Word.Selection.Range, NumRows:=1, NumColumns:= _

        2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _

        wdAutoFitFixed
 

    With doc.PageSetup

        .LineNumbering.Active = False

        .Orientation = wdOrientPortrait

        .TopMargin = InchesToPoints(0.4)

        .BottomMargin = InchesToPoints(0.4)

        .LeftMargin = InchesToPoints(0.8)

        .RightMargin = InchesToPoints(0.6)

        .Gutter = InchesToPoints(0)

        .HeaderDistance = InchesToPoints(0.5)

        .FooterDistance = InchesToPoints(0.5)

        .PageWidth = InchesToPoints(8.5)

        .PageHeight = InchesToPoints(11)

    End With

        

    With doc.Tables(1)

        .Columns(1).SetWidth 100, wdAdjustNone

        .Columns(2).SetWidth 400, wdAdjustNone

    End With

        

    With Word.Selection.Tables(1)

        If .Style <> "Table Grid" Then

            .Style = "Table Grid"

        End If

        .ApplyStyleHeadingRows = True

        .ApplyStyleLastRow = True

        .ApplyStyleFirstColumn = True

        .ApplyStyleLastColumn = True

    End With

  

   While Not rs.EOF

        If Not tmpCat = rs.Fields("CategoryName") Then

            With Word.Selection

                .SplitTable

                .Font.Size = 11

                .TypeParagraph

                .Font.Size = 11

                .MoveDown

                .Style = ActiveDocument.Styles("Categories")

                .Font.Size = 11

                .SelectRow

                .Shading.Texture = wdTextureNone

                .Shading.ForegroundPatternColor = wdColorAutomatic

                .Shading.BackgroundPatternColor = wdColorLightYellow

                .TypeText Text:=rs.Fields("CategoryName").Value

                .MoveRight Unit:=wdCell

                .MoveRight Unit:=wdCell

            End With

            tmpCat = rs.Fields("CategoryName")

        End If

        

        With Word.Selection

            .Style = ActiveDocument.Styles("Projects")

            .Font.Bold = wdToggle

            .Font.Size = 11

            .SelectRow

            .Shading.Texture = wdTextureNone

            .Shading.ForegroundPatternColor = wdColorAutomatic

            .Shading.BackgroundPatternColor = wdColorWhite

            .TypeText Text:=rs.Fields("WeeklyUpdName").Value

            .MoveRight Unit:=wdCell

        End With

        

        With Word.Selection

                With Selection.ParagraphFormat

                    .LeftIndent = InchesToPoints(0.25)

                End With

            .Style = ActiveDocument.Styles("Desc")

            .Font.Size = 11

            .TypeText Text:=rs.Fields("WeeklyUpdDes").Value

            .MoveRight Unit:=wdCell

        End With

        rs.MoveNext

        

    Wend

    

    intTable = ActiveDocument.Tables.Count

    intRow = ActiveDocument.Tables(intTable).Rows.Count

    doc.Tables(intTable).Rows(intRow).Delete

    

    Selection.HomeKey Unit:=wdStory

    

    Word.Dialogs(wdDialogFileSaveAs).Show
 

End Sub

Open in new window

0
Comment
Question by:setalley
  • 2
  • 2
5 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24147653
I am not a word expert, but try this in place of your code begining with line 111, and see if it helps.

Set objRange = ActiveDocument.Paragraphs(3).Range
Set objTable = ActiveDocument.Tables.Add(Range:=oRg, numrows:=2, numcolumns:=3)

intTable = ActiveDocument.Tables.Count
intRow = ActiveDocument.Tables(intTable).Rows.Count

With Word.Selection
    If intRow > 1 Then
        objTable.Rows.LeftIndent = InchesToPoints(0.25)
    End If
    .Style = ActiveDocument.Styles("Desc")
    .Font.Size = 11
    .TypeText Text:=rs.Fields("WeeklyUpdDes").Value
    .MoveRight Unit:=wdCell
End With
0
 

Author Comment

by:setalley
ID: 24150361
I'm sure it's something I've done wrong (I'm not a 'coder') but I'm receiving the following error message when I try using your code:

Run-time error '424'
Object required
Debug highlights the following line:
Set objTable = ActiveDocument.Tables.Add(Range:=oRg, numrows:=2, numcolumns:=3)
        With Word.Selection

            .Style = ActiveDocument.Styles("Projects")

            .Font.Bold = wdToggle

            .Font.Size = 11

            .SelectRow

            .Shading.Texture = wdTextureNone

            .Shading.ForegroundPatternColor = wdColorAutomatic

            .Shading.BackgroundPatternColor = wdColorWhite

            .TypeText Text:=rs.Fields("WeeklyUpdName").Value

            .MoveRight Unit:=wdCell

        End With

        

        'Placed your code here

                

        Set objRange = ActiveDocument.Paragraphs(2).Range

        Set objTable = ActiveDocument.Tables.Add(Range:=oRg, numrows:=2, numcolumns:=3)
 

        intTable = ActiveDocument.Tables.Count

        intRow = ActiveDocument.Tables(intTable).Rows.Count
 

    With Word.Selection

        If intRow > 1 Then

        objTable.Rows.LeftIndent = InchesToPoints(0.25)

    End If

        .Style = ActiveDocument.Styles("Desc")

        .Font.Size = 11

        .TypeText Text:=rs.Fields("WeeklyUpdDes").Value

        .MoveRight Unit:=wdCell

    End With

    Wend

    

    

    'Continued on with the remainder of my existing code

    

    intTable = ActiveDocument.Tables.Count

        With Word.Selection

            .Style = ActiveDocument.Styles("Projects")

            .Font.Bold = wdToggle

            .Font.Size = 11

            .SelectRow

            .Shading.Texture = wdTextureNone

            .Shading.ForegroundPatternColor = wdColorAutomatic

            .Shading.BackgroundPatternColor = wdColorWhite

            .TypeText Text:=rs.Fields("WeeklyUpdName").Value

            .MoveRight Unit:=wdCell

        End With

           

        

        

        

        

        Set objRange = ActiveDocument.Paragraphs(2).Range

        Set objTable = ActiveDocument.Tables.Add(Range:=oRg, numrows:=2, numcolumns:=3)
 

        intTable = ActiveDocument.Tables.Count

        intRow = ActiveDocument.Tables(intTable).Rows.Count
 

    With Word.Selection

        If intRow > 1 Then

        objTable.Rows.LeftIndent = InchesToPoints(0.25)

    End If

        .Style = ActiveDocument.Styles("Desc")

        .Font.Size = 11

        .TypeText Text:=rs.Fields("WeeklyUpdDes").Value

        .MoveRight Unit:=wdCell

    End With

    Wend

    

    

    

    

    intTable = ActiveDocument.Tables.Count

        With Word.Selection

            .Style = ActiveDocument.Styles("Projects")

            .Font.Bold = wdToggle

            .Font.Size = 11

            .SelectRow

            .Shading.Texture = wdTextureNone

            .Shading.ForegroundPatternColor = wdColorAutomatic

            .Shading.BackgroundPatternColor = wdColorWhite

            .TypeText Text:=rs.Fields("WeeklyUpdName").Value

            .MoveRight Unit:=wdCell

        End With

           

        

        

        

        

        Set objRange = ActiveDocument.Paragraphs(2).Range

        Set objTable = ActiveDocument.Tables.Add(Range:=oRg, numrows:=2, numcolumns:=3)
 

        intTable = ActiveDocument.Tables.Count

        intRow = ActiveDocument.Tables(intTable).Rows.Count
 

    With Word.Selection

        If intRow > 1 Then

        objTable.Rows.LeftIndent = InchesToPoints(0.25)

    End If

        .Style = ActiveDocument.Styles("Desc")

        .Font.Size = 11

        .TypeText Text:=rs.Fields("WeeklyUpdDes").Value

        .MoveRight Unit:=wdCell

    End With

    Wend

    

    

    

    

    intTable = ActiveDocument.Tables.Count

Open in new window

0
 
LVL 19

Accepted Solution

by:
dlc110161 earned 500 total points
ID: 24152005
Why don't you change your Style "Desc" so that it is a hanging indent? That way the paragraph will automatically take care of itself anytime the paragraph extends more than one line.

Dawn Bleuel
Word MVP
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24152217
If Dawn's comment does not resolve your poblem
I just noticed that begining with line 63  in your original code addresses the table properties, which I missed during the  first look at your code,
1.revert back to your original code
2.place this code between line 63 and 64
       intTable = ActiveDocument.Tables.Count
       intRow = ActiveDocument.Tables(intTable).Rows.Count
       If intRow > 1 Then
           .Rows.LeftIndent = InchesToPoints(0.25)
      End If

       

0
 

Author Closing Comment

by:setalley
ID: 31570147
Dawn,
Thank you so much...such a simple solution but the approach never even crossed my mind.  
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Preface: When I started this series, I used the term CommandBars because that is the Office Object class that it discusses. Unfortunately, when Microsoft introduced Office 2007, they replaced the standard Commandbar menus with "The Ribbon" and rem…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now