Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  1. In Excel, Create a New File.
  2. You will have a blank Excel file now.
  3. Go to a blank tab in your workbook. Rename that sheet "Raw Data".
  4. Select the top-left cell (A1).
  5. Now we will pull in the data from the Excel file
  6. Choose the Data menu
  7. Choose From Web in the "Get External Data" section
  8. Be sure you have Copy link address from step 2 above so it is on your clipboard.
  9. The "New Web Query" window appears.  
  10. In the "Address" bar at the top, Paste the link you address you copied in Step 2 above.  It should be something like "https://www.commcarehq.org/a/excelreportingdemo/reports/export/saved/download/96c41fd6fb9be6119ad32467254cbc9a/"
  11. Click Go
  12. The "Windows Security" window appears. Type your CommCareHQ username and password.
  13. The export will be displayed to you in the Web Query Window
  14. Click the second Yellow Arrow  above the table of data (not the very top one). This will select all the CommCare data in the export.
  15. Click Import


  16. The "Import Data" window appears.
  17. Click Properties
  18. The "External Data Range" window appears
    1. In the top of the window, name your export "Raw Data"
    2. Choose Save query definition
    3. Choose Untick Enable background refresh
    4. Choose Preserve Cell Formatting
    5. Choose Adjust column width
    6. Choose Overwrite existing cells with new data, clear unused cells
    7. Choose Fill down formulas in columns adjacent to data
    8. Click OK



  19. Click OK again
  20. The data is pasted into the empty worksheet

...

  1. Choose Data menu
  2. Click Refresh All
  3. This will pull the latest data from CommCareHQ. You will be prompted for your password.

If Excel hangs during the refresh, disable Enable Background Refresh on all data connections. This is a known problem for spreadsheets with multiple web-based data connections. Find it under Data -> Connections -> Select Connection, click Properties -> unselect Enable Background Refresh.

Step 11: Send to Others

If you just send the Excel file to others they will be prompted to enter login credentials in order to refresh the data. If you don't want this to happen, you can do the following:

...