/
Excel and Access Refresh Files

Excel and Access Refresh Files

Jul 27, 2020

Report Web Services

Report web services are a preferred alternative to data refresh files. They offer much of the same functionality, without the reliance on a shared network location nor the need to manually resave files out from Projector.

 

The dataset refresh functionality within Projector allows you to connect an Excel file to an Access database file. This provides additional flexibility to users who wish to customize their reports using formulas, formatting and combining data from multiple Projector reports.


Create a report and link it to a data refresh file

  1. Go to the Reports tab of the Projector Management Portal

  2. Double click to open a report and check that it supports data refresh files

  3. On the Output tab select Data Refresh File

  4. Leave the default setting, "Spreadsheet" selected for now and run the report with whatever options you wish. Please keep in mind that you will not be able to change the field settings later on and use the data refresh functionality.

  5. Once the report completes save it to your computer

  6. Run the same report again, but this time on the Format tab enable the "Data refresh file" radio button.

  7. Save the data refresh file (mdb) to your computer.

  8. Open your original Excel Report

  9. Click anywhere inside your Pivot Table and the "PivotTable Tools" ribbon menu should appear in Microsoft Excel

  10. Choose Options -> Click Refresh (not Change Data Source)

  11. A popup window appears. Navigate to the data refresh file (.mdb) you created earlier

  12. The spreadsheet updates with the new data. If you want to update the data again you can overwrite the .mdb file and click the refresh button again

Refresh a report that uses a data refresh file

  1. From the Management Portal save your .mdb file and overwrite your old mdb file. If you do not wish to overwrite your old mdb file then save as a new file and follow the instructions for Updating a report to point at a new .mdb file.

  2. Open your report in Excel

  3. Click in your report data so that the PivotTable tools appear in Excel

  4. Select the Options tab under PivotTable Tools

  5. Click Refresh

Update a report to point at a new .mdb file

  1. If you want to point the spreadsheet at a different .mdb file click the drop arrow below refresh and choose "Connection Properties."

  2. Under the Definition tab change the file path to the new .mdb file.