Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Macro to bold first line of a cell embedded in Word

Posted on 2011-02-24
13
Medium Priority
?
646 Views
Last Modified: 2012-05-11
Hi,

We're using MS Dynamics for (amongst other things) quotations.

Simply put, we have a basic template in Word 2007 with a table and bookmarks and Dynamics populates those tables with the stock item, description, qty ordered, total etc etc.

However we have to do quite a lot of manual tidying up. The main thing we have to do is with the first column of the table.

To start with it will look like this :

____________________________________
|Product code                                                            |
|Product Description                                                 |

What we have to do, for each cell is move the product code down one line, bold the product code, put an empty line in between the product code and description. We also then have to insert a line in each of the following columns so that the qty, price etc all line up with the product code.

____________________________________
|                                                                                   |
|Product code                                                          |
|                                                                                   |
|Product Description                                               |
|____________________________________|

I hope that is clear. My ASCII art-fu is weak!

We already run a macro once the quotation is created in Word by Dynamics and I was looking to add to this to make these cosmetic changes.

The actual table in word is 4 columns wide (Product, QTY, Unit Price, Total Price) and then as many rows as there are items on the quote.

So, the macro effectively needs to
go to a bookmark (ItemName in this instance),
insert a line,
bold the next line
insert another line,
go to the end of the text and insert a line.
Go to the next column,
insert a line,
next column,
insert a line
next column,
insert a line

lather rinse and repeat until end of table.

Many thanks in advance
0
Comment
Question by:bboitano
  • 7
  • 6
13 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34969061
It isn't clear what you are aiming for here.

Can you post a fuller before and after example

Meanwhile does this start off properly (it doesn't loop to the end of the table)?



Sub ModifyTable()
    Dim rw As Row
    Dim rw1 As Row
    Dim tb As Table
    Dim c As Integer
    
    Set rw = ActiveDocument.Bookmarks("ItemName").Range.Rows(1)
    Set tb = ActiveDocument.Bookmarks("ItemName").Range.Tables(1)
    Set rw1 = tb.Rows.Add(rw)
    rw.Range.Font.Bold = True
    Set rw1 = tb.Rows(rw.Cells(1).RowIndex + 1)
    For c = 2 To tb.Columns.Count
        Set rw1 = tb.Rows.Add(rw1)
    Next c
    
End Sub

Open in new window

0
 

Author Comment

by:bboitano
ID: 34969175
Does this attached image help?

I will try the macro shortly. Thank you very much
macro.PNG
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34969179
Note that you could post before and after sample documents. You don't have to use 'ASCII art'
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!

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34969188
Oops. We cross-posted. I'll study the pictures.
0
 

Author Comment

by:bboitano
ID: 34969265
I don't know why I just didn't attach it in the first instance :/

Too much to do and too little time is my excuse and I'm sticking with it!

Thanks again.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34974574
Sorry. Got called away today. I will try to address your question again tomorrow.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34977807
I have attached a document file that is my interpretation of your picture.

This is the code that will make it look like your second picture
Sub ModifyTable_2()
    Dim rw As Row
    Dim rw1 As Row
    Dim tbl As Table
    Dim c As Integer
    
    Set tbl = ActiveDocument.Bookmarks("ItemName").Range.Tables(1)
    Set rw = tbl.Rows(1)
    Set rw1 = tbl.Rows.Add(rw)
    rw.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    rw1.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    
    Set rw = tbl.Rows(4)
    Set rw1 = tbl.Rows.Add(rw)
    rw.Range.Font.Bold = True
    rw.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    rw1.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    
    Set rw = tbl.Rows(6)
    Set rw1 = tbl.Rows.Add(rw)
    rw1.Range.Paragraphs(1).Style = "Normal"
    'rw.Range.Font.Bold = True
    rw.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    rw1.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    
   Set rw = tbl.Rows.Add
    rw.Range.Paragraphs(1).Style = "Normal"
End Sub

Open in new window

Table.doc
0
 

Author Comment

by:bboitano
ID: 34978285
No problem - thank you for helping.

Have looked at the macro and it doesn't seem to run quite right. I will try and get a screenshot to show you what I mean later this morning. I just need to pull out the confidential info.

Otherwise, I notice you are adding the lines by way of adding rows and changing the bordering. Is it not possible to just add a line break at the beginning and end of the text in a cell?

Many thanks
0
 

Author Comment

by:bboitano
ID: 34978393
Here is an intial quote

 Initial
After running your macro on it

 After macro
And what it should look like (done manually)


Target.PNG
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 34978487
Yes, it is possible, but can I suggest that you attach actual document samples, as I did?  Pictures are a bit ambiguous, so it could save a few retries.
0
 

Author Comment

by:bboitano
ID: 34978536
Here is the file from which I took the screenshots. Hope it helps some.

Thank you again for your help.

macro.docx
0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 34981323
See if this is more like what you need
Sub ModifyTable3()
    Dim tbl As Table
    Dim rng As Range
    Dim cl As Cell
    
    Set tbl = ActiveDocument.Tables(1)
    For Each cl In tbl.Range.Cells
    
        'insert line feed if necessary
        Set rng = cl.Range.Characters.First
        If rng.Text <> vbVerticalTab Then
            rng.InsertBefore vbVerticalTab
        End If
        
        'Bolden first visible line
        rng.Collapse wdCollapseEnd
        rng.Move wdCharacter, 1
        If rng.MoveUntil(vbVerticalTab, Len(cl.Range)) = 0 Then
            rng.End = cl.Range.End
        End If
        rng.start = cl.Range.start + 1
        rng.Font.Bold = True
        
        'append a line feed
        Set rng = cl.Range.Characters.Last.Previous
        If rng.Text <> vbVerticalTab Then
            rng.InsertAfter vbVerticalTab
        End If
    Next cl
End Sub

Open in new window

0
 

Author Comment

by:bboitano
ID: 34995702
Hi Graham,

Hope you had a great weekend.

After changing it to
    'Set tbl = ActiveDocument.Tables(1)
     Set tbl = ActiveDocument.Bookmarks("ItemName").Range.Tables(1)
    For Each cl In tbl.Range.Cells

Open in new window


Seems to do everything fine. If I may just continue testing it today (only had a chance to run it once) and I'll close this off and award the points later on today.

Many thanks
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Suggested Courses

824 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