Solved

Macro to bold first line of a cell embedded in Word

Posted on 2011-02-24
13
621 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 500 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.

757 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

23 Experts available now in Live!

Get 1:1 Help Now