cancel
Showing results for 
Search instead for 
Did you mean: 

Auto-Refresh Multiple Workbooks

Former Member
0 Kudos

Hey all!

We have a Sales Reporting team that has been using BPC to create reports for Sales Managers. For the most part, everything works great. However, during the first week of the month, our Sales Reporting team needs to open up each individual Sales Manager's report, click "Refresh Workbook", save the report and send it to the sales managers. We have over 400 Sales Managers, so as you might imagine, this is very time consuming.

I've been asked if there is anyway that we can automate this process. I've been butting my head against a wall for a couple of weeks now, but every idea I come up with seems lead to a dead end. Has anyone else tried to do anything like this? Any ideas would be greatly appreciated.

Thanks!

Sean

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Don't look at distributing books. This is mainly for distributing published books which are one or more PDF reports with a TOC, (as you already discovered).

It seems the Distribution and collection feature of the product is hardly known or used.

I have always found this to be a fantastic feature, (though it can be a little quirky until you get use to it).

I have pasted the basic info from the documentation below; this should give you at least the basics for starting, (the online help has all the information).

Please let me know if you have any further questions and let us know how it works out for you.

Fletch

About distributing and collecting offline data

OutlookSoft's offline distribution and collection feature allows administrators to distribute reports and input schedules to multiple recipients, based on a predefined distribution list. The reports are saved to be used offline from the server in native Excel.

The mechanism to distribute offline reports is similar to publishing a book of reports, but this feature also allows you to distribute and collect input schedules. Other differences between publishing a book and distributing and collecting reports and input schedules are:

u2022 Reports or input schedules are locked (parked)

u2022 You can collect changes to offline input schedules and send the data to a database

u2022 Reports and input schedules are distributed through email or a designated network folder, not with a TOC on OutlookSoft Web

Distributing offline reports involves at least three steps, with an optional fourth step to collect modified input schedules:

u2022 Step 1: Create a report or input schedule template that defines the layout of the report. See

Creating reports or Creating input schedules, respectively.

For input schedules, you should set the input cells to 'unprotected' and protect the workbook. See Setting workbook options: Setting the OutlookSoft workbook password.

Also, the workbook should not contain any data. This is because the input schedule expands upon distribution, and clears all input cells.

u2022 Step 2: Create a distribution list that defines the scope (current view) of the data for one or more users. See Creating distribution lists.

u2022 Step 3: Use the Distribution Wizard to distribute offline reports or input schedules through email or a network folder. Reports are created for each user and for each current view described by the distribution report. See Distributing reports and input schedules.

u2022 Step 4: (Optional) If you distributed input schedules, the users can input data offline, and then email the reports back to you or save them in a designated directory. You can then use the Distribution Wizard to collect the data from the modified reports and send it to the database.

See Collecting input schedules.

Former Member
0 Kudos

Sorry, I've been away for a bit.

David, Thank you so much for this information the Distribution and Collection of offline data is exactly what we're looking for.

Have you used this feature with Office 2007? When try to distribute an offline report, I get the following message for each .xls that is created by BPC:

"The following features cannot be saved in macro-free workbooks:

VB Project

To save these features, click no. To continue saving as a macro-free work book, click yes."

This seems to be a problem because we're using Excel 2007, which has different file extensions when files are using macros. Clicking "Yes" repeatedly will allow the files to be created, but when you have thousands of iterations, it's time consuming.

I'm wondering if anyone else has seen this issue.

Thanks again!

Sean

Answers (3)

Answers (3)

Former Member
0 Kudos

If all 400 files are already created and stored in a network folder, I'd write a macro that looked to that folder and created a unique list of all the files...I'd then write a loop statement to go through all the files and refresh them one by one using the MNU refresh command and then save and email each one to an email address that was put in each file (or use a named range)..then moves on to the next file...until you reach the end of the list...The only problem I've run into is the fact that Outlook doesn't like it when you use VBA to send emails and pops up a box requiring you to choose yes to send with a 5 second delay...I've used a workaround to this which is installing a program called Redemption...it circumvents Outlook security using custom vba commands for creating and sending emails...Most IT departments don't like it...my only advice is to test the heck out of what you create to make sure you don't send the wrong file to the wrong rep.

Former Member
0 Kudos

Ok. I've read through everything I can find and am about to give up hope that I'll ever be able to do this. The last possible solution I can think of to Automatically Refresh these workbooks while still keeping them in Excel Format is to use the MNU_eTOOLS_REFRESH Menu Command somehow. Unfortunately, it seems that this command can only be called after the user has opened up the Excel Book and logged into BPC.

Has anyone found a way to call on these Menu Commands without manually opening Excel and Logging in? Is there a way to simulate logging in so that I can call this command?

Any help is greatly appreciated!

Thanks!

Sean

Former Member
0 Kudos

Never tried this myself, so keep in mind I might be way off base, but can you at least create a control panel-ish report to help automate this process?

If you do an expansion of your different salespeople, can you write a formula to set the current view (1) to a particular salesperson, open the report from the library (2), and save an offline copy of it (3)?

For example:

1: EvMNU("SETCV",,"Salesperson=Fred;Time=2009.Mar;")

2: EvMNU("OPENFILE","Run Fred's Report","SalesReport.xlt")

3: create a generic VBA function for Excel.Workbook("SalesReport1").SaveAs & .Close

You can string together 1 & 2 into a single formula, though the third one might require an additional button.

Never tried to control Excel (though COM) while loading and triggering BPC...probably possible, but I don't know how.

- Chris

Former Member
0 Kudos

Have you looked into book publishing? Not sure how you are distinguishing between salesmen (is salesman a dimension?), but would think this is what you're after.

Look in the help for BPC for Excel under Publishing and Distributing Data.

- Chris

Former Member
0 Kudos

Chris -

Thanks so much for your answer. The Book Publishing is definitely a great tool. Unfortuantely, the Book Publishing can only send to a PDF or a Web Form. Our users still want the data to be editable within MS Excel.

Any other ideas on how to do this and keep the data in Excel?

Thanks much!

Sean