Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-15
9
Medium Priority
?
7,301 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 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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …
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…

877 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