?
Solved

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

Posted on 2007-11-15
9
Medium Priority
?
7,287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
9 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 20287792
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
ID: 20287796
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
ID: 20287822
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Expert Comment

by:Rodcarr1
ID: 20609395
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
 
LVL 1

Expert Comment

by:Rodcarr1
ID: 20610130
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
ID: 20611209
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
ID: 20611565
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
ID: 20612445
Hi Graham,

This is the solution I have applied and it works.

Regards
Rodney
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20612548
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Suggested Courses

719 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