#1
|
|||
|
|||
Exporting Selected Data from Excel to Locked Word File
Hi all, I'm finding a way to export data from selected cells in an Excel workbook into a Word doc (which I hope to lock). The cells containing the data to be exported into the Word file are basically everywhere in the Excel workbook. I've tried the
Is there anyway to go about updating the data in the Word doc automatically each time the Word doc is opened and at the same time lock the Word doc so that it does not get tampered with? Below is a sample of some of the info to be exported from the Excel but there is more info in the same workbook. 1.png And a sample of some of the info to be input into the Word doc. 2.PNG |
#2
|
||||
|
||||
You apply 'no changes (read only)' protection to the document and mark each link as an exception, it will continue to update.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Thanks, Macropod. Would this still work if I have to change the Excel file?
|
#4
|
||||
|
||||
It's not clear whether you mean 'edit' the Excel file or reference a different one. Either should be possible.
If editing is liable to result in a change in cell addresses, it'd be best to name each cell and refer to the range name in the Word link. That way, the link will follow wherever the range name in Excel points to. To use a different Excel file, you'd need to use Word's Edit Links function, which isn't normally visible. You can add its button to the QAT, or access it via File|Info.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Sorry about the confusion. What I meant was the Excel file is usually used to generate different client's information, before we churn out the final contract, so each XLS would be different. I think I'm going with the more tedious but safe method to make people export the data before they save a separate Excel for each client.
If I still need the prompt for loading x number of rows in my table, would this lock and ref method still work or do I have to write a complex VBA for it? Referring to my earlier post here. https://www.msofficeforums.com/word-...ng-prompt.html |
#6
|
||||
|
||||
Is there any particular reason the Word file needs to be linked to the Excel file? If not, a macro could be used to add and populate however many rows your table needs, after which the document could be secured against further changes.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
The purpose of linking to the Excel file is to minimise typo from having to retype the information into the Word doc, so unfortunately there's no real workaround for this other than to link the info or to type it out again manually.
|
#8
|
||||
|
||||
As per my previous reply, a macro can transfer whatever data you need from Excel to Word. Hence typo risks in that regard should be non-existent.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Would any of the earlier replies help for exporting 1 table if it's just that 1 table in the Excel that requires additional rows?
E.g. exporting data from table 1 in Excel (Cells B1 to F3) to the Word doc? |
#10
|
||||
|
||||
If the Excel table has the same format as the Word one, it's just a basic copy/paste; if not, it's still quite possible to iterate through the Excel range and write the data to Word, adding rows to the Word table as needed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
|||
|
|||
Thanks Macropod. The tables are not very different in that there is only 1 additional column that I want to omit from the Word doc but the number of rows is subject to change. I tried one of the macros you created for another user, and got an error as below.
2.PNG 1.PNG Any idea what I'm doing wrongly? To add on, I've split the whole table into 2, with the rent table being the last item on the first table. I'm thinking it might be the lock feature that I'm using that's causing this issue but unfortunately I'm very very new at VBA and unable to write complex macros to make the info export and row addition work together. |
#12
|
||||
|
||||
That suggests you tried to run the macro manually, instead of firing it from a formfield in the table.
Without seeing your document - and ideally a sample workbook as well - it's difficult to give tailored advice. You can attach a document to a post with some representative data (delete anything sensitive) via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Exporting data to an excel template | Stevie23 | Excel Programming | 3 | 04-05-2018 12:12 AM |
Macro for exporting Word data to an excel register | Seb | Word VBA | 12 | 03-30-2017 07:30 PM |
Exporting multiple data sets from a single Word Form into Excel | Tom Saylor | Word VBA | 6 | 09-11-2016 05:22 AM |
Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet | Labyrinth | Word | 7 | 07-19-2016 01:35 PM |
* Exporting Access Data to Excel | djreyrey | Excel Programming | 1 | 03-23-2012 10:03 PM |