none
Insert parts of or whole excel sheet (.xlsx) in a word document (docx) RRS feed

  • Question

  • Hi there, I am looking to insert an entire excel sheet (say sheet1) into a word docx. The platforms I am using are Visual Studio 2010 Premium, .NET 4.0, Visual C# 2010, MS Office 2010.  The methods I was looking at were  microsoft.office.interop.word.Document.range.insertdatabase() and microsoft.office.interop.word.Document.range.insertfile().

    The use of the first method i.e. microsoft.office.interop.word.Document.range.insertdatabase() was only partially successful. It led to a open Datasource window even after I have specified the path and range of the excel sheet to be inserted, and after setting the Application.Visible to false (which was what I needed i.e. to run unseen). After googling the problem for a while, I came across a rather obscure post stating that the open Datasource window popped up only after MS Word 2007.

    The second method of using the microsoft.office.interop.word.Document.range.insertfile() method always invariably fails and returns a "command failed" or "The file might be corrupt " message.

    Resources on the internet in using both methods mentioned above seem quite scarce, so my question is has anyone been successful implementing an insert method using any of those mentioned or has a better alternative.

    Thanks in advance

    P.S. Do let me know if you like to see my source code.

    Wednesday, May 28, 2014 6:47 AM

Answers

  • Hi C-W

    Insert file isn't designed to work with Excel, just with "text type" documents (Word, RTF, HTML, plain text, etc.).

    In the Word UI: Insert/Text/Object, Object and choose the "Create from file" tab in the dialog box. Navigate to the workbook and "OK".

    Record this in a macro and see if that helps at all?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by C-W Thursday, May 29, 2014 8:44 AM
    Wednesday, May 28, 2014 1:11 PM
    Moderator

