Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-13-2021, 06:35 AM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default 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
  • Paste (Link and Merge Formatting),
  • Paste Special (Unformatted Unicode Text), and
  • Paste Special (Ms Excel Worksheet Object),
methods but I realised none of these will auto update the changes once the Word doc is locked.

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
Reply With Quote
  #2  
Old 01-13-2021, 01:11 PM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #3  
Old 01-13-2021, 08:56 PM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default

Thanks, Macropod. Would this still work if I have to change the Excel file?
Reply With Quote
  #4  
Old 01-13-2021, 09:12 PM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #5  
Old 01-13-2021, 09:34 PM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 01-13-2021, 10:50 PM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #7  
Old 01-13-2021, 11:15 PM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 01-14-2021, 12:31 AM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #9  
Old 01-14-2021, 12:42 AM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default

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?
Reply With Quote
  #10  
Old 01-14-2021, 01:04 AM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
  #11  
Old 01-14-2021, 01:51 AM
kyotokisses kyotokisses is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2010
Novice
Exporting Selected Data from Excel to Locked Word File
 
Join Date: Apr 2020
Posts: 11
kyotokisses is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 01-14-2021, 07:04 AM
macropod's Avatar
macropod macropod is offline Exporting Selected Data from Excel to Locked Word File Windows 10 Exporting Selected Data from Excel to Locked Word File Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,816
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

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]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Exporting Selected Data from Excel to Locked Word File 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 Selected Data from Excel to Locked Word File Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet Labyrinth Word 7 07-19-2016 01:35 PM
Exporting Selected Data from Excel to Locked Word File * Exporting Access Data to Excel djreyrey Excel Programming 1 03-23-2012 10:03 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 10:26 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft