?
Solved

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

Posted on 2009-04-14
5
Medium Priority
?
1,622 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

719 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