Want to have data linkefrom excel into a word document in a certain way, is this possible?

Hi,

At work every evening we need to create a report of the day's productivity and activity, about 15 minutes is spent just copying the data already spread across various sheets on an excel document into an email to all the staff, and i was thinking there must be a way to make this easier, by somehow linking this data from excel into fields in a word document, which can then be used as a template for the day report.

I need various conditions to be met.

- Firstly, i want it to basically go into a "template" sort of structure, so the numbers from excel would need to go in as if they were typed text (i.e. not in any form that couldnt be copied into an email program) for example it would be "Todays productivity was held at [would need to insert a designated excel cell here, i.e. 85%]

- Secondly the excel file this would be coming with is set up with hyperlinks (which appear to be hidden, but i believe just link through to different sheets in the workbook, so different sheets would need to be accounted for (i.e. the specific location of the cells would need to be specified)

-And finally, this may be the most challenging.... the excel file the data would be coming from is a new one each week (i.e. we'll have one outlining the plans and final data for each day at the start of each week). They are all the exact same template each week, from the same core file i'd imagine, but just with different numbers in the fields to reflect the plans for that week. Whilst i was thinking the above would be feasible, would the fact that it would be a different file name each week mean if i were to link all the cells to a word document.... would i have to do this all over again? Or would there be a way of basically telling word to look for those cells in the file called "week commencing 21/10/13.xls" instead of the previous week's "week commencing 14/10/13.xls"


Your help is greatly appreciated!

Originally Posted by mrice

Would it not be easier to get Excel to directly compile and send the e-mail rather than going via Word?

If the code was in a static workbook which was opening in the same Excel instance as the weekly sheet, this would meet the requirement of changing the sheet.

Is Outlook being used as the e-mail client?

Hi, Thanks for responding!

No, we use a a different 3rd party mail client so outlook is not an option.

I want it compiled in word since this only makes up a part of the report, and so therefore other comments and details need to be added to the report separately.

Also, the weekly plans are done all separately by head office miles and miles away from us, and so i have no hand in editing them or asking for this code to be added, also some of the fields are un-editable by us (to ensure we dont fiddle with the equations they use to interpret our data into final figures for output, etc.), so this would have to be something that is done outside of the workbook itself

Originally Posted by mrice

I suspect that it will be easier to get Excel to push its data into Word rather than Word pulling it from Excel.


A neat way of doing this is to put some distinctive placeholders into a copy of your Word template. In Excel, your code will need to reference the Word Object Library and then you can use the Word objects to open your template and sequentially replace the placeholders with the data from your Excel workbook using VBA to drive the Word find/replace functionality.

Hope that this makes sense.

I'm just about keeping up with you here! (I am reasonable with excel in its basic/normal functions, but this stuff does go a bit beyond that!) From what i understand, In order to get excel to push it though, wouldn't that first require me to have full write privileges on the excel file (which i don't, as certain cells and parts of the file are locked/read only), and then that would also mean I'd need to edit each excel file as it came in each week to put this stuff into the file.

Is there no way of making word pull it, but allowing me to easily just change the file name it pulls from as the new file comes in each week, all with the same cell/sheet locations & references as the week before, but just from a new file?

Originally Posted by mrice

You could get word to pull but, from a personal point of view, writing VBA in Excel is easier that in Word.

I would suggest that any Excel 'push' code is lcated in a separate workbook so all you need is to read from the restricted weekly sheet.

I've attached a couple of files.

Save the Word file to c:\temp

Then run the Test macro in the workbook.

The code creates a Word application, opens the template and substitutes three values. I've made a reference to the Word 14 object library. (VBA editor - tools - references)

Dim WordApp As Word.Application
Dim WordDoc As Word.Document


Sub Test()
Set WordApp = New Word.Application
Set WordDoc = WordApp.Documents.Open("C:\temp\template.docx")
WordApp.Visible = True

With WordApp.Selection.Find
    .Text = "#A1#"
    .Replacement.Text = Range("A1")
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
WordApp.Selection.Find.Execute Replace:=wdReplaceAll

With WordApp.Selection.Find
    .Text = "#B1#"
    .Replacement.Text = Range("B1")
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
WordApp.Selection.Find.Execute Replace:=wdReplaceAll

With WordApp.Selection.Find
    .Text = "#C1#"
    .Replacement.Text = Range("C1")
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
End With
WordApp.Selection.Find.Execute Replace:=wdReplaceAll

End Sub

Thanks for your help! Can't access those at the moment since im using a mac and it doesnt seem to happy with it, but will give it a go when i get on a windows computer!

Could you clarify what you mean by "so all you need is to read from the restricted weekly sheet" could you clarify what you mean by this? (Sorry to be a pain! All of this is quite new to me!)