none
How to link an Excel spreadshet with financial data to a Word Document RRS feed

  • Question

  • Hello,

    I have an Excel spreadsheet with some financial data in a Balance Sheet. I would like to this data to a Word Document. For example, if the Excel spreadsheet show an amount of $1,000,000, I would like to show the amount in the Word document as $1,000 (in thousands, where the Excel data is divided by 1,000). How do I do this task? I tried to add another column in the Excel spreadsheet and divide the values by 1000. Afterwards, I copied & pasted special data as a unformatted text (paste special link) in Word. Is there a more efficient way of doing this instead of inserting additional columns in the Excel spreadsheet to divide the amounts by 1,000?

    Thanks,

    wire_jp



    • Edited by wirejp Friday, October 5, 2018 11:02 AM
    Friday, October 5, 2018 12:51 AM

Answers

  • The easiest approach would be to copy a given Excel cell and paste it into Word using Paste Special, with the 'paste link' option and your preferred paste format. Having done that, any changes to the Excel cell will be reflected in the document. If the Excel columns from which you collect the data change over time, you might consider setting up a worksheet with some fixed cells the document can link to (say, on another sheet), then have formulae in those cells that you can modify to point to the data of interest; otherwise you'd need to edit the links in Word - which is more involved.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by wirejp Friday, October 5, 2018 11:33 PM
    • Unmarked as answer by wirejp Monday, October 15, 2018 4:49 PM
    • Marked as answer by wirejp Tuesday, October 16, 2018 11:53 AM
    Friday, October 5, 2018 9:27 PM
  • Yes, a performance hit is likely, but so too do you get one by running a macro. Note that you can switch off Word's 'update automatic links at open' option if you don't want the links to update every time you open the document.

    Although the process could be done with a macro, that would require you to hard-code every Excel cell reference and corresponding document destination into the macro code. That, in turn makes it much harder to maintain a working solution.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by wirejp Tuesday, October 16, 2018 11:53 AM
    Monday, October 15, 2018 11:20 PM
  • How many of these data columns, or pairs of data columns, do you need to extract
     a. from a given sheet and
     b. into a given document?

    e.g. Do you have an Excel sheet with a column with the account names, then (say) 2 years of data, e.g. 24 months from Jan 2016 to Dec 2017? Or what? In the Word document, are you only trying to get two of the columns (plus account names, but modified I think you said) into the Word document, or are you doing something more like 12 pairs of two columns one for Jan, one for Feb, etc., each with account names. Or perhaps you have 12 separate documents, one for Jan, one for Feb, and so on, each drawing on the same sheet? (And then there is the question of what happens the year after, but perhaps I am jumping ahead too far :-) )

    Because I wonder whether you are trying to copy/paste link every cell individually, when it would be much more efficient to copy an entire block, or, if you have a lot of rows, potentially a number of blocks, each with enough rows to fill a page.

    There is also another way to extract data from a spreadsheet, but
     a. the linking is not exactly automatic - you have to update the field code that is used to do the link
     b. you don't get much control over the formatting unless you postprocess the table afterwards and
     c. because of a limitation, you would probably have to get the account name data from the sheet as well. In other words, if the names you need in Word are different from the ones you need in Excel, you would need to have both in Excel
     d. You really need to have a row of column headers at the top of your Excel data
     e. This approach doesn't work well if you have mixed data types in any of your Excel columns (e.g. a mix of numbers and texts) 

    The way to do that second one is to use a DATABASE field to insert the data in Word. For example, suppose you have a workbook called caccounts.xlsx in c:\xlwb, with a sheet called C201617 with columns like this...

    acxl        acwd      Jan2016 ..... Dec 2016 ...... Jan2017 ..... Dec2017

    where acxl contains your XL account names, and acwd (which could be a hidden column) contains your Word account names), you might insert a DATABASE field like this: 

    { DATABASE  \d "C:\\xlwb\\caccounts.xlsx" \s "SELECT [acwd], format(int([Dec2016]/1000),'$#,##0') As [Dec 2016], int([Dec2017]/1000) As [Dec 2017] FROM [C201617$]" \h }

    Where the {  } are the special field code brace pairs you can insert using ctrl-F9 in Windows Word, the backslashes in the pathname are doubled up, and the sheet name is followed by a "$".

    Database fields can be difficult to get right so it is sometimes simpler to build them up step by step. As an example, if you actually have two columns just called "Dec", without any year number, when they come into Word, they will probably be named "Dec" and "Dec1"

    To update the field results, you select the field and press F9. To switch from field code view  to results view you press Alt-F9.



    Peter Jamieson

    • Marked as answer by wirejp Tuesday, October 16, 2018 11:54 AM
    Tuesday, October 16, 2018 1:37 AM

