none
Copy data from Excel cells to a Word table RRS feed

  • Question

  • Hello all,

    I've seen multiple forums regarding copying data from a Word table to an Excel spreadsheet, but not visa-versa...Here's my situation:

    I have a form in Microsoft Office that contains a table like the one below

    Name             Address                 City            State...

    You guys get the drift. I want to copy cells from an excel spreadsheet containing the data needed into this table using vb.net. I'm new to vb and have finally just figured out proper excel/word automation.

    Any help would be appreciated. I have tried to 'reverse engineer' code that allows for moving table data in Word to Excel with no success. I had this question in Visual Basic General, but I think it was the wrong forum so I moved it here hoping this is the correct one.

    Thanks

    -DP

    Please reference the following thread for history (link below): Opening Word and Excel Files from VB.net
    http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/7ea68bac-dbaf-4989-8eef-7d3586b2faa5

    Thursday, April 26, 2012 12:31 PM

Answers

  • Hi DP

    I can move the question later. We're still in the process of determining which approach you want :-)

    2. You don't have to change the order of the columns, although you don't want to use copy/paste if they aren't in order. Copy/paste isn't necessary and is usually frowned on as, if at all possible, the Clipboard should be avoided by the developer. It just makes a difference what options are open to you and how involved things can get.

    6. (My response to this point affects point (1)).
    If the Excel workbook is basically a "source file" and shouldn't/needn't be manipulated by the user, then I see two possible efficient approaches, and one that's less so.

    (A) Excel is capable of providing data in a workbook via an OLE DB connection. For a .NET app, this usually means ADO.NET, although the old ADO is also a possibility. This means you can query the data you want from Excel, save it in variables, close the connection, then write the values to the Word document. If you need assistance with the data connection, the place to ask about that would be the Excel forum, or possibly a general forum that supports database connections.

    Writing the data to Word would belong in the Word forum.

    (B) Starting with the Office 2007 file formats, you can extract information directly from the closed document, using the standard Packaging and XML namespaces in .NET. There's also the Open XML SDK available that will help "streamline" the task. If you decide to go this route, retrieving the data would be a topic for a forum that supports the Open XML file formats. The forum for the Word side stays the same.

    Even your Office 2003 users can use this, as accessing the Open XML file is done independent of the Office application. (And anyway, the Office Compatibility Pack supports working with the newer file formats in older versions of Office.)

    (C) This is the less efficient route, as it involves automation (interop) with the Excel application. This is basically the type of thing you've been looking at up until now. If either of the other two approaches will work for you, they're probably preferable to this one, as they'll be faster, use fewer resources and you won't run into the kinds of problems that crop up when the user is also trying to work in Excel parallel to your app. The other two approaches will also be port better when you move to newer versions of Office.

    Thoughts :-)?


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Darth Probius Tuesday, May 1, 2012 11:21 AM
    Friday, April 27, 2012 5:35 PM
    Moderator

