Exporting Timesheets from CAV
Connect A Voice allows us to export timesheet data out into an Excel format. Here's how it's done:
- Log into CAV and select the county you're working with.
- Click on Reports from the top menu bar.
- Set the drop-downs as follows. Change the Provider to the county and the dates to the pay period you're working with. Then click on Generate.
- A box will pop up asking you if you want to save the file. Click on Save.
- A new box will pop up stating the download is complete. Click on Open.
- If you are prompted with a security warning, click Allow.
- At this point, the spreadsheet opens and contains all of the data that was requested.
Formatting the Excel Spreadsheet
Unfortunately, the visual layout of this export is hard to read and requires quite a bit of manual manipulation. To help make this job easier, we have created an Excel spreadsheet that uses a macro to clean things up.
You can think of an Excel macro as a small computer program. It has been coded to perform all of the repetitive formatting tasks that you would have needed to do manually. What used to take 5+ minutes now takes a matter of seconds. Here's how it's done:
- Single-click on any cell within the Excel spreadsheet and press CTRL+A on your keyboard. This will select the entire contents of the spreadsheet.
- Now press CTRL+C on your keyboard to copy everything.
- Open the CAV Timesheet Report Macro spreadsheet from the P:\ drive.
- Click the Enable Content button in the toolbar.
- If prompted, check "Do Not Ask me Again" and click Yes on the Security Warning.
- Single-click on cell A1 and press CTRL+V on your keyboard.
- On the View tab inside of Excel, click on Macros.
- Highlight CAV_TimesheetReport and click the Run button. Depending on the amount of data in the file, this should take a few seconds to complete. You'll know that it's finished once the screen stops jumping around.
- Press CTRL+Down Arrow on your keyboard to get to the bottom row of data.
- Find the first blank row and click on its row number to highlight the entire row.
- Press CTRL+SHFIT+Down Arrow on your keyboard to highlight all blank rows.
- Right-click one of the blank row's row numbers and select Delete.
- The report is now ready to further sort, print, or email.
- If you would like to save this report, you must go to File > Save As and save it somewhere other than the P:\ drive.