Solved

Macro to bold first line of a cell embedded in Word

Posted on 2011-02-24
13
623 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

803 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