All replies

  • Hi DP

    Actually, the General Office forum, or maybe the Word or Excel forum, would be the better place for the question as it doesn't involve the VSTO technology and is purely object model related... Given the direction you want to go, I'd say the Word forum would be best as there are a number of regulars there who work in both directions.

    But let's get the ball rolling, in any case :-) Before we can get down to the nasty details, we need more information from you, so that we don't get you started on the wrong foot:

    Which version(s) of Office are involved?

    Do you want to pull in the columns 1:1 (not skip any)?

    Am I correct in assuming you want the result to be a WORD table and not an embedded Excel table?

    I get the impression you want to append rows from the Excel sheet to an existing Word table?

    Are both the Excel workbook and the Word document going to be local on a user's machine? Or are we talking about something that may involve websites, Sharepoint or something?

    Will the Excel workbook be open at the time the trasfer should start, or closed?


    Cindy Meister, VSTO/Word MVP

    Friday, April 27, 2012 8:04 AM
    Moderator
  • Cindy,

    Thanks for the response. I'm still new to these forums so I apologize for placing this in the wrong one. Should I move (not sure if I even can or how to) it or is there a way to get a moderator or someone else to move this to the proper forum (is it a request)? Regarding your questions:

    1. Office 2007 and Office 2003 (although most users are on 2007; the few who have 2003 can be moved to the new version easily, since we need to move them soon anyway).

    2. As of right now, the columns in the excel file are not in order (file is exported from an application). If necessary, I can write code that will remove unwanted columns and arrange them so that they are in the proper order unless you know of a way to overcome this without doing any of that.

    3. Yes you are correct; the result will be a word table. Actually the entire form in Word is one large table (I decided to just work with this instead of recreating the entire thing).

    4. If i'm understanding what you said here correctly (essentially copy and paste?) then yes, the rows will be appended to the existing table.

    5. The application (online) where the file is exported from is available to all users. They will export the file to their local machine. The word document will also be on their local machines as well. Look at the referenced thread and view my code; this is why i'm allowing the user to select the document as each one might be saving the file in a different location and naming it a different way.

    6. Looking at my code (again), I originally thought it would be good to have the user see both documents as the application ran. However, after thinking about it, I'm thinking that the Excel file should be closed and only have the Word document open. However, if you have a better suggestion I'm definitely open to anything.

    Again, thanks for the response. Other than a (small?) code change, would the above work with a Word template instead of a document so that the user would always be prompted to save the new file?

    -DP


    Friday, April 27, 2012 2:16 PM
  • Hi DP

    I can move the question later. We're still in the process of determining which approach you want :-)

    2. You don't have to change the order of the columns, although you don't want to use copy/paste if they aren't in order. Copy/paste isn't necessary and is usually frowned on as, if at all possible, the Clipboard should be avoided by the developer. It just makes a difference what options are open to you and how involved things can get.

    6. (My response to this point affects point (1)).
    If the Excel workbook is basically a "source file" and shouldn't/needn't be manipulated by the user, then I see two possible efficient approaches, and one that's less so.

    (A) Excel is capable of providing data in a workbook via an OLE DB connection. For a .NET app, this usually means ADO.NET, although the old ADO is also a possibility. This means you can query the data you want from Excel, save it in variables, close the connection, then write the values to the Word document. If you need assistance with the data connection, the place to ask about that would be the Excel forum, or possibly a general forum that supports database connections.

    Writing the data to Word would belong in the Word forum.

    (B) Starting with the Office 2007 file formats, you can extract information directly from the closed document, using the standard Packaging and XML namespaces in .NET. There's also the Open XML SDK available that will help "streamline" the task. If you decide to go this route, retrieving the data would be a topic for a forum that supports the Open XML file formats. The forum for the Word side stays the same.

    Even your Office 2003 users can use this, as accessing the Open XML file is done independent of the Office application. (And anyway, the Office Compatibility Pack supports working with the newer file formats in older versions of Office.)

    (C) This is the less efficient route, as it involves automation (interop) with the Excel application. This is basically the type of thing you've been looking at up until now. If either of the other two approaches will work for you, they're probably preferable to this one, as they'll be faster, use fewer resources and you won't run into the kinds of problems that crop up when the user is also trying to work in Excel parallel to your app. The other two approaches will also be port better when you move to newer versions of Office.

    Thoughts :-)?


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Darth Probius Tuesday, May 1, 2012 11:21 AM
    Friday, April 27, 2012 5:35 PM
    Moderator
  • Cindy,

    @2 - I know you said it's unnecessary to change the order of the columns, but if code was written to delete and reorder the columns, would the Clipboard still need to be utilized to move the data? Unless I'm not thinking about this correctly, I don't think this project should get too involved...

    @6A - I have seen multiple forums that have suggested the use of OLE DB. However, being unfamiliar with the language, I had decided to stay away thinking it wasn't what I was looking for and/or it was too complicated. However, looking at it now, it seems somewhat familiar to SQL (which I know). If this is the case, then this may be the option that I'm looking for.

    @6B - I've never used the Open XML SDK and am not sure what is involved with it. As of right now, I think the first option is the better of the two.

    @6C - Could you elaborate a bit more on this point? What specific problems do you forsee potentially happening?

    Finally, I have one more question for you. Would it be easier if the file was converted to a .csv and then imported into the Word table?

    Again, thanks for the response and I will await to hear from you. Let me know if you need more detail as to what I am looking to accomplish with this app.

    -DP

    Monday, April 30, 2012 1:25 PM
  • Hi DP

    @2: I'm not sure I understand what you're asking. You do not need copy/paste and, unless there's no other way to achieve a goal, should not use it. In this case, there are alternatives, so I, personally, wouldn't consider it. If the columns were re-ordered/deleted, then theoretically you could perform ONE copy/paste action instead of multiple ones - that's what my brain was working on.

    @6A: OLE DB isn't a language, it's a publicly available connection method (like ODBC). Any database can create an OLE DB provider that follows the standard. Any developer can use OLE DB to work with data via an OLE DB connection using the provider for that database type. So SQL Server has an OLE DB provider, as does Access/Excel, Oracle, etc. So if you're familiar with SQL, using an OLE DB connection to Excel shouldn't be a totally new thing. It differs in some details when specifying the connection properties (Excel is a workbook with worksheets, not a database with tables), but the actual coding System.Data is the same.

    @6C: Speed of execution could be a factor - automation is always slower. The danger that the automated Excel instance gets "orphaned" in memory (thing Task Manager/Processes) if your app should crash. User actions could interfere in certain scenarios. That kind of thing. The Office applications were designed as end-user products; the developer is a "second class citizen", which makes certain things non-intuitive and/or frustrating for the developer. It would work, but OLE DB would be "cleaner".

    RE csv: That could also work. The basic steps would be: import the file (it will come in as "plain text"); get hold of the range (how easy this is will depend on whether you're importing only one, or more than one row); convert the range to a table; make sure the table integrates correctly into the existing one (column widths, borders, whatever).


    Cindy Meister, VSTO/Word MVP

    Monday, April 30, 2012 4:34 PM
    Moderator
  • Cindy,

    @2 - That answers this question. Thanks

    @6A - So what would be the next step. I know you had mentioned this thread would be moved and that I should ask about the data connection in an Excel forum...

    -DP


    Join the darskide. We have cookies!

    Monday, April 30, 2012 4:46 PM
  • Hi DP

    At this point, given the length of the thread, I'd say

    - you mark messages in the thread as "Answers" and I move it to the Word forum. Thread closed.

    - you ask a new question in the Excel forum that clearly sets out what you need (saves people from having to wade through our discussion to find out why the thread is there and what's wanted). Tell them the Office version, that the user will d/l a Word and Excel document and that you want to extract data from the Excel file to use in Word. Your qusetion is: how do you use VB.NET to connect to the closed Excel workbook in order to extract the data?

    Once you've got that part working, if you're unsure how to populate the Word table, you can post a new question in the Word forum specifically covering that. If you feel it would be relevant, you can include links to this and/or the Excel discussion.

    Sound like a plan?


    Cindy Meister, VSTO/Word MVP

    Tuesday, May 1, 2012 7:44 AM
    Moderator