This guide will cover how to use the data link you generate from a Customizable Data Export with the different versions of Excel. Data Linking starts with the Customizable Data Export Report - general information on using that report is available in the Customizable Data Export Overview Guide here. For assistance generating a data link, please see the Data Linking Overview guide here.
Notes About XML Linking
Importing Your XML Link
Import Instructions for Microsoft Excel 365
Import Instructions for Excel 2013 or 2016
Import Instructions for Excel 2010
Please Be Aware of the Following:
- The Mac-based version of Excel was not designed to support data linking
- Only one link to ClickTime can be established per workbook
- This feature is only available to Corporate or Enterprise accounts
- Due to a current Microsoft issues, you will want to save files in .xls format (instead of .xlsx format) if you need to refresh a worksheet with updated data. If you need to save this file in .xlsx format, please contact ClickTime Support and we can provide you with another method to populate your worksheet that can be saved in .xlsx and refreshed as needed
Some customers have also asked about using XML linking when their local network is behind a firewall. There should not be any issues with using the XML linking process behind a firewall, as ClickTime does not push data to the file directly. Excel pulls the data from ClickTime via port 443 (a standard HTTPS web protocol) when the end-user opens and uses the Excel file - if you are experiencing issues you believe are related to your firewall, please check with your local network Administrator for assistance. This guide has more information about optimal settings for working with ClickTime and may also be a helpful resource.
After making sure you have the appropriate "Legacy" XML Linking options enabled (explained below), please make you have done the following before using the XML link that ClickTime provides:
- Created a Customizable Data Export with a saved name:
- Ran the report to make sure that the criteria used is the most up-to-date
- You can then copy/paste this XML link from the page confirming the report has finished running:
- You can then periodically refresh your worksheet using the "Refresh All" option on the data tab:
- Please note that if you make any changes to your date range and want that to be reflected in your worksheet, you will need to re-run the Customizable Data Export before refreshing your Excel worksheet
- Please be aware that if you add columns to your saved report, re-run the report, and refresh your worksheet, it will not include those columns. You will need to start the import with a new blank worksheet in order for the new columns to be included
- Please be aware that if you remove columns from your saved report, re-run the report, and refresh your worksheet, it will still contain those columns, but there will be no data in the fields
Reminder: By default, data will only be exported for the date range you specified in the report criteria. However, you can also override this date range via the custom URL configuration parameters StartDate and EndDate, as detailed in the Data Linking Overview guide here.
Data Import Instructions for Excel Microsoft 365
The below options for Excel 2013/2016 may work for you, but if you have difficulty, you may also be able to use the generic "From XML" data linking option available in recent version of Excel.
To add this to your Excel options:
- Go to File --> Options --> "Customize Ribbon"
- Change the "Choose commands" drop-down to "All Commands"
- Find the "From XML Data Import (Legacy)" option in the list
- Add a new "Group" to your Ribbon:
- Name your new Group (optional)
- Use the "Add" option to move the "From XML" command to the new Group
- Click "OK
Some customers have also reported that additional steps may need to be taken when using Office 365. Specifically, they may need to go to the File --> Options screen and choose the "Data" section. From there, enable the "From XML Data Import (Legacy)" option:
They should then see an additional Legacy option in the "Get Data" drop-down, which includes the "From XML Data Import (Legacy)" choice.
Data Import Instructions for Excel 2013 and 2016
- Open an Excel workbook.
- Go to a blank worksheet.
- Go to the Data tab and then choose "From Other Sources" and choose "From XML Data Import" (as shown in the screenshot below).
- In the pop-up dialog box that opens you will be asked to select the file that you want to import.
- Instead of searching for a file on your local computer, paste the URL you copied from ClickTime into the "File Name" field.
- Click Open. Excel will then ask where you want to put the data. Make the appropriate choice and click "OK".
- Your data will be successfully imported into your Excel Sheet. Save your Excel spreadsheet.
- To update data in the sheet with fresh new data from your ClickTime account, go to the Data tab and choose to "Refresh All". Please make sure to save your file in .xls format in order to Refresh Data
Data Import Instructions for Excel 2010
- Open an Excel workbook.
- Go to a blank worksheet.
- Go to the Developer tab (as shown in the screenshot below).
- In the popup dialog box that opens you will be asked to select the file that you want to import.
- Instead of searching for a file on your local computer, paste the URL you copied from ClickTime into the "File Name" field.
- Click Open. Excel will then ask where you want to put the data. Make the appropriate choice and click "OK".
- Your data will be successfully imported into your Excel Sheet. Save your Excel spreadsheet.
- To update data in the sheet with fresh new data from your ClickTime account, go to the Developer tools and choose to "Refresh Data".
Comments
0 comments
Article is closed for comments.