Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

Office Design for PPT DOC and XLS Integration Sample Design

I'm attempting to consolidate data in an Excel Spreadsheet, automatically into a Word Document and PPT.  I've attached three sample files.

1.) I know they must all go in the same folder to have the auto updates (without a path).  I am hoping it doesn't have to reside on the desktop.
2.) I do not know if I need to store these as templates or just simply a doc (macro enabled?) and ppt (macro enabled)?
3.) From the sample, you will see that it starts with data in the spreadsheet and I've enclosed the identifiers as <NAME>

Any help in working through this sample is much appreciated.

B. Excel-Data.xlsm Excel-Data.xlsm
Proposal-for-Major-Equipment-Acq.docx
THE-PROPOSAL.pptx
0
Bright01
Asked:
Bright01
  • 8
  • 6
  • 3
  • +1
1 Solution
 
Echo_SCommented:
With PowerPoint, you generally create an add-in. It's very unusual to see a macro in a PPT file the way you would in Word and Excel because it's hard to run a macro automatically in PPT. Here is info on PPT and macros/add-ins: http://www.pptfaq.com/FAQ00031.htm

I would actually look at something like PPTMerge to get the Excel data into the PPT file. http://www.pptools.com/merge/index.html 

You'd just have to have a PPTX file if you approach it using an add-in.
0
 
Bright01Author Commented:
If I use an add-in (which one?) does that mean that everyone who uses the integrated set of three files must also have the add-in installed or once it's installed and embedded, someone can simply open the ppt and the proper information will be posted from the Excel file?

B.
0
 
SiddharthRoutCommented:
B

>>>>1.) I know they must all go in the same folder to have the auto updates (without a path).  I am hoping it doesn't have to reside on the desktop.

No it is not required. The user can keep that folder wherever he/she pleases to and can :)

>>>>2.) I do not know if I need to store these as templates or just simply a doc (macro enabled?) and ppt (macro enabled)?

No, they don't need to be macro enabled unless you want the finished product to run a macro? All the macros will be run from Excel.

>>>>3.) From the sample, you will see that it starts with data in the spreadsheet and I've enclosed the identifiers as <NAME>

I am stepping out for some time. Will have a look at the above sheets soon :)

Sid
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Echo_SCommented:
Well, B, it depends. You've really just thrown three files up here -- the workflow is pretty vague at this point.

You'd run the macro in one program or the other to create the final documents. The macro or add-in would have to be installed on the creator's machine -- run it to populate all the files (Word and PPT?) with the info generated from the Excel sheet. But other users should be able to open the final files and see them populated with the information.

I think SiddharthRout is probably correct that you want to run the macro from Excel to push the info to the other applications.

The other option is to run a macro in Word to pull the info from Excel and run a macro (which is really an add-in) in PPT to pull the info from Excel. But going from Excel to Word/PPT would be one less step.
0
 
Bright01Author Commented:
Echo,

Thank you for the reply.  I agree.  Have Excel do  the calculations and place the results in particular cells that then auto populate both Word and PPT.  The three files that I threw up on the share represent a very simple example of several Excel fields that with the same ID, are in a DOC and PPT.  What I don't know how to do is link them.  According to your previous post, I know they have to be in the same file but I don't know how to take data that is in one cell of the spreadsheet (Take any of them) and integrate the data with Word and Powerpoint.

B.
0
 
Echo_SCommented:
Sorry, I don't know how to do that in PowerPoint, either. Hopefully someone who does will pop in.

Generally speaking, you could copy the Excel cell and paste special (as Excel Worksheet Object) and click the Paste Link option. I'm not sure that's really the best method here, though.

That's why I mentioned PPTMerge -- it does more like what I think you're after. But it's run from the PPT side of things, not the Excel side.
0
 
Arno KosterCommented:
it took me a while, but this should do the trick

Excel-Data.xlsm
0
 
Bright01Author Commented:
akoster,

Thank you.  Need some clarification on this.  

1.) This is all hard coded.  I used the sample to try to uncover how to simply have data in a cell transferred to specific tags in a DOC and PPT.  If I try to use this with a macro, I'm going to have to change the Macro each time I add a field in Excel that I want to auto populate.  I thought "smarttags" was the MS way to get this done.

2.) I copied Sheet one back into my XLSM and tried to run it.  The macro also copied over.  Nothing happened (Macros are enabled).

Any way to address both of these issues?  I'd like to try your macro; but I'm still looking for a simple solution.

B.
0
 
Arno KosterCommented:
Well, you somehow have to define how to relate certain fields in excel to specific text fragments in powerpoint and word.
When you want to change fields on a regular basis, you could modify the macro to use cell values to define the relations between excel & powerpoint/word, instead of typing them out.

As i understand it, smarttags are used to give a user contextual information related to certain words in a document. eg. when you type an email adress in word, you get the option to send a mail to that address by right mouse clicking, of to add the adress to your contacts. You might be able to turn this around, but then you have  the same difficulties in relating source and target.

