• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Automate Merge of Excel file into Field Coded Word Document

I am automating a Word mail merge using VB.NET, VSTO.  I have a field coded Word Document.  I am using code like this to merge another word document into the field coded Word document.  This works fine for Word documents.

Dim range As Word.Range = Nothing
Dim fWd As Word.Field = Nothing
Dim strFieldName as string = "ImprovementData"
Dim strFilePath as string = "c:\someplace\somewhere.doc"

For Each fWd In oDoc.Fields
 If fWd.Code.Text.IndexOf(" " & strFieldName & " ") <> -1 Then
     range = fWd.Result
     fWd.Delete()
     range.InsertFile(strFilePath)
     exit for
end if
Next

I also need to merge Excel spreadsheets into the same field coded document.    This does not work for Excel.  How can I merge the spreadsheet into the word document.  I am limited to using field codes.  Cannot use bookmarks.  The Word Document being merged into is a Field Coded template that is used over and over.  The document is actually populated from data from an Access database along with the merged Word and EXCEL files.  I cannot imbed macros in the Word template or in the EXCEL files.  Have to do it with VB.NET in VSTO.
0
JANWIL78
Asked:
JANWIL78
  • 7
  • 7
1 Solution
 
GrahamSkanCommented:
Your full scenario isn't clear. Is it a Mail Merge main document, and are the fields Mergefields? Why can't you use bookmarks?
0
 
JANWIL78Author Commented:
It is a mail merge main document.  I have added a sample of one of the main documents.  The field that I am trying to insert the EXCEL file in is the <<ImprovementData>> on page 2.  All of the other fields work, and I can merge a WORD file in this field.  I've attached the main document and the WORD and EXCEL file that I am working with.  There is a very good reason that I cannot use bookmarks...but it escapes me right now....sorry...the user is adamant that field codes are used, he explained why and it made perfect sense for the purpose ...but it escapes me right now...I am retrieving data from a database to populate the other fields.  I am using VB.NET (ADO) to populate the other fields.
SampleWordMergeFile.docx
SampleEXCELMergeFile.xls
QuoteLetterExperts.docx
0
 
JANWIL78Author Commented:
Something else I noticed as I was working with this...I attached an .xls file.  I got an error 'file might be corrupted' when I tried to use a 2007 Excel file .xlsx.  I haven't investigated that thoroughly but just wanted add clarification as to why I have 2007 Word documents and a different Excel version.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
GrahamSkanCommented:
InsertFile doesn't deem to work with Excel files.

Using the Word interface (Insert/Text group, Object, Text from file), if an .xls file is chosen, we are offered a list of acceptable file types, none of which is Excel. If it is an xlsx file that is chosen, then , as you say, the file is reported as corrupt. The detailed report says that the file was created in a pre-release version. An obvious bug.

I don't recall using InsertFile with Excel files in 2003 or earlier, so that may be new functionality. I'll check that later.
0
 
GrahamSkanCommented:
I cannot get it to work with .Xls file in Office 2003 or Office 2007. Without the range parameter being set, it imports the whole file as if it were a .txt file. If the range parameter is set, a 'bookmark not found' error is reported. This applies it the range is expressed as  "R1C1:R1C8" or as named range.
0
 
JANWIL78Author Commented:
I have been doing some more research on this.  I think I am going to have to treat Excel files differently.   The users are going to have to define a range in the spreadsheet and then that range will be inserted as a linked table.  Not sure whether that is mailmerge functionality or if I am going to have to use a different code approach.  In some cases, the Excel spreadsheet may contain a graph.  I have used ADO before to read the cells in a spreadsheet.  I haven't come across a situation with a graph though.  Any suggestions on best path to follow?  Thank you.
0
 
GrahamSkanCommented:
You aren't actually using the mail merge mechanism. You are only using the merge fields as placeholders, like bookmarks, so that wouldn't be a problem.

If you are familiar with ADO, I suggest that you use that.

Alternatives are copy/paste, adding an Excel object into the document (Inlineshapes.AddOleObject) or opening the workbook and walking through the range collecting the text cell-by-cell.
0
 
