+ Reply to Thread
Results 1 to 8 of 8

Getting a Word table back into Excel for editing

  1. #1
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Getting a Word table back into Excel for editing

    Hello once again

    This time I have an Excel workbook that when the user fills out information on a user form, the data is placed on a worksheet and exported to Word as a table. So far, so good. There is an additional user form to update existing records on the worksheet. Once finished VB updates the worksheet and then opens the related Word document, again for updating.

    The Word document is opening but my error message is fired that says "No tables found", even though there is one there. This is where the system falls over.

    I've tried several methods of getting the document opened, updated, and saved. None have worked so far. The code that follows is the portion used to open the Word document after updating Excel. Until I learn how to directly edit the Word document, the table values are supposed to be copied back to Excel and then re-exported with the updated information.

    If someone is interested in looking through the entire workbook, I can upload it. All of the file paths and document names would need to be modified in order to run the routines.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Getting a Word table back into Excel for editing

    Your code looks good.

    What happens when you step through it?

    If it runs fine, then it's a timing issue. You'll have to a 1 second or two delay to let Word load all its underlying code.

    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

  3. #3
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Getting a Word table back into Excel for editing

    Hi Tinbendr, we meet again.

    First, thank you for taking a look. Thought maybe this one was going to go unnoticed. I tried adding your suggestion but it didn't seem to help. That given, I wasn't sure where to fit it in so I tried a couple of different methods.

    For testing purposes I have the routine that opens the Word document in a module and the cmdUpdate_Click routine calls it. Once this is worked out I will likely move the code into the form code rather than the module. While I'm getting better, debugging and error interpretation is still a bit frustrating for me.

    If you're interested in looking, I have uploaded the Excel file. The command button, Update Record, on the ECRList sheet is what runs the routine and opens the form. This is all very messy as it's my first attempt at getting excel and word to work together through VBA. Also included one of the Word files.
    Attached Files Attached Files

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Getting a Word table back into Excel for editing

    Hi Tinbendr, we meet again.
    So it seems, but then I do a lot of work just like this, so i guess it's only natural.

    It worked out of the box for me.

    I changed this:
    Please Login or Register  to view this content.
    Added the question mark for trailing edge extensions. (docX, docM, docB)

    (I've come to realize if was a doc file with the extension changed to a docx.)

    If you want to narrow it to docX files, for example, change the question mark to a X.

    You should fully qualify this with the worksheet name, just in case, there's a conflict with Word Cells.
    Please Login or Register  to view this content.
    Your row/column do no match between Excel and Word either. Number of Row/Columns are off by 2 and 1.

    Please Login or Register  to view this content.
    You also can't iterate Word merge cells via row/column without generating an error (Excel too, for that matter) . E.G., if you try to reference Cell 4,2 (Row 4, Column 2) when the whole row is merged, it'll generate an error. You can turn error trapping off, but I prefer to create different groups for loops for each section. I mean, loops certainly bring an air of sophistication to your code, but nothing beats Plain Jane.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Getting a Word table back into Excel for editing

    Thanks for all the suggestions, I will work them in today and get back to you, and all.

    Rob

  6. #6
    Forum Contributor
    Join Date
    04-20-2017
    Location
    Iowa
    MS-Off Ver
    2007
    Posts
    212

    Re: Getting a Word table back into Excel for editing

    Think I'm getting closer. I've not figured out where this code needs to be inserted/changed though.

    Please Login or Register  to view this content.
    This may well be the reason I get the following error: "Requested member of the collection does not exist". Clicking Debug highlights the following line in the ImportWordTable sub.
    Please Login or Register  to view this content.

  7. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Getting a Word table back into Excel for editing

    So, either use the loops, or use the code I provided.

    The code I provided is only a fraction of what you'll need to pull the whole table.

    If you use the loops, you'll have to turn off error trapping at the top of the loops with:
    Please Login or Register  to view this content.

    And turn it back on at the end with:
    Please Login or Register  to view this content.
    The only problem with turning it off is that if there is any error, it won't stop to tell you. It will just plow through the code and do the best it can.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Getting a Word table back into Excel for editing

    As an alternative to running a macro within Excel, you can copy the range B3:F26 and paste it into a Word document:
    Paste Special > Microsoft Office Excel Worksheet Object (option: Paste Link)

    Once the worksheet form is linked to the Word doc, you have two update options from within the Word doc itself without the Excel workbook being open:
    1. Right click the form and choose "Linked worksheet Object", Open link
    which will open the Excel sheet for editing, save and close Excel. The Word doc is updated.

    2. If the worksheet has been modified previously, you may choose "Update Link" and the Word doc will be updated with the Excel wb closed.
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to copy a Cell from Excel into Word, edit it as RTF and then save back in Excel
    By peterjgoodwin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-10-2013, 06:42 PM
  2. VBA code that will follow excel hyperlink to word doc & copy text back into excel
    By crnadeau4 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-22-2013, 11:51 AM
  3. Use Excel VBA for editing and find/replace in a Word document
    By drdavidge in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2013, 06:10 AM
  4. Creating/editing a table in an embedded word document
    By destructive_engineer in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2012, 10:13 AM
  5. Word to excel then result back to word?
    By jubangy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-20-2008, 11:22 AM
  6. [SOLVED] Editing Word Document within Excel
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2006, 04:25 PM
  7. editing an excel 97 file within excel 2003 and saving back as exce
    By Chris Egebrecht in forum Excel General
    Replies: 3
    Last Post: 03-24-2006, 07:20 PM
  8. [SOLVED] Excel to Word and back again
    By Rominall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2006, 12:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1