Solved

How do I delete blank rows in a Word document table from an Excel VBA procedure ?

Posted on 2013-12-19
19
1,987 Views
Last Modified: 2013-12-23
Hi All,

I have an Excel 2010 VBA procedure which populates 'label' controls in the cells of a Word document table with data from an Excel User form. The Word table has 6 rows. When I invoke the procedure, depending on the source data, between 1 and 6 rows are populated, as expected.

What I'd like to do is populate the Word table rows and then delete those unpopulated (blank, in other words) rows beneath them.

Is this possible from within my Excel user form code (as shown below) ?

   Dim WordApp As Object
   Dim WordDoc As Object
   Dim ils As Object
   
   Set WordDoc = WordApp.Documents.Open(gcProposalTemplatePath)
   WordApp.Visible = True

   'Initialise (clear out previous entries) the Proposal schedule/prices table in the Word document
   For i = 1 To 6
      For Each ils In WordDoc.InlineShapes
         If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then ' wdInlineShapeEmbeddedOLEObject
            Select Case ils.OLEFormat.Object.Name
               Case "laSession" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laCourseTitle" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laType" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laLevel" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laDuration" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laDelegates" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laProposedDate" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laPricesInhouse" & i
                  ils.OLEFormat.Object.Caption = ""
               Case "laPricesMaylands" & i
                  ils.OLEFormat.Object.Caption = ""
            End Select
         End If
      Next
   Next

   'Populate the Proposal schedule/prices Word table with data from the Excel User form controls
   For i = 1 To 6
      For Each ils In WordDoc.InlineShapes
         If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then ' wdInlineShapeEmbeddedOLEObject
            If Me.Controls("coCourseTitle" & i).Value <> "" Then
               Select Case ils.OLEFormat.Object.Name
                  Case "laSession" & i
                     ils.OLEFormat.Object.Caption = i
                  Case "laCourseTitle" & i
                     ils.OLEFormat.Object.Caption = Me.Controls("coCourseTitle" & i).Value
                  Case "laType" & i
                     ils.OLEFormat.Object.Caption = Me.Controls("coType" & i).Value
                  Case "laLevel" & i
                     ils.OLEFormat.Object.Caption = Me.Controls("coLevel" & i).Value
                  Case "laDuration" & i
                     ils.OLEFormat.Object.Caption = Me.Controls("coDuration" & i).Value
                  Case "laDelegates" & i
                     ils.OLEFormat.Object.Caption = Me.Controls("teNoOfDelegates" & i).Value
                  Case "laProposedDate" & i
                     dt = Me.Controls("teTrainingDate" & i).Value
                     time = Me.Controls("teTrainingTime" & i).Value
                     AM_PM = Me.Controls("coTrainingAmPm" & i).Value
'                     MsgBox fFormatDateComplete(dt, time, AM_PM)
                     ils.OLEFormat.Object.Caption = fFormatDateComplete(dt, time, AM_PM)
                  Case "laPricesInhouse" & i
                     If Me.Controls("coVenue" & i).Value = "Inhouse" Then
                        ils.OLEFormat.Object.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
                     Else
                        ils.OLEFormat.Object.Caption = "n/a"
                     End If
                  Case "laPricesMaylands" & i
                     If Me.Controls("coVenue" & i).Value = "Maylands" Then
                        ils.OLEFormat.Object.Caption = "£ " & Me.Controls("teTotalPrice" & i).Value
                     Else
                        ils.OLEFormat.Object.Caption = "n/a"
                     End If
               End Select
            End If
         End If
      Next
   Next

' THIS IS WHERE I ASSUME THE 'DELETE BLANK ROWS' FUNCTIONALITY WOULD OCCUR

Open in new window