As to number 2, I tried to avoid handcoded references as much as possible. when you click on cell D3, the cell containing the client name, you will see that is has been given the name "item_name". The cell in which the logo picture is placed is named "item_logo". Although this information should have been copied together with the sheet, i am not sure if this is the case in your document.

When you double click on the button to start the macro, you should see the excel statusbar mention lines like

extracting items from excel
opening word application
etc.

can you verify that the statusbar indeed displays these values ?



0
 
Bright01Author Commented:
Greetings akoster.  Perhaps I'm asking for something that doesn't exist in Office.  Or perhaps we're not coming at this from the right angle.  Would creating a "Template" be a way to auto-populate both the Word and Powerpoint item from Excel?

Several updates/questions;

I opened all three files in the same file folder.  I enable Macros.  I double click on "Process".  I get a debug error that highlights the code;

Set wrd_doc = wrd.documents.Open(ActiveWorkbook.Path & "\" & doc)

Is there a way to have a macro where I can declare a name (i.e. range name) and have it auto populate the DOC and PPT without all of the declarations?

e.g.  Application.StatusBar = "exporting name item"
        wrd_sel.Find.Text = "<CLIENT NAME>"
        wrd_sel.Find.Replacement.Text = item_name
        wrd_sel.Find.Execute , , , , , , , , , , wdReplaceAll

There should be a way of declaring a variable name and having it populate another Office application without having to code it in two places.

B.
0
 
SiddharthRoutCommented:
Bright01:

I saw your file finally and I have few suggestions around it. Let me know if you are ready to discuss it.

Sid
0
 
Bright01Author Commented:
I'm ready.  I think Akoster has done a good job if I have to leverage macros; however, I'd like to design a simpler solution..... hard to believe that MS doesn't have a solution to this that's easy to implement.

B.
0
 
SiddharthRoutCommented:
>>>hard to believe that MS doesn't have a solution to this that's easy to implement.

You are right. There is a much simpler solution. I am creating a sample for you :)

Sid
0
 
SiddharthRoutCommented:
Ok Here is the sample.

I deleted all the Tags in the word document and replaced them with what are call form fields. I also inserted a book mark called "Logo" at the place where you want the image. This way you don't need to search for your tags :)

Also the form fields look grey in color in design mode but when you print it that color is not printed :)

Place both the files in the same directory and then click the button in the Excel file :)

Hope this is what you wanted?

Sid

Code Used

Private Sub CommandButton1_Click()
    Dim oWordApp As Object, oWordDoc As Object
    Dim FlName As String
    Dim sh As Shape
    
    FlName = ActiveWorkbook.Path & "\Proposal-for-Major-Equipment-Acq.docx"
    
    '~~> Establish an Word application object
    On Error Resume Next
    Set oWordApp = GetObject(, "Word.Application")
    
    If Err.Number <> 0 Then
        Set oWordApp = CreateObject("Word.Application")
    End If
    Err.Clear
    On Error GoTo 0
    
    oWordApp.Visible = True
    
    Set oWordDoc = _
    oWordApp.Documents.Open(FlName)
    
    With oWordDoc
        '~~> Word Doc Form Fields
        .Formfields("ClientName1").result = Sheets("Sheet1").Range("D3").Value
        .Formfields("ClientName2").result = Sheets("Sheet1").Range("D3").Value
        .Formfields("EXECContact").result = Sheets("Sheet1").Range("D5").Value
        .Formfields("Title").result = Sheets("Sheet1").Range("D7").Value
        .Formfields("Date").result = Sheets("Sheet1").Range("D6").Value
        .Formfields("Project").result = Sheets("Sheet1").Range("D8").Value
    
        For Each sh In ActiveSheet.Shapes
            If sh.Type = 13 Then
                sh.Copy
                .Bookmarks("Logo").Range.Paste
                Exit For
            End If
        Next
    End With
End Sub

Open in new window

Excel-Data.xlsm
Proposal-for-Major-Equipment-Acq.docx
0
 
Bright01Author Commented:
Sid,

Just tried it out and it works well.  My question to you is how easy is this to expand/maintain?  For example, a proposal may be 25  pages long with 95% text (standard and "to modify").  It will  also have graphics but I'm assuming that the graphics are driven by Excel and again, like the logo(??) copied into the DOC?  There will be standard places where the excel data just fills itself in.   Will I need to modify the macro each time I add a Form element?  How easy is it?  Do I use the same approach for PPT?

TY,

B.
0
 
SiddharthRoutCommented:
>>>> How easy is it?  

It's very easy :) Every time you add a form field just add one line to the macro

.Formfields("FORMFIELDNAME").result = Sheets("Sheet1").Range(CELLADDRESS).Value

>>>> Do I use the same approach for PPT?

I haven't checked ppt yet. Wanted to sort the word first :) Once you confirm that the word file is working then we will move to PPT.

Sid
0
 
Bright01Author Commented:
Sounds simple!

I'm trying it out.  How do I add a Form Field?  I can't find it on the ribbon or on any of the bars.

B.  
0
 
SiddharthRoutCommented:
Nothing like a video ;-)

Sid
SiddharthRout-448663.flv
0
 
Bright01Author Commented:
Great Job Sid.  Much thanks........big help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now