VBA + Refresh referencing sheet name instead of selecting/activating

[b5noj]

Hi

Is it possible to refresh and EPM report without activating or selecting a sheet?? I've always been told it's not good practice to select or activate a sheet when writing VBA.

Example - Current method

Dim api As Object
api = Application.COMAddIns("FPMXLClient.Connect").Object
Sheet1.activiate
api.Refresh

Example - What I'd like to use

Dim api As Object
api = Application.COMAddIns("FPMXLClient.Connect").Object
Sheet1.api.Refresh

[former_member186338]

P.S. Please accept correct answer to your previous question!

[former_member186338 · Accepted solution]

Sorry, but there is no API that will refresh some particular sheet without activating it.

"I've always been told it's not good practice to select or activate a sheet when writing VBA." - wrong idea!

You can always store current active sheet in some variable, then activate sheet to be refreshed, then activate sheet saved in variable.

Dim wshCurrent as Worksheet
Set wshCurrent = ThisWorkbook.ActiveSheet 'store current active sheet
ThisWorkbook.Worksheets("SheetWithReportName").Activate
api.RefreshActiveSheet
wshCurrent.Activate 'restore active sheet

[b5noj]

ok thanks

"I've always been told it's not good practice to select or activate a sheet when writing VBA." - wrong idea! - Why?

[former_member186338]

Activate sheet is equivalent of user switching to some particular tab. Nothing bad... Just temporary activation.

Unfortunately, SAP is not providing API to refresh not active sheet (like we are able to refresh entire workbook). But a lot of other useful functionality is missing in EPM VBA API!

[former_member186338]

Theoretically there is a crazy way to refresh worksheet without activating it:

You have to change sheet option:

Not EPM Worksheet 4 TRUE or FALSE

for all sheet except required.

Then RefreshActiveWorkBook :))))