Automate Workbook Refresh

[Former Member]

Hi All,

Does anyone have a way to automatically refresh a workbook and save it to a file folder? We have a workbook that is highly formatted and has multiple queries on one worksheet so the reporting agent as I understand it won't help me, but let me know if I am wrong. Any ideas will be appreciated.

Thanks in advance.

[Former Member · Accepted solution]

Hi Jason,

When you write "automatically", do you mean on a scheduled basis without human intervention?

The only method I have ever seen to do this requires:

+ you have a PC running, network connected

+ you use Visual Basic + the Excel "ontime" method

+ you "somehow" need to pass to the Visual Basic code a valid userID & password (this does not have to be in the code, per se, but it has to be somewhere ... in the workbook, in another file, et cetera)

+ If your system puts out system messages at logon, you will need ANOTHER application running that monitors for this system message and dismisses it.

In short, it is relatively complex. I have developed the code for doing all of this. But ... In practice, I have always found it better to give an assistant the task to run something like this (assuming it is large) over their lunch break instead.

- Pete

[Former Member · Accepted solution]

Pete -

Thank you for your reply.

I agree it would be best to have an admin refresh these daily, but the business has pushed back on that.

Sounds like I am heading down the same path using vbscript. Would you mind posting your code or send it to me at jason.miller@gerber.com? It would save me a bunch of time.

[Former Member · Accepted solution]

Please let me know if you have a VB Code to automate the workbook refresh and convert the work to flat file as we need the workbook to refresh every quarter automatic

[Former Member · Accepted solution]

Hi Soniya,

I saw your original posting with this question. I did not respond to that posting because this is really not something that can be done in VB.

With VBA in Excel, you can schedule a routine to run (the command is Application.OnTime, then give the time for the subroutine to run and the name of the subroutine). But, this has 2 problems for your application:

1. this allows only "time", not "date + time"

2. the schedule stays in Excel memory; which means it gets lost if Excel is closed.

I would think that using scheduling within BW is the right way to do this. But, I am not an expert on that subject.

If the workbook is stored on a local or LAN drive vs. on the BW server, then the only other option that comes to mind is Windows scheduling (and set up the workbook so that your macro will kick off automatically when the workbook opens).

Here is a link that gives instructions on how to use Windows scheduler:

http://www.iopus.com/guides/winscheduler.htm

Hope this helps.

- Pete

[Former Member · Accepted solution]

Hi Soniya,

There is one Tool called MOZHI EPP which can do what exactly you are trying to envision. Take a look at http://www.mozhiepp.com or http://www.wftus.com.

We are very happy to automate even System Refreshes, Upgrade, Training and Health Checks.

This is awesome which can even automate various SAP Business Process. Supports BDC and ECATT scripts and a GUI based which is so easy for us to configure.

Regards

Ganesh Radhakrishnan

[Former Member]

Check out this....

Function can be used to refresh queries in an active workbook. To refresh all queries, set the first parameter to True and use the following code:

If Run("sapbex.xla!SAPBEXrefresh", True) = 0 Then

If Run("SAPBEX.XLA!SAPBEXsaveWorkbook") = 0 Then

Else

MsgBox "ERROR WB save"

End If

Else

MsgBox " Error in Refresh"

why this...bcoz..if all queries gonna refresh..then whole workbook gonna refresh...

[Former Member]

Hi

I don't think you can run it in RA unless you break it down to several single RA sheets.

In later version's you can use Info Broadcasting but again I am not sure if it is cable of running multiply query's.

Reg's

Edan

[Former Member]

Sorry - Forgot to mention that we are running 3.1c.