Excel to Word - generating a report

[imru]

Hello...I've been given a task to use microsoft technologies (specifically word, excel and VBA...I'm a Java developer so I don't have a complete understanding of VBA).

I have an Excel Workbook with about 9 sheets in it. These sheets are populated via a CSV file I export from Word (it's a survey...an answer of "A" = 4 points and so on). I need to generate reports in Word based on the data in the excel sheets.

I have a master template document with bookmarks for the dynamic data, however I cannot seem to find how to do the following:

Based on the data in Excel I need to insert text (this text is stored in a bunch of different files) into the bookmark locations in Word. I have found many examples doing this using Access...however while that is a good solution to my task, that is not something I am able to do. Any ideas?

Thanks,
Steve

[fumei]

First off, if you want to have the text-into-bookmark dynamic you need to recreate the bookmark. Normally, people use Bookmark.Range.Text = "blah blah". This does NOT put the text into the bookmark range.
Bookmark.Range.Text = "blah blah" will do this.

[Bookmark]blah blah

Bookmark("ThisOne").Range.Text = "blah blah"
Bookmark("ThisOne").Range.Text = "blah2 blah2" will do this:

[Bookmark]blah2 blah2blah blah

In other words, it will append, not replace.Sub FillABookmark(strBM_Name As String, strBM_Text As String)
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
With Selection
.GoTo what:=wdGoToBookmark, Name:=strBM_Name
.Collapse Direction:=wdCollapseEnd
oBM(strBM_Name).Range.Text = strBM_Text
.MoveEnd unit:=wdCharacter, Count:=Len(strBM_Text)
oBM.Add _
Name:=strBM_Name, Range:=Selection.Range
.Collapse Direction:=wdCollapseEnd
End With
Set oBM = Nothing
End Sub will replace. Pass the bookmark name, and the string you want to add to it. as parameters to the procedure. Of course if you are doing this with a named instance of Word then you must fully qualify the objects in the procedure.Dim wrdApp As Word.Application
' then...
Set oBM = wrdApp.ActiveDocument.Bookmarks
' etc etc etc

(this text is stored in a bunch of different files)This is do-able, but you need to give more details.

[imru]

Thanks alot for your help! This is definitaly some of what I've been looking for. My vision is getting blurry scouring the net for examples...

I'll try to explain what I am doing in a little more detail.

1. I receive a word document (a filled out survey) from a participant. After which I export it as a CSV file and use a Import data to get the data into excel. Once the data is in excel, my scoring engine runs through it an creates a summary sheet that has about 20 "point scores". Based on these point scores, I need to create my report.

2. The reports are rather lengthy, if you were to add all the text possible into one document, it runs around 200 pages. Remember I have about 20 scores, and for each score I have 5 possible pieces of text. For instance, on attribute 1 the user scores a 3.4...which tells me I have to insert the text block that corresponds to a score of 3.0 - 5.0. In order to manage all of this text, the possible text for each score is stored in a seperate file. So I have 20 files. (File 1 corresponds to Score 1...page 1 of this file corresponds to a score of 0.0 - 2.0, page 2 corresponds to a score of 2.01 - 4.0 and so on)

3. Since I have static and dynamic text in my report, the bookmarks correspond to the location the text needs to go based on each score. (bookmark1 corresponds to Score 1, and the possible text for Score 1 is stored in File 1)

Thanks again for your help

[mdmackillop]

Hi Steve,
Welcome to VBAX
Are you looking to run this from Word or Excel. I think both are possible, but obviously a different approach is required for each.
Gerry is the real Word expert here, so I'd follow his advice there.
From the Excel side, it looks like you're relating scores, score groupings, file names and destination bookmarks. Is it possible for you to set up these in a spreadsheet and post it here together with a sample csv file and scores?(use Manage Attachments in the Go Advanced section)
Regards
MD

[imru]

It really doesn't matter whether I use Excel or Word to control all of this. I was thinking excel...because my score data is stored in multiple sheets. I have to generate these reports for about 10 people (you can think of each workbook containing all the scores per person in a given company). I'm looking for a quick and dirty way to solve this, so if word is better..then so be it. (or even a combination of both...but that doesn't seem very automated). I will post the sample in a short while, thanks again for your help.

[mdmackillop]

A "quick and dirty" sample, thanks to Ken Puls for his Push to Word KB item
Copy all files to C:\AAA folder.
Regards
MD


Option Explicit
Sub BCMerge()
Dim pappWord As Object
Dim docWord As Object
Dim wb As Excel.Workbook
Dim Path As String
Dim GetRow As Long
Dim MyBM As String
Dim MyFile As String
Set wb = ActiveWorkbook
Path = "C:\AAA\Report.dot"
On Error GoTo ErrorHandler

'Create a new Word Session
Set pappWord = CreateObject("Word.Application")
On Error GoTo ErrorHandler
'Open document in word
Set docWord = pappWord.Documents.Add(Path)

'Read the corresponding values obtained by Match function
GetRow = Application.Match(Range("MyScore"), Range("Scores"), 1) + 2
MyBM = Cells(GetRow, 1).Offset(, 1)
MyFile = Cells(GetRow, 1).Offset(, 2)

'Go to the relevant bookmark and insert the data from the file
pappWord.Selection.Goto What:=wdGoToBookmark, Name:=MyBM
pappWord.Selection.InsertFile Filename:=MyFile

'Activate word and display document
With pappWord
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With

'Release the Word object to save memory and exit macro
ErrorExit:
Set pappWord = Nothing
Exit Sub
'Error Handling routine
ErrorHandler:
If Err Then
MsgBox "Error No: " & Err.Number & "; " & Err.Description
If Not pappWord Is Nothing Then
pappWord.Quit False
End If
Resume ErrorExit
End If
End Sub

[imru]

Thanks...that definitaly helps. I have to do some searching in the file and insert the correct test based on the score. After I enter all of the text into the report I then have to do a Find & Replace in order to customize the report for the individual.

Attached is a small portion of what I'm working with.

Bookmark A1 needs to be the score that is in Cell C3 (in Book1.xls). After the score is entered...I need to move the RED ARROW to the corresponding spot on the interval. OR is a special range we calculate based on the average of the entire companies scores. The OR can be found in the sheet titled OR.

A1_AB.doc is the text I need to search for and push into the report. This text will be pushed into the Bookmark named A1_AB in SampleReport.dot.

Thanks again for all your help...I was thrown into this and the guys who made this stuff are not developers, they are businessmen :help

[mdmackillop]

If your documents consist of a variety of single simple paragraphs it would simplify things if these paragraphs were stored in a range of cells within Excel. The FirstName etc. could more easily be incorporated prior to insertion into the report. Is this possible? More complex text/graphics could still be saved in documents if required.
I'll need to look at the graphics position later.
Regards
MD

[mdmackillop]

something like

pappword.Shapes(7).IncrementLeft 100 'set variable by score

[imru]

Sorry it took me so long to respond...I lost my internet connection for the day.

I figured out, using the macro recorder, how to move the grapics around...my question is now how do I do this from Excel. I've tried a few things out but can't seem to figure it out.

To answer your question about the paragraphs...yes I could easily put the paragraphs into a Cell. I do have to do some searching for what paragraph to insert. I could hard code this, but if I had to change any of the text...I could run into problems.

Another thing I seem to need to do is copy a cell from an excel sheet and paste it into a table in word (there is a bookmark in the table in word). The reason I need to do this is because I am copying some wingdings colored symbols (thumbs up, Upwards arrows, Downwards Arrows). I guess I could insert the character code into word and then make it the active selection and change the font and color that way... (I'm not finding the VBA books I have to be that helpful...at least not in the same way I've found Java and C++ books helpful. There's alot of nuiances that you guys are really helping me out with and thanks again!)

Attached is one of the graphics I have saved in a word document that needs to be pushed into the report based on the data in the excel book (and some other conditional logic)

[mdmackillop]

I think the Names can be sorted with a Find/Replace on the completed report. Regarding the graphics, if you keep it simple and put one graphic in each doc file, this can be inserted at the appropriate bookmark. In my Excel example, I would continue the row for each score with alternate Bookmark and FileNames. This can easily be incorporated into a loop e.g.

'Read the corresponding values obtained by Match function
GetRow = Application.Match(Range("MyScore"), Range("Scores"), 1) + 2
'Get the values from pairs of cells in the Score Row
For j = 0 To 50 Step 2
MyBM = Cells(GetRow, 1).Offset(, j + 1)
MyFile = Cells(GetRow, 1).Offset(, j + 2)
'Go to the relevant bookmark and insert the data from the file
pappWord.Selection.Goto What:=wdGoToBookmark, Name:=MyBM
pappWord.Selection.InsertFile Filename:=MyFile
Next

[mdmackillop]

This should move the arrow for a score of 5
'Move the arrow (shape7)
Dim Score As Long, i As Long
Score = 5
i = 35 + Score * 33.5
docWord.Shapes(7).Left = i

[mdmackillop]

Updated sample. Extract all files to C:\VBX
Sample files have only been created for the test value in the spreadsheet, but this should set the arrow and import your Gauge graphic into a table

[imru]

That definitaly helps! For moving the red arrow i came up with the same formula but the IncrementLeft function tends to produce some weird results. How about applying this to the OR Shape (btw..how did you know the index of the Red Arrow was 7? I just referred to it was "Text Box 23" as that's what I got from the macro recorder.

For the OR I'm using a combination of the following functions:

ActiveDocument.Shapes("AutoShape 21").Select
Selection.ShapeRange.ScaleWidth 7.5, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth -3, msoFalse, msoScaleFromBottomRight

However this has not been producing consistent results.

[mdmackillop]

..how did you know the index of the Red Arrow was 7?

Nothing clever there, I just tried all the options!

Sub Shapeindex()
For i = 1 To 20
ActiveDocument.Shapes(i).Select
MsgBox i
Next
End Sub


I can look at OR this evening.