Solved

MS Word VBA : How to insert a table of data into a word document ?

Posted on 2007-11-15
9
7,248 Views
Last Modified: 2013-12-25
Hi,

Our user need to send out a well formatted letter via MS Word.  Yet, on top of the letter itself, in the middle of the letter to the client, we need to insert a table of data (which is the client's transaction details) and the data is resided in our MS SQL server.

My question is : are there any way to let the user to just input a date (in MS Word) and retrieve the relevant data from our MS SQL server and then pass back the data set to MS Word and make it as a table of data within our letter to the client?  I have already consider the use of MS EXCEL instead of MS Word (as I never do any programming in MS Word but MS Excel).  Yet, our users disagree on this as they already have countless fancy standard letter to client and they don't want to do them again in EXCEL.

Can anyone help?  Thanks.

Cheers
Stanley
0
Comment
Question by:StanleyLMW
  • 6
  • 3
9 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
Comment Utility
That can certainly be down with Word VBA, but you may find it easiest to use Mail Merge.

There is a Mail Merge wizard in Word accessible via the Tools menu. The exact presentation depends on the Word Version. In Word 2003 , it goes: Tools/Letters and Mailings/Mail Merge...

In that version the Wizard is driven from the Task pane which opens on the right hand of the Window. For your purpose, you will need to choose 'Directory' as the document type in Step 1. In step 2 you browse to your database table and choose recipients (individual records from the table if you don't need them all).

Step 3 is where you can place Mail Merge fields( via the Mail Merge toolbar, which needs to be visible). Here you can insert your table. Make it a single row (plus any header rows). Using the Insert Merge Fields button on the toolbar, select the fields and put them in the first 'data' row.

Step 4 gives you the chance to preview via the <<>>abc button.

In Step 5, choose one of the four right-most buttons on the toolbar to run the merge.
If you chose to produce a new document in step 5, you can edit or print it in step 6.



0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
typo:
"That can certainly be down with Word VBA, but you may find it easiest to use Mail Merge"
should be:
"That can certainly be DONE with Word VBA, but you may find it easiest to use Mail Merge"
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
If this seems easy enough, we will consider the best way to get the date into the query. Let us know how you get on.
0
 
LVL 1

Expert Comment

by:Rodcarr1
Comment Utility
Hllo, I have sort or less the same issue. In MS Access I use :
    o_wordAPP.Documents.Open filename:=tFILE   'tFILE is the path of the Word applet
    o_wordAPP.Visible = True                                  'I make Word visible

    Set o_wordDOC = o_wordAPP.Documents(Dir(tFILE))   'I make an object for the word document

    With o_wordDOC                                                   'I want to do stuf in the document

        Do While Not oRS2.EOF    'I get my variables from my Access table

            Select Case oRS2(1)            'Select value (variable)

                Case "CRBADR"               'For each variable you can decide what to
                    .GoTo What:=wdGoToBookmark, Name:="CRBADR"     'Create bookmarks in MS Word to navigate
                    .Bookmarks("CRBADR").Range.Text = gCRBADR(sLEDNR)    'Fill the bookmark with the proper value

                Case "CRBGESLACHT"
                    .GoTo What:=wdGoToBookmark, Name:="CRBGESLACHT"
                    If gCRBGESLACHT(sLEDNR) = "M" Then
                        .Bookmarks("CRBGESLACHT").Range.Text = "heer"
                    ElseIf gCRBGESLACHT(sLEDNR) = "V" Then
                        .Bookmarks("CRBGESLACHT").Range.Text = "mevrouw"
                    Else
                        .Bookmarks("CRBGESLACHT").Range.Text = "L.S."
                    End If

                  '*************************************************************************************
                'Here I try to create a table in Word but this doesn't happen!!!!!!!!!!
                'After that I have to navigate through the table to fill out the values in the proper cells
                Case "SPECIFICATIE"
                    .GoTo What:=wdGoToBookmark, Name:="SPECIFICATIE"
                    .Tables.Add Range:=Selection.Range, NumRows:=10, NumColumns _
                    :=4, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
                    wdAutoFitFixed
                    With Selection.Tables(1)
                        If .Style <> "Table Grid" Then
                            .Style = "Table Grid"
                        End If
                        .ApplyStyleHeadingRows = True
                        .ApplyStyleLastRow = True
                        .ApplyStyleFirstColumn = True
                        .ApplyStyleLastColumn = True
                    End With

                  '*************************************************************************************

            END SELECT

            ors2.movenext

        Loop
    End With


If this is what you mean Stanley than we have a common issue. Above I have spelled out an example so if there is anyone who can help us out here please help!!!!!!!!!!!!!!!!!!!!!

Regards,



Rodney
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 1

Expert Comment

by:Rodcarr1
Comment Utility
The pice between the stars is now:
I have prepared the table in word with one row and 5 cells. I have creaed five bookmarks and put each bookmark in a cell. Then I declare five string variables (str1 to str5). In the loop for the detail lines I build the string variables with str1 = str1 & ors(j-1) & vbcrlf.
Then you get one multiline string. At last I fill the bookmarks with the values and the row (one rows) will automatically be extracted and it looks like a table. Eventually you can create a second row for the totals.

Good luck.
Rodney


                Case "SPECIFICATIE"
                    .GoTo What:=wdGoToBookmark, Name:="SPECLEDNR"
                    .Tables(2).Select

                    Set oDB1 = CurrentDb()
                    Set oRS1 = oDB.OpenRecordset("SELECT ADMNR, MBROMS, VRVDAT, BEDRAG, MBROMS FROM WANHLS, MUTBRB WHERE CRBNR = " & Me.CRBNR & " AND WANHLS.MUTBR = MUTBRB.MUTBR ORDER BY ADMNR, VRVDAT")

                    If Not oRS1.EOF Then

                        Debug.Print "LEDNR - OMSCHRIJVING - VERVALDATUM / BEDRAG"

                        Do While Not oRS1.EOF

                            With oRS1

                                j = j + 1

                                For i = 1 To 4

                                    If i = 1 Then
                                        sSTR1 = sSTR1 & oRS1(i - 1) & vbCrLf
                                    ElseIf i = 2 Then
                                        sSTR2 = sSTR2 & oRS1(i - 1) & vbCrLf
                                    ElseIf i = 3 Then
                                        sSTR3 = sSTR3 & Format(oRS1(i - 1), "DD-MM-YYYY") & vbCrLf
                                    Else
                                        sSTR4 = sSTR4 & Format(oRS1(i - 1), " ##.00") & vbCrLf
                                        sSTR5 = sSTR5 & "¬ " & vbCrLf
                                    End If

                                Next

                                .MoveNext

                            End With

                        Loop

                        o_wordDOC.GoTo What:=wdGoToBookmark, Name:="SPECLEDNR"
                         o_wordDOC.Bookmarks("SPECLEDNR").Range.Text = sSTR1

                        o_wordDOC.GoTo What:=wdGoToBookmark, Name:="SPECOMS"
                         o_wordDOC.Bookmarks("SPECOMS").Range.Text = sSTR2
                       
                        o_wordDOC.GoTo What:=wdGoToBookmark, Name:="SPECVRV"
                         o_wordDOC.Bookmarks("SPECVRV").Range.Text = sSTR3
                       
                        o_wordDOC.GoTo What:=wdGoToBookmark, Name:="SPECVAL"
                         o_wordDOC.Bookmarks("SPECVAL").Range.Text = sSTR5
                       
                        o_wordDOC.GoTo What:=wdGoToBookmark, Name:="SPECBDG"
                         o_wordDOC.Bookmarks("SPECBDG").Range.Text = sSTR4

0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
You could fill the bookmarks more efficiently. You don't need to 'GoTo' them.

This line:
o_wordDOC.Bookmarks("SPECLEDNR").Range.Text = sSTR1
will work on its own

0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
RodCarr1
If you still have a problem, I suggest that you post  question a of your own. Most experts will not see these extra comments.
0
 
LVL 1

Expert Comment

by:Rodcarr1
Comment Utility
Hi Graham,

This is the solution I have applied and it works.

Regards
Rodney
0
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
OK. I see that you didn't use a table after all. If you do want to know how to create and fill a Word table at some time in the future, I am sure that there will be an expert who can respond to a question on that subject.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:

772 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

12 Experts available now in Live!

Get 1:1 Help Now