Thanks in anticipation
Toco
0
Comment
Question by:Tocogroup
[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
  • 10
  • 9
19 Comments
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731242
0
 

Author Comment

by:Tocogroup
ID: 39731337
Thanks for the link.

I've applied the code to my procedure but I'm getting a Runtime error 438 ("Object doesn't support this property or method") on the first line...

Set oTable = Selection.Tables(1)

Do I have to name the table in the Word document so I can specifically refer to it in my code ?
Or do I have to select it first ?

I'm assuming I'll get the same error when I get on to the next line....

Set oRow = oTable.Rows(1).Range
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731347
Hi,

Set oTable = Selection.Tables(1)

is the first table in the selection

if you want to deal with all the tables

try

For Each oTable in ActiveDocument.Tables
'Your Code

Next

Open in new window

Regards
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Tocogroup
ID: 39731361
OK, I'm being a little (or possibly, very) ignorant here. I've only got one table in my Word document but it generates an error first time into the DeleteEmptyRows procedure.

The statement refers to a 'Selection'. How do make that selection ? I was hoping the procedure would just find the first available table in the document without me having to actually select it.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731373
Hi,

let's try
Option Explicit 

Public Sub DeleteEmptyRows()

Dim oTable As Table, oRow As Range, oCell As Cell, Counter As Long, _
NumRows As Long, TextInRow As Boolean

' Specify which table you want to work on.
For Each oTable In ActiveDocument.Tables
' Set a range variable to the first row's range
Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False

For Counter = 1 To NumRows

    StatusBar = "Row " & Counter
    TextInRow = False

    For Each oCell In oRow.Rows(1).Cells
        If Len(oCell.Range.Text) > 2 Then
            'end of cell marker is actually 2 characters
            TextInRow = True
            Exit For
        End If
    Next oCell

    If TextInRow Then
        Set oRow = oRow.Next(wdRow)
    Else
        oRow.Rows(1).Delete
    End If

Next Counter
Next oTable

Application.ScreenUpdating = True

End Sub

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39731425
Ok, I got a 'Runtime error 13 Type mismatch' on the following line, first time in.....

For Each oTable In ActiveDocument.Tables

The Word document at this stage is open and has been populated.
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731445
Hi,

i forgot you are in XL
place this at the end of your code

' Specify which table you want to work on.
For Each oTable In WordDoc.Tables
' Set a range variable to the first row's range
Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False

For Counter = 1 To NumRows

    StatusBar = "Row " & Counter
    TextInRow = False

    For Each oCell In oRow.Rows(1).Cells
        If Len(oCell.Range.Text) > 2 Then
            'end of cell marker is actually 2 characters
            TextInRow = True
            Exit For
        End If
    Next oCell

    If TextInRow Then
        Set oRow = oRow.Next(10) ' wdRow
    Else
        oRow.Rows(1).Delete
    End If

Next Counter
Next oTable

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39731484
Did what you said and copied your code to the end of mine yet it's still returning the same Error 13 Type mismatch on the line...

For Each oTable In WordDoc.Tables

Yet, 'WordDoc' is referenced in the populate loop just before this without a problem.
Do I have to Set oTable prior to the For...Next loop ?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731508
No

try to put at the beginning


Dim oTable as Object
regards
0
 

Author Comment

by:Tocogroup
ID: 39731510
Might this 'mismatch' message refer to one of the Word controls in the table cells themselves ?
0
 

Author Comment

by:Tocogroup
ID: 39731525
Yes, that did the trick for the oTable variable. However, it fell over with the same error 13 on...

Set oRow = oTable.Rows(1).Range

I've got this variable declared as a range. Should this too be an object ?
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731552
Yes

for oRow and oCell at least

Regards
0
 

Author Comment

by:Tocogroup
ID: 39731742
Thanks. That sorted out the Error 13.

The procedure now runs through to end but doesn't delete any table rows. For example, it doesn't detect empty rows 4, 5 and 6. I stepped through in Debug and it returns a cell Len of 3 for each initial cell of those 3 rows thus setting the TextInRow variable each time. See attached screenshot.

Could it be that Len counts an embedded control in the cell as 1, plus the end-of-cell marker of 2 chars ?
Proposal-document-table-Screensh.jpg
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39731958
It could be

so let's try this

Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False

For Counter = 1 To NumRows

    StatusBar = "Row " & Counter
    TextInRow = False

    For Each oCell In oRow.Rows(1).Cells
        If oCell.ColumnIndex = 1 Then
            If Len(oCell.Range.Text) > 3 Then
                TextInRow = True
                Exit For
            End If
        Else
            If Len(oCell.Range.Text) > 2 Then
                'end of cell marker is actually 2 characters
                TextInRow = True
                Exit For
            End If
        End If
    Next oCell

    If TextInRow Then
        Set oRow = oRow.Next(10) ' wdRow
    Else
        oRow.Rows(1).Delete
    End If

Next Counter
Next oTable

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39732117
Using Len on the first cell of the row returns the value 3 for every row no matter whether there's a value in the cell or not. The second column of the table contains 4 controls in each cell and returns a Len of 6 every time, irrespective of whether it is populated or otherwise.

Therefore, Len appears to count the control (as 1) and not the number of characters within the control.

I tried testing the value of the first cell of each row (for a value between 1 and 6 inclusive which are the only permitted values in that column) but  it returned a character which looked like a bullet point (???)
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39733480
Hi,

because you only have controls in your word

let's try this

' Specify which table you want to work on.
For Each oTable In WordDoc.Tables
' Set a range variable to the first row's range
Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False

For Counter = 1 To NumRows

    StatusBar = "Row " & Counter
    TextInRow = False

    For Each oCell In oRow.Rows(1).Cells
        For Each ils In oCell.Range.InlineShapes
            If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then
                If ils.OLEFormat.Object.Caption <> "" Then
                    TextInRow = True
                    Exit For
                End If
            End If
        Next
    If TextInRow Then Exit For
    Next oCell

    If TextInRow Then
        Set oRow = oRow.Next(10) ' wdRow
    Else
        oRow.Rows(1).Delete
    End If

Next Counter
Next oTable

Open in new window

0
 

Author Comment

by:Tocogroup
ID: 39733519
That's it ! It removed those blank rows. Excellent.

Unfortunately, it also removed the column headings row from the Word table.

I could change the For...Next loop counter from 1 to 2 but I later want to add another table to the document which will be principally text (not controls). How can I differentiate between a cell which contains text, and one which contains a control ?
0
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39734522
Let's try this

For Each oTable In WordDoc.Tables
' Set a range variable to the first row's range
Set oRow = oTable.Rows(1).Range
NumRows = oTable.Rows.Count
Application.ScreenUpdating = False

For Counter = 1 To NumRows

    StatusBar = "Row " & Counter
    TextInRow = False

    For Each ocell In oRow.Rows(1).Cells
        If WorksheetFunction.Clean(ocell.Range.Text) <> "" Then
            TextInRow = True
            Exit For
        End If
        For Each ils In ocell.Range.InlineShapes
            If ils.Type = 1 Or ils.Type = 2 Or ils.Type = 5 Then
                If ils.OLEFormat.Object.Caption <> "" Then
                    TextInRow = True
                    Exit For
                End If
            End If
            
        Next
    If TextInRow Then Exit For
    Next ocell

    If TextInRow Then
        Set oRow = oRow.Next(10) ' wdRow
    Else
        oRow.Rows(1).Delete
    End If

Next Counter
Next oTable

Open in new window

0
 

Author Closing Comment

by:Tocogroup
ID: 39735574
That's great ! It works perfectly. Thank you for your time and patience with this problem.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

696 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