JANWIL78Author Commented:
Pointed me in the right direction and gave me alternatives to resolving my question.
0
 
JANWIL78Author Commented:
I have been working with this further.   I have run into a snag.  I am prompted to SELECT TABLE (see image) if I leave out the SQL STATEMENT argument on the insertdatabase.   However, if I put in the SQL STATEMENT, I no longer get prompted to select table but I get only the last row of the spreadsheet.  I have tried different variations with the HDR=YES/NO, I've tried moving both rows to the second line.  I am using the same main document as attached above.  I have also tried variations with the SQL Statement.

Dim range As Word.Range = Nothing
Dim fWd As Word.Field = Nothing
Dim strFieldName as string = "ImprovementData"
Dim strFilePath as string = "c:\someplace\SampleEXCELMergeFile.xls"

For Each fWd In oDoc.Fields
 If fWd.Code.Text.IndexOf(" " & strFieldName & " ") <> -1 Then
     range = fWd.Result
     fWd.Delete()
     range.InsertDatabase(Format:=0, Style:=0, LinkToSource:=False,   Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilePath & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" _
                                       , SQLStatement:="SELECT * FROM [Sheet1$A2:H3]", DataSource:=CType(strFilePath, Object), From:=-1, To:=-1, IncludeFields:=False)
    exit for
end if
Next


SampleEXCELMergeFile.xls
EXCELSelectTable.png
0
 
JANWIL78Author Commented:
The attached snippet copies from my EXCEL spreadsheet and pastes to my Word document in the location that I want.   Still playing with the parameters and will need to see the actual excel files that the users will be merging to determine whether this is the best approach or not.
Dim oDoc As Word.Document = Nothing
        Dim oWord As Word.Application = Nothing       
        Dim range As Word.Range = Nothing
	Dim strFieldName as string = "ImprovementData"
        Dim strFilePath as string = "c:\someplace\SampleEXCELMergeFile.xls"

	Dim objExcel As Excel.Application = Nothing
        Dim objBooks As Excel.Workbook = Nothing
        Dim objSheets As Excel.Worksheet = Nothing
 	Dim fWd As Word.Field = Nothing

	oWord = Globals.ThisAddIn.Application

        oDoc = oWord.ActiveDocument
	objExcel = New Excel.Application
                                    
        objBooks = objExcel.Workbooks.Open(strFilePath)

	For Each fWd In oDoc.Fields
            If fWd.Code.Text.IndexOf(" " & strFieldName & " ") <> -1 Then
               range = fWd.Result
               fWd.Delete()
               objSheets = CType(objBooks.Worksheets(1), Excel.Worksheet)
               objSheets.Range("A2:H3").Copy()

               range.PasteExcelTable(False, True, False)
               objSheets = Nothing
               objBooks.Close()
               objExcel.Quit()
               objBooks = Nothing
               objExcel = Nothing

               Exit For
             End If
         Next
oDoc = nothing
oWord = nothing
oRange = nothing

Open in new window

0
 
GrahamSkanCommented:
Sorry, I can't discern any text in the images.

The choice of transfer method from Excel would depend on your circumstances, especially, do you want to update from the source later, or are you producing a final copy for printing.

I haven't used it very often, but a Database field is good if you need to update the document later. That is the same as the embedded object solution. However formatting possibilities are very limited for both the methods. That may not be important to you (or you client).

If you copy and paste to a table, you can reformat the Word table afterwards.

I see that you have posted another comment, so I'll read it now.
0
 
GrahamSkanCommented:
Ah, I had already seen it (26183554).

Copy/Paste will also copy the formatting from the spreadsheet. This could be advantageous.
0
 
JANWIL78Author Commented:
Thank you so much for your assistance with this.  Once I can get true examples of the user's spreadsheets I will know whether the copy/paste approach will work for me.  I like that the formatting will be maintained.  We'll see.  Thanks again.
0
 
GrahamSkanCommented:
OK. Good luck
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now