creating a report table

kannan · #1 04-26-2012, 04:14 AM

Hi,

I am to create a report after a script execution in VBScript and the report has to be in word format. The report has to be customer specific template/ format.
i have a few options
1. recreate the entire report using code (dont know how much will this be possible)
2. Use a predefined template with headers, footers other sections and then create 'fields of interest' like mailmerge and insert data
3. use the predefined template as above and insert table data.

- option 1 takes a lot of time and is almost impossible to re-create the report exactly.
- option 2 works but i am looking for a single document into which the data is inserted.

can anyone please suggest a way to over come the mailmerge problem (in my case). I understand that mailmerge is supposed to do what it is doing.
or any other suggestions for the report creation would be appreciated.

thanks
kannan.k

macropod · #2 05-02-2012, 12:30 AM

Hi kannan,

Your problem description is way too vague to give any advice on. For example, you provide no information about where the report data are held or anything about how it should appear in the document. I also have no idea what you mean by 'a way to over come the mailmerge problem'. What mailmerge problem?

Depending on what the requirements are, you may indeed be able to use mailmerge. Or maybe OLE would do the job. And, yes, you can create complete, consistent, documents using vba, though this is much simpler if you have a template to work from, perhaps even containing custom Quick Parts that the code can insert as/when required.

You will have to provide a lot more detail before anyone can help you.

kannan · #3 05-02-2012, 04:02 AM

Hi Paul,

thanks for the reply. I apologise if the post was not clear.
let me explain in a more detailed manner of my setup in steps.

- the VB script is supposed to compare two strings aqcuired from external sources
- based on the comparison, the result will be true or false.
- this has to be entered in the report.

- the customer report template has some customer based information, for the first few pages and headers and footers in all pages
- then table will be present with the the usual "no:", "case ID", "expected", "observed", "result" etc... as column heading.

so my requirement is to (somehow) intelligently insert the required data in the appropriate location in table (using VBscript or C#; language does not matter i suppose). new row will have to be added for each test step.

as i had mentioned earlier, i am able to create my own report; but is it possible to insert multiple data at required places in word recursively?

thanks
kannan.k

macropod · #4 05-02-2012, 05:38 AM

Hi kannan,

It is still not clear what the script that does the comparison that produces the true/false result has to do with the report, except that either true/false or something based on it has to go somewhere. If you're trying to filter the data that the table is based on, this could be done in Excel. The filtered table could then be linked to your document via OLE or you could use a directory/catalogue mailmerge to populate the table. Either process can vary the number of rows. Mailmerge can do it with or without a table (ie using tabs to define the various columns).

You also mention inserting "multiple data at required places in word recursively". I'm not sure what recursion has to do with it, but you can easily enough add rows to a table or update a bookmarked range via vba.

None of this is especially complicated - it's just a matter of determining which of the tools at your disposal is/are best suited to the job.

kannan · #5 05-02-2012, 07:44 AM

Hi Paul

thanks for your support!
In my setup, the script that does the comparison also populates/creates the report. you can visualize it like a macro running in one word document(test script) will be creating/populating another word document (test report).

My difficulties with mail merge is that it needs the data source upfront and creates multiple copies of the template (which is its capability) but i need the other way around. multiple data has to be inserted into the same document at defined places many times over.

thanks
k.kannan

macropod · #6 05-02-2012, 04:11 PM

Hi kannan,

OK, so, if you know where you want the data to go, it's a simple matter of putting it there. Ideally, you'd collate all the data for a given location before inserting it there, though could could work through the data iteratively and output each portion to the appropriate place in the document as you go. Do note that if the same data needs to be replicated in multiple places, it only needs to be output to the document once - you then use cross-referencing for the replication.

Now, suppose you have a table somewhere that you want to add rows to and you know it's the third table. If you're working through the data iteratively, you can use code like:

Code:

Sub Demo()
Dim i As Long, StrData As String
StrData = "Column1,Column2,Column3,Column4,Column5,Column6,..."
With ActiveDocument.Tables(1).Rows
  .Add
  For i = 1 To .Last.Cells.Count
    .Last.Cells(i).Range.Text = Split(StrData, ",")(i - 1)
  Next
End With
End Sub

In this case 'StrData' is the name of whatever variable you're holding the data in. For demonstration purposes, a comma-delimiter is assumed.

Likewise, if you have a bookmarked destination (in this case, named 'MyBookmark') that you want to populate, with a single data stream that you want to turn into a table, you might use code like:

Code:

Sub Demo()
Dim StrHdr As String, StrData As String
StrHdr = "Column1,Column2,Column3,Column4,Column5,Column6"
StrData = "$A$1,$B$1,$C$1,$D$1,$E$1,$F$1" & vbCr & _
          "$A$2,$B$2,$C$2,$D$2,$E$2,$F$2" & vbCr & _
          "$A$3,$B$3,$C$3,$D$3,$E$3,$F$3" & vbCr & _
          "$A$4,$B$4,$C$4,$D$4,$E$4,$F$4" & vbCr & _
          "$A$5,$B$5,$C$5,$D$5,$E$5,$F$5" & vbCr & _
          "$A$6,$B$6,$C$6,$D$6,$E$6,$F$6" & vbCr & _
          "$A$7,$B$7,$C$7,$D$7,$E$7,$F$7" & vbCr & _
          "$A$8,$B$8,$C$8,$D$8,$E$8,$F$8" & vbCr & _
          "$A$9,$B$9,$C$9,$D$9,$E$9,$F$9" & vbCr & _
          "$A$10,$B$10,$C$10,$D$10,$E$10,$F$10"
StrData = StrHdr & vbCr & StrData
Call UpdateBookmark("MyBookmark", StrData)
With ActiveDocument.Bookmarks("MyBookmark").Range
  .ConvertToTable Separator:=","
  .Tables(1).Style = wdStyleTableMediumList2
End With
End Sub
 
Sub UpdateBookmark(BmkNm As String, NewTxt As String)
Dim BmkRng As Range
With ActiveDocument
  If .Bookmarks.Exists(BmkNm) Then
    Set BmkRng = .Bookmarks(BmkNm).Range
    BmkRng.Text = NewTxt
    .Bookmarks.Add BmkNm, BmkRng
  End If
End With
Set BmkRng = Nothing
End Sub

As I said, none of this is especially complicated.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to keep hyperlinks when creating a table of contents	Abacaxi	Word	3	04-18-2012 12:24 AM
Creating checkbox linked to table value.	sleepinglate	Word	0	10-16-2011 11:29 AM
Creating Daily report forms	DaveServo	Excel	4	05-20-2011 03:38 AM
Creating a footer row in a table?	barnkat	Word Tables	0	08-13-2010 10:40 AM
Creating table template on Microsoft Word (2003)	Tems4pauly	Word Tables	3	06-19-2009 06:05 AM