Solved

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

Posted on 2013-12-19
19
1,841 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
  • 10
  • 9
19 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
0
 

Author Comment

by:Tocogroup
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
 

Author Comment

by:Tocogroup
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
No

try to put at the beginning


Dim oTable as Object
regards
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

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

Author Comment

by:Tocogroup
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
Yes

for oRow and oCell at least

Regards
0
 

Author Comment

by:Tocogroup
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Expert Comment

by:Rgonzo1971
Comment Utility
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
Comment Utility
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 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
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
Comment Utility
That's great ! It works perfectly. Thank you for your time and patience with this problem.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

763 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

6 Experts available now in Live!

Get 1:1 Help Now