Exporting data from Access to Word as tables using VBA - formatting question

I have (with help from EE members) written code to export data from subforms to word through tables.

I would like to be able to format those tables. Specifically I need to:

i) Remove the header row in the table.
ii) Specify the column width for each column
iii) Be able to format the cell contents (number and date formatting).


My code is as follows:

 Set daoRS2 = Forms!ClientFrm!ContractFrm!PaymentSubform.Form.RecordsetClone 

               Set tbl = .Tables.Add(.Bookmarks("\EndofDoc").Range, 1, 3)
             With tbl.Rows.First
            .Cells(1).Range.Text = "Payment Number"
            .Cells(2).Range.Text = "Due Date"
            .Cells(3).Range.Text = "Amount Due"
            End With
        daoRS2.MoveFirst
        Do Until daoRS2.EOF
            Set rw = tbl.Rows.Add
            With rw
                .Cells(1).Range.Text = daoRS2.Fields("PaymentNumber")
                .Cells(2).Range.Text = daoRS2.Fields("DueDateG")
                .Cells(3).Range.Text = daoRS2.Fields("AmountDue")
            End With
            daoRS2.MoveNext
        Loop

Open in new window

nrtdAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Have you tried to do any of these things? I'm not sure you have, since removing the "header" row is as simple as commenting out the code section that creates it (lines 4 through 8 in your code above).

To set the width, you must do so at the Column level, so something like:

YourTable.Columns("YourColumn").Width = 1000

Here's more info on the Column property: https://msdn.microsoft.com/en-us/library/office/ff195142.aspx. That includes descriptions and examples of all of the Column Properties.

I'd suggest you format the data as you insert it. For example, to format as Currency:

.Cells(3).Range.Text = Format(daoRS2.Fields("AmountDue"), "Currency")

You can also create your own custom formatting using the various symbols and such as defined in this MSDN Article on the Format function: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

We don't mind helping you fix your issue, but we do expect you to try to fix it yourself, and not just post your project requirements and expect the VOLUNTEER Experts to do your work for you.
nrtdAuthor Commented:
Hi Scott

Thank you for taking the time to reply.

I have tried to solve the problems for myself by reading the forums but have been going round in circles.

Before I posted here I removed lines 4-8 as you suggest but it still leaves a blank first row. Sorry for not making that clear. My various attempts to remove this then stopped the loop from running properly so I thought it best to post the original code in case it was relevant.

I honestly didn't expect someone to rewrite all the code but point me in the right direction as you kindly have done.

N
GrahamSkanRetiredCommented:
You create a single-row table and then add a new row for each line of data. Ideally, you could test the last row and only add a new one if it already has data. If that is too difficult, you could simply delete the empty first row after the loop has finished.
tbl.Rows.First.delete

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nrtdAuthor Commented:
Graham, That has done the trick! Thank you so much. N
nrtdAuthor Commented:
To adjust the column width:
tbl.AllowAutoFit = False
tbl.Columns(1).SetWidth InchesToPoints(0.5), RulerStyle:=wdAdjustNone
tbl.Columns(2).SetWidth InchesToPoints(1.5), RulerStyle:=wdAdjustNone
tbl.Columns(3).SetWidth InchesToPoints(5#), RulerStyle:=wdAdjustNone
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.