Automate Updating of File | CLEARIFY - Page 1

I realize this is an Excel or Windows question, but hoping someone can help. . .

I created sales reports in Excel and saved them to Microsoft OneDrive for our sales reps to open and view.  Currently we need to manually open, refresh, and save the files in order for our reps to have access to current data.  How can I automate this process so the reports are updated once a day?

Hi Heath, To eliminate the obvious, you know how to setup the Config Tool to automatically refresh your "cube" each night, right?  That would give you fresh data and you could then just run out the report and push it to your OneDrive to overwritew the old/stale report.  Dave

Yes, QQube data is updated just as I have it scheduled.  This is not the question.

How do I automate the process of pushing this data to the Excel files on OneDrive?  If it takes 3 minutes per report to open, update, and save a file times 20 reports equals an hour a day. . .

Right, just wanted to make sure Heath.  Beyond that you might be able to put Windows Scheduler to the task.  I can do that on the Peachtree side with their Sage Intelligence tool, but not sure how to do it or if it is even possible on the QB side.

We have 16 sales reps and I created nice sales reports using QQube, but was having to manually open, refresh data, and save the reports.  This took someone in our office about 45 minutes each day to do, but if we didn't do so our reps were looking at reports with outdated data.  We wanted to automate the refresh process so the data is current at the end of each day.  With the reports saved to OneDrive our reps were able to open and view the reports online and it doesn't matter what brand of device or what software they use.  After working with three programmers over four months I was able to automate the process. . .

The two most difficult things to figure out were:

1. I had to disable the QQube add in.  The main reason for this is because the QQube pop up windows asking if I wanted to refresh data in the report would cause problems with the Excel Macro.
2. I'm using Excel 2013.  Excel changed the way files are synced with OneDrive (Excel 2010 or earlier wouldn't have this problem) that caused problems with the automated update process.
   1. The fix is to go into Microsoft OneDrive settings and uncheck the box next to 'Use Office to sync files faster and work on files with other people at the same time'

If anyone has any questions or would like to do the same I'll do what I can to help, but am still a novice on QQube & Excel. . .

Now that I can get our reports to refresh themselves I want to figure out how to use Task Scheduler to e-mail reports to owners of our company on a weekly basis!

Heath,

Great stuff.  I have disabled  the add in also when I needed to add a filter or date parameter- so that my query will not get overridden. 

I also add a "thisworkbook.refreshall" macro and workbook updates nicely in 2010 and 2013.  Works great. Even with pivots. 

Could you not have a macro that "opens up workbook", "refresh", "close or Saveas".   

Just some thoughts.   

BTW - if any of your guys are not on Excel 2013, they will not be able to do anything with your pivots.   I have had to be real careful to ask clients what version of XL they are in..  as I have developed in 2013 to find they are using 2010 and couldn't refresh pivots. (Its due to change in data model structure)...   Luckily pivots aren't horrible to recreate. 

Thanks for sharing.

Fran

On 12/1/2014 10:05 AM, Fran Reed said:

Could you not have a macro that "opens up workbook", "refresh", "close or Saveas"

This is exactly what I wanted to do six months ago.  We started with a local programmer who wrote a macro for us that didn't work due to issues with QQube pop up windows and OneDrive sync issues.  Next we requested help from Mr Excel. . . same results.  Finally worked with another person at Mr Excel that was able to help solve these issues.

We figured out by uncheking the 'Auto Refresh' button in QQube Add-In the pop-up window doesn't appear when the Excel file is opened.  Unfortunately, QQube has a mind of its own and will recheck this button SOMETIMES.  It isn't possible to program a macro to OPEN, REFRESH, and CLOSE if sometimes you have a pop-up window as a program starts and sometimes not. . .

The only solution to this QQube issue I was able to come up with is to disable the QQube Add In completely.  This way QQube won't redo something I intentionally unchecked.

Once we figured this out we could get the file to refresh automatically on a local drive, but not on OneDrive which was saved on my local hard drive & synced to my OneDrive account.  The problem has to do with how data is synced from Microsoft Office files to OneDrive account if using Office 2013.  Once we found the hidden checkbox to change how the sync process works this problem was solved.

Maybe there is a much easier way to automate the refresh process and I was doing something wrong from the start?

On 12/1/2014 10:05 AM, Fran Reed said:

if any of your guys are not on Excel 2013, they will not be able to do anything with your pivots.

I will need to do more research on this to confirm.  I know most of our salespeople are not on Excel 2013 and some do not even have Excel.  I gave them a link to their report that allows them to view reports and manipulate data, but not change/edit the file. They are also able to download the files.

I do know our salespeople can view and manipulate all data online.  This is why I chose to save files to Microsoft OneDrive and not another cloud based file sharing platform like Dropbox.

What I don't know is if they can do anything with the pivots once they download a file if they are not using Excel 2013.  I will test this when I get home tonight with a computer running Excel 2007.

QQube introduced refresh options in the Excel add-In to handle this scenario.  Mr. Excel was also one of those who requested this feature from us, and we delivered in QQube Version 7.

Now the spreadsheet can have the synch disabled upon opening.

Similar scenario: my solution was to write a powershell script that opens each Excel file (I have them all set to refresh automatically when opened - although the refresh could be scripted as well), then save the file with a new name (same name, but with the date appended).

At the end of the script the new files are all moved to a share for the end-users. It's really a simple script with redundent blocks for each report, so if you know powershell at all you could adapt it for your own use.

I use Task Scheduler to execute the powershell script.

Hi,

Would you be willing to share your powershell scipt?

Heath,

If you disable the excel add in, then aren't you not getting the most recent / accurate data?

I assume the update needs to be done each time in excel in order for the previously synched data to be updated. Is this not correct?