Link a workbook to a presentation - Reporting in Excel and ...



LinkGetting StartedOfficeReports v6 – April 2016 TOC \o "1-3" \h \z \u Link a workbook to a presentation PAGEREF _Toc447816722 \h 3Linking an Excel Range to a Table in PowerPoint PAGEREF _Toc447816723 \h 4Linking an Excel Range to a Chart in PowerPoint PAGEREF _Toc447816724 \h 5Linking an Excel Range to a Shape in PowerPoint PAGEREF _Toc447816725 \h 6Conditional formatting in PowerPoint (e.g. Heat maps) PAGEREF _Toc447816726 \h 7Logos, Icons and Sticky Colors PAGEREF _Toc447816727 \h 8Settings PAGEREF _Toc447816728 \h 8Logos PAGEREF _Toc447816729 \h 8Icons PAGEREF _Toc447816730 \h 8Sticky Colors PAGEREF _Toc447816731 \h 9Reusing the presentation (Automation) PAGEREF _Toc447816732 \h 10Change Data PAGEREF _Toc447816733 \h 10Repetitive Reports PAGEREF _Toc447816734 \h 10Flexibility: Linking variable sized ranges PAGEREF _Toc447816735 \h 11Using Macros PAGEREF _Toc447816736 \h 11Link a workbook to a presentationIn order to link a workbook to a presentation, do the following:Make sure the workbook you want to link is closed!In PowerPoint, click ‘Link Workbook’ in the OfficeReports ribbon, and choose the workbook.454660182880If the workbook does not have the same name as the presentation, a window will popup telling you that OfficeReports will make a copy of the workbook and give it the same name as the presentation. The workbook will open, and is now linked to this presentation. After linking, the ribbon changes:-2540635Clicking ‘Show Workbook’ will get the workbook in focus. Clicking ‘Remove Link’ will removed the link, and the ribbon will change back showing a ‘Link Workbook’ button again. We will explain the ‘Change Data’ button and all the other buttons in the rest of this document.Note: OfficeReports will open the workbook when necessary. In case you already have opened the workbook yourself, OfficeReports will ask you to close the workbook before continuing. OfficeReports can only access the workbook, when OfficeReports has opened the file.Linking an Excel Range to a Table in PowerPointTo populate an existing table in PowerPoint, do the following:Click ‘Show Workbook’ and select the range you want to show in the table.In the presentation, click in the table cell where you want the range to start appearing.In the OfficeReports ribbon, click ‘Table->Link Excel Range to selected Table’454660183515The contents from the Excel range will appear in the table, and the ‘Edit Link’ window pops up: -2540185420OfficeReports does not just link to the data. It is also possible to use some of the settings from Excel. We will explain the other options in chapter “ REF _Ref447723864 \h Logos, Icons and Sticky Colors”. OfficeReports names the range you link to make sure the link still works, even if you add rows and columns in the sheets in the workbook. (see Excel Named Ranges)Video example: link to YouTubeNote: The same window will appear when selecting this table in the presentation and clicking ‘Edit Selected Infographic’ in the OfficeReports ribbon. Now you can change the link settings.Note: If you change the contents in the workbook, and click ‘Apply’ in this window, or click the ‘Refresh’ button in the OfficeReports ribbon, the table will be updated with the contents from the workbook. Note: It is possible to populate multiple Excel ranges in the same PowerPoint table!Linking an Excel Range to a Chart in PowerPointTo populate an existing table in PowerPoint, do the following:Click ‘Show Workbook’ and select the range you want to show in the chart. In the presentation, select the chart.In the OfficeReports ribbon, click ‘Chart->Link Excel Range to selected Chart’454660184150The contents from the Excel range will appear in the chart and the ‘Edit Link’ window pops up again (see “ REF _Ref447724574 \h Linking an Excel Range to a Table in PowerPoint”).Note: The same window will appear when selecting this chart in the presentation and clicking ‘Edit Selected Infographic’ in the OfficeReports ribbon. Now you can change the link settings.Note: If you change the contents in the workbook, and click ‘Apply’ in this window, or click the ‘Refresh’ button in the OfficeReports ribbon, the chart will be updated with the contents from the workbook. Note: Using the “Fixed” option, OfficeReports will not change the data range used by the chart. This means the data you populate has to look exactly like the data behind the chart to start with. This enables easy populating of Combo charts and other advanced charts.Note: If you do not use the “Fixed” option, you can populate any number of rows or columns in the chart. OfficeReports will tell the chart which data to use. If possible, also populate the category texts.Linking an Excel Range to a Shape in PowerPointTo populate an existing shape in PowerPoint, do the following:Click ‘Show Workbook’ and select the range you want to show in the shape.In the presentation, select the shape.In the OfficeReports ribbon, click ‘Chart->Link Excel Range to selected Chart’The contents from the Excel range will appear in the shape and the ‘Edit Link’ window pops up again (see “ REF _Ref447724574 \h Linking an Excel Range to a Table in PowerPoint”).Note: If you select multiple cells in Excel, OfficeReports will add a space between the contents of cells on the same row, and a newline for each new row.Note: Also in shapes it is possible to show logos and Icons!Conditional formatting in PowerPoint (e.g. Heat maps)By checking ‘Use background color’ in the ‘Edit Link’ window, even conditional formatting definitions in the workbook are made visible in the presentation. Not as a picture (distorting the fonts), but in a native PowerPoint Table!19431057658000Logos, Icons and Sticky ColorsSettingsBefore OfficeReports can add logos and icons to charts, tables and shapes, you have to tell OfficeReports where to find them. Do this by clicking “Images/Logos” in the OfficeReports ribbon in PowerPoint. You can define 2 folders in which OfficeReports will look for the images and logos. Please use the 'common' folder for images that you use generally across projects. The 'project' folder is for images only used for this specific presentation.LogosOfficeReports can replace texts in tables, charts and shapes by images/logos: Check 'Use Image/Logo' in the ‘Edit Link’ windowOfficeReports will look for image files in the folders defined below with the same name as the texts in the table, chart or shape. If OfficeReports finds such a file, the text is replaced by the image/logo.Note: If you are a bit creative in Excel, you can show a specific smiley in PowerPoint depending on a specific value in Excel IconsOfficeReports can add icons to tables, charts and shapes, depending on the background color of the linked cells in Excel. You define which icon to use for a specific background color by clicking “Images/Logos”.Click the ‘Icons’ button and check 'Use Icons' in the ‘Edit Link’ windowOfficeReports will try to find the background color in the defined ‘Icon List’. If the color is found, the matching icon from the image folders is displayed in the table, chart or shape.3947160571500-40640121920002759710196850LINKLINK46126401019175To PowerPointTo PowerPoint321310948055From ExcelFrom ExcelSticky ColorsIn the same way as OfficeReports applies logos to tables, charts and shapes, you can also define colors for specific texts. Do this by clicking “Sticky Colors” in the OfficeReports ribbon.Check 'Use Sticky Colors' in the ‘Edit Link’ windowOfficeReports will try to find the text in the defined ‘Sticky Colors’ list. If the text is found, the matching color is used in the chart or shape (does not work for tables yet).-26987564579500Reusing the presentation (Automation)Change DataUpdating the presentation with new data is easy:Click ‘Change Data’ in the OfficeReports ribbon.Select the workbook containing the new data in the File Dialog.Click ‘Refresh’ to update the presentation with the new data.Note: OfficeReports copies the data (only values, not formulas) from the selected workbook into the linked workbook. This means the formulas, conditional formatting and other definitions made in the linked workbook will still work!Note: OfficeReports copies data from sheets to sheets in the attached workbook with the same name. Sheets in the attached workbook without a matching sheet in the selected workbook are not changed.Repetitive ReportsIn case you need to create many reports from different workbooks, you can use the “Repetitive Reports” function:Click ‘Repetitive Reports’ in the OfficeReports ribbonIn the window popping up, select the folder where the different workbooks are.In the list box, select the workbooks for which you want a presentation generatedCheck ‘PDF’ if you want the presentation saved as PDF file.Click ‘Ok’ and OfficeReports will generate the requested reports.The presentations are generated in exactly the same way as if you had used ‘Change Data’ for each report.Flexibility: Linking variable sized rangesIf the number of rows or columns are variable, click the ‘Advanced’ button in the ‘Edit Link’ window, and you will have the following options:Using MacrosIf our standard functionality is not enough for you, it is possible to define to run a specific macro anytime OfficeReports updates a link. This makes it possible to create any kind of table/chart and automate any kind of report mass production. It goes beyond the scope of this ‘Getting Started’ manual to explain in detail. If you are interested in this functionality, please contact us.Watch the videosPlease have a look at our videos. They will make everything clear to you!General introduction to LinkAutomation ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download