All replies

  • You really haven't given us enough information. For example:
    • Do you want the document linked to the workbook, so that changes in the Excel data are reflected in Word?
    • How are the Excel data laid out?
    • How do you identify the destination(s) in Word where the data are to go?


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Friday, October 5, 2018 1:15 AM
  • Hi Paul,

    Thank you for your response. My responses to your questions are below:-

    • Do you want the document linked to the workbook, so that changes in the Excel data are reflected in Word? Yes, I want the Word document linked to the  workbook, so that the changes in the Excel data are reflected in Word.


    • How are the Excel data laid out? The Excel data is presented in columns (for each month of the year). The month name is the title of the column headings. On the left side of the data, one will find the account name. But I will be referencing the annual comparative data i.e. comparing December 2017 data versus December 2017 data.


    • How do you identify the destination(s) in Word where the data are to go? In Word, the data is placed in a Balance Sheet summary "table" in two comparative columns by month (a column for December 2017 data and a column for December 2016 data for comparison. The column headers will show the month 2017 and 2016. The account name is to the left of the data (but the account name descriptions will be slightly different. Please note that it is not really designed as table with a rows and columns, but just two columns of data)



    • Edited by wirejp Friday, October 5, 2018 11:29 AM
    Friday, October 5, 2018 7:13 AM
  • The easiest approach would be to copy a given Excel cell and paste it into Word using Paste Special, with the 'paste link' option and your preferred paste format. Having done that, any changes to the Excel cell will be reflected in the document. If the Excel columns from which you collect the data change over time, you might consider setting up a worksheet with some fixed cells the document can link to (say, on another sheet), then have formulae in those cells that you can modify to point to the data of interest; otherwise you'd need to edit the links in Word - which is more involved.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by wirejp Friday, October 5, 2018 11:33 PM
    • Unmarked as answer by wirejp Monday, October 15, 2018 4:49 PM
    • Marked as answer by wirejp Tuesday, October 16, 2018 11:53 AM
    Friday, October 5, 2018 9:27 PM
  • Hi Paul, 

    This is the approach which I have adopted: - in the Excel spreadsheet, I copied all of the data in an identical table in a section below and I linked each of the new table's data to the above (original) table's data and then I divided the new table data's amount by 1000. As you described above, then I linked the Excel data to the Word document. 

    Thank you for your help.

    Friday, October 5, 2018 11:33 PM
  • Hi Paul,

    My boss feels that this method of copying and using paste special to link the data from Excel to Word is too cumbersome and it will cause the Word document slow down, as it updates the links when the Word is opened. She would like both the numerical data and the text data (headings and the text descriptions of the numerical data) to be updated in the Word document. She is wondering if there is a macro program can be written to automatically update the linked data.

    Kind regards.

    Monday, October 15, 2018 4:49 PM
  • Yes, a performance hit is likely, but so too do you get one by running a macro. Note that you can switch off Word's 'update automatic links at open' option if you don't want the links to update every time you open the document.

    Although the process could be done with a macro, that would require you to hard-code every Excel cell reference and corresponding document destination into the macro code. That, in turn makes it much harder to maintain a working solution.


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by wirejp Tuesday, October 16, 2018 11:53 AM
    Monday, October 15, 2018 11:20 PM
  • How many of these data columns, or pairs of data columns, do you need to extract
     a. from a given sheet and
     b. into a given document?

    e.g. Do you have an Excel sheet with a column with the account names, then (say) 2 years of data, e.g. 24 months from Jan 2016 to Dec 2017? Or what? In the Word document, are you only trying to get two of the columns (plus account names, but modified I think you said) into the Word document, or are you doing something more like 12 pairs of two columns one for Jan, one for Feb, etc., each with account names. Or perhaps you have 12 separate documents, one for Jan, one for Feb, and so on, each drawing on the same sheet? (And then there is the question of what happens the year after, but perhaps I am jumping ahead too far :-) )

    Because I wonder whether you are trying to copy/paste link every cell individually, when it would be much more efficient to copy an entire block, or, if you have a lot of rows, potentially a number of blocks, each with enough rows to fill a page.

    There is also another way to extract data from a spreadsheet, but
     a. the linking is not exactly automatic - you have to update the field code that is used to do the link
     b. you don't get much control over the formatting unless you postprocess the table afterwards and
     c. because of a limitation, you would probably have to get the account name data from the sheet as well. In other words, if the names you need in Word are different from the ones you need in Excel, you would need to have both in Excel
     d. You really need to have a row of column headers at the top of your Excel data
     e. This approach doesn't work well if you have mixed data types in any of your Excel columns (e.g. a mix of numbers and texts) 

    The way to do that second one is to use a DATABASE field to insert the data in Word. For example, suppose you have a workbook called caccounts.xlsx in c:\xlwb, with a sheet called C201617 with columns like this...

    acxl        acwd      Jan2016 ..... Dec 2016 ...... Jan2017 ..... Dec2017

    where acxl contains your XL account names, and acwd (which could be a hidden column) contains your Word account names), you might insert a DATABASE field like this: 

    { DATABASE  \d "C:\\xlwb\\caccounts.xlsx" \s "SELECT [acwd], format(int([Dec2016]/1000),'$#,##0') As [Dec 2016], int([Dec2017]/1000) As [Dec 2017] FROM [C201617$]" \h }

    Where the {  } are the special field code brace pairs you can insert using ctrl-F9 in Windows Word, the backslashes in the pathname are doubled up, and the sheet name is followed by a "$".

    Database fields can be difficult to get right so it is sometimes simpler to build them up step by step. As an example, if you actually have two columns just called "Dec", without any year number, when they come into Word, they will probably be named "Dec" and "Dec1"

    To update the field results, you select the field and press F9. To switch from field code view  to results view you press Alt-F9.



    Peter Jamieson

    • Marked as answer by wirejp Tuesday, October 16, 2018 11:54 AM
    Tuesday, October 16, 2018 1:37 AM
  • Hi wirejp,

    Did Paul's answer resolved your problem?
    You can mark it as answer if it helped and please help us close the thread. 

    Best Regards,

    Yuki


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, October 16, 2018 5:52 AM
    Moderator