BEx query refresh macro

[Former Member]

Gurus,

I am trying to find out if there is any VBA code that will trigger a query refresh (similar to setting the query properties to refresh query when opening workbook). The problem is that we don't have a web portal and I want to allow my customers to run queries stored in Excel workbooks on our web site. One query isn't a problem, but in workbooks with multiple queries on separate tabs I would prefer to have a single macro that will automate the refresh for all queries in the workbook. Any suggestions would be greatly appreciated.

Thanks in advance,

Reid

[Former Member · Accepted solution]

Hi Reid,

Use the following in Excel VBA ...

To refresh ALL queries in the active workbook:

Run "SAPBEX.XLA!SAPBEXrefresh", True

To refresh a single query, either:

(a) be sure that active cell is part of the query (a filter cell, a text element, or part of the query results table), and use:

Run "SAPBEX.XLA!SAPBEXrefresh", False

(b) refer to a cell that is part of the query by using:

Run "SAPBEX.XLA!SAPBEXrefresh", False, Range("??")

where ?? = cell address.

- Pete

[Former Member · Accepted solution]

Thanks Peter,

This is exactly what I needed.

Reid

[Former Member · Accepted solution]

Dear Peter,

Extention to the above mail, I would also like to include Period/Fiscal Year information in the query during the refreshing time. How it would be done in a macro.

Please let me know.

Thanks

Raj