All replies

  • Hello,

    Word provides the Macro Recorder. Did you try to record a macro and then explore the required API calls?

    Properties and methods required for getting the job done are the same for VBA and VSTO. It doesn't matter what programming language you are using.

    Wednesday, May 28, 2014 6:56 AM
  • HI Eugene, yes I did at least for the method of :

    "microsoft.office.interop.word.Document.range.insertdatabase()"

    I have posted my source here if it interests you

    static void Main(string[] args)
            {
                try
                {
                    ///Initialization
                    const string PATH = @"C:\Users\TCW\Desktop\";
                    const string DOCNAME = @"Mailing Template.dotx";
                    if (!File.Exists(PATH + DOCNAME))
                        throw (new System.Exception("File not found : " + DOCNAME));
                    Application wordApp = new Application();
                    Document newDoc = wordApp.Documents.Open(PATH + DOCNAME);
                    ///Switch to set document to visible
                    const bool ISVISIBLE = false;
                    wordApp.Visible = ISVISIBLE;
                    const string EXCELNAME = @"dataset.xlsx";
                    if(!File.Exists(PATH + EXCELNAME))
                        throw (new System.Exception("File not found : " + EXCELNAME));
                    
                    /// Insert Data Fields here
                    object reSQLStatment = "SELECT * FROM [Parameter List$]";
                    newDoc.MailMerge.OpenDataSource(PATH + EXCELNAME,  //Data Source Path 
                                                    WdOpenFormat.wdOpenFormatAuto, //Data Source format
                                                    Type.Missing,//Confirm Conversions 
                                                    Type.Missing,//Read Only 
                                                    Type.Missing,//Link to Source 
                                                    Type.Missing,//Add to recent files 
                                                    Type.Missing,//Password Document 
                                                    Type.Missing,//Password Template 
                                                    Type.Missing,//Revert 
                                                    Type.Missing,//Write Password Document 
                                                    Type.Missing,//Write Password Template 
                                                    Type.Missing,//Connection
                                                    reSQLStatment,//SQL Statement 
                                                    Type.Missing,//SQL Statement 1 
                                                    Type.Missing,//Open Exclusive 
                                                    Type.Missing//Subtype
                                                    );
                    
                    ///Insert Data Table here
                    Microsoft.Office.Interop.Word.Range mergeRange =  newDoc.Range(200, 210);  
                    object bodySQLStatement = "SELECT * FROM [Sheet1$]";
                    object bodyConnection = @"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\TCW\Desktop\dataset.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=";//"Entire Spreadsheet";
                                        
                    //Range.InsertDatabase seems to work seemlessly for Word prior to 2007. Now a dialog box will appear to request the data source. 
                    mergeRange.InsertDatabase(WdTableFormat.wdTableFormatSimple1,//Table Format 
                                              Type.Missing,//Style
                                              Type.Missing,//Link to Source
                                              bodyConnection,//Type.Missing,//Connection
                                              bodySQLStatement,//SQL Statement 
                                              Type.Missing,//SQL Statement 1
                                              Type.Missing,//PasswordTemplate 
                                              Type.Missing,//Write Password Document
                                              Type.Missing,//Write Password Template
                                              PATH + EXCELNAME,//Data Source
                                              Type.Missing,//From
                                              Type.Missing,//To  
                                              true//Include Fields   
                                              );
            //Below is the VB snippet culled from Word Macro Recorder 
            /*Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
            Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\TCW\Desktop\dataset.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=" _
            , SQLStatement:="SELECT * FROM `Sheet1$`" & ""
            , PasswordDocument:=""
            , _PasswordTemplate:=""
            , WritePasswordDocument:=""
            , WritePasswordTemplate:= _""
            , DataSource:="C:\Users\TCW\Desktop\dataset.xlsx"
            , From:=-1, To:=-1
            , _IncludeFields:=True
                    
                     
                    ///Merge and Save
                    newDoc.MailMerge.Execute(false);
                    Windows mergeDocWins = wordApp.Windows;
                    const string MERGEDNAME = @"DEFECTS.HTM";
                    newDoc.Save();
                    wordApp.Documents.Close();
                    //Microsoft.Office.Interop.Word.Application has a quit method and event hence the casting to _Application to remove ambiguity
                    ((_Application)wordApp).Quit(); 
                }
                catch (System.Exception e)
                { Console.WriteLine(e.Message); }
            }
        }
    

    As you can see I have basically parsed what I got from the macro recorder into the insertdatabase method.

    Still the insertdatabase method will cause a Select Data Source window to open and the user has to manually navigate and set the source, something I am trying to avoid.

    I did not try macro recorder for insertfile because I am not sure of which actions in Word to take.

    P.S Sorry if all this comes out looking weird. Its my first time posting on this forum.

    Thanks again for your troubles.




    • Edited by C-W Wednesday, May 28, 2014 8:12 AM
    Wednesday, May 28, 2014 7:56 AM
  • Hi C-W

    Insert file isn't designed to work with Excel, just with "text type" documents (Word, RTF, HTML, plain text, etc.).

    In the Word UI: Insert/Text/Object, Object and choose the "Create from file" tab in the dialog box. Navigate to the workbook and "OK".

    Record this in a macro and see if that helps at all?


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by C-W Thursday, May 29, 2014 8:44 AM
    Wednesday, May 28, 2014 1:11 PM
    Moderator
  • Hi Cindy,

    Great to get an official confirmation. Have tried the AddOLEObject() taken from the Macro Recorder. Not exactly what I expected but sufficient that I can work from there. ;-)

    Many thanks.

    C-W

    Thursday, May 29, 2014 8:44 AM
  • Hi C-W

    Well, I don't know whether you can call an answer from me "official confirmation" :-) I don't work for Microsoft, just support Word development on a volunteer basis for almost 20 years...


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, May 29, 2014 9:24 AM
    Moderator
  • Hi Cindy,

    so on what basis do you say that "Insert file isn't designed to work with Excel"? Since the official source here

    http://msdn.microsoft.com/en-us/library/office/ff835231%28v=office.15%29.aspx

    seems to indicate otherwise.

    Still I have to assume that with 20 years of volunteering you know something about Word we mere mortals don't ;-)


    • Edited by C-W Thursday, May 29, 2014 9:36 AM
    Thursday, May 29, 2014 9:34 AM
  • Hi C-W

    What, in the linked information you reference, makes you think InsertFile should support Excel files?

    InsertFile has existed since "forever", coming forward from the days of WordBasic. Back then, there was no "interoperability" between the various applications. That only came with the introduction of OLE (mid-90s).

    InsertFile inserts content that's handled by Word as text. It can use converters to incorporate RTF and HTML alongside plain text and its own, native language.

    To put it another way: InsertFile flows the content coming in as text.

    An Excel workbook does NOT flow as text - it's a foreign object in the text; so is a graphics file, which you also cannot insert using InsertFile.

    In order to incoporate an Excel workbook - which is foreign to Word and how it works - a different handling is required than is used for text content. This means a different part of the object model.


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, May 29, 2014 9:49 AM
    Moderator
  • Hi Cindy,

    forgive me if I misunderstood, in the link provided, the syntax section clearly brought up excel support. The excerpt I copied below :

    ----------------------------------------------------------------------------------------------

    expression .InsertFile(FileName, Range, ConfirmConversions, Link, Attachment)

    expression Required. A variable that represents a Range object.

    Parameters

    Name

    Required/Optional

    Data Type

    Description

    FileName

    Required

    String

    The path and file name of the file to be inserted. If you don't specify a path, Word assumes the file is in the current folder.

    Range

    Optional

    Variant

    If the specified file is a Word document, this parameter refers to a bookmark. If the file is another type (for example, a Microsoft Excel worksheet), this parameter refers to a named range or a cell range (for example, R1C1:R3C4).

    -----------------------------------------------------------------------------------------------

    the explanation for the "range" parameter states quite explicitly that an excel worksheet or even part thereof may be inserted into a word doc. 

    Thursday, May 29, 2014 10:07 AM
  • Hi C-W

    Huh, my eyes missed that. However, it appears that the Help topic was simply copied forward from Word 2003 (or older).

    If I run the method on an Excel 97-2003 file (*.xls) there is no error message, but the converter is no longer available that used to be present in older versions of Word. That was able to import TABULAR information, as a Word table, once upon a time...


    Cindy Meister, VSTO/Word MVP, my blog

    Friday, May 30, 2014 6:56 PM
    Moderator
  • Hi Cindy,

     "...Help topic was simply copied forward from Word 2003...".

    I suspect as much but still good to hear from an expert like you. It would seem the resources on the MSDN are not kept as up to date as they should be, but I guess its inevitable when you have as large and diverse a product family like Microsoft does. Just frustrating for developers like us.

    Monday, June 2, 2014 7:24 AM
  • <<It would seem the resources on the MSDN are not kept as up to date as they should be>>

    No, they are not. I coincidentally ran into a Help topic yesterday (Documents.Open) where the last parameter is missing from any Language Reference I can find - a parameter that was introduced in 2010, if not earlier (XMLTransform).


    Cindy Meister, VSTO/Word MVP, my blog

    Monday, June 2, 2014 2:36 PM
    Moderator
  • I see,

    sorry for deviating a little, being relatively new to Windows programming, MS office, etc, would you say this is the best resource for information or is there any other place you can recommend.

    Tuesday, June 3, 2014 3:08 AM
  • Hi C-W

    I'm sure there are other places, but I only come across them when I search Google for specific problems, so I can't really make any informed recommendations.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, June 3, 2014 1:33 PM
    Moderator