VBA and Smart View add-ins

LucLuc

Hello colleagues,

This is my first post in Oracle community, that's why I would like to say HELLO to all of you.

I need your expertise.

I creating VBS macro in outlook to automate HFM refresh process (take excel attachment refresh and reply). I stuck in the part responsible for load ad-ins in Excel part. Please see below:

Set xlApp = CreateObject("excel.application")

  xlApp.Visible = True

  xlApp.COMAddIns("Hyperion.CommonAddin").Connect = True

  xlApp.Workbooks.Open ("C:\Oracle\SmartView\bin\HsTbar.xla")

  Set xlApp = xlApp.Workbooks.Open(link_to_file, UpdateLinks:=False)

Above code create Excel application, run Smartview add-ins and open the specific file. Unfortunately Smart View add-in not load successfully.

Would you mind help me/ give some advise?

Kind Regards,

Luc

Srini Chavali-Oracle

I suggest you post in a VBA community

Dan Gagnon

its been an issue for a long time, if excel is not already opened and you call a Smart View sheet from an application, Smart View does not load properly.

generally i tell users just to launch excel first (any blank sheet) so that it loads Smart View properly, then the sheet with the Smart View usage.

LucLuc

Hello Dan Gagnon,

Not good news from your side.

I tried to run empty excel (used below vb script code), but I have no idea how to co-opereate with this "new" excel file. How to open requested file (I prefer opening the file without links update). Is this possible?

Sub run_excel()CreateObject("WScript.Shell").run "excel.exe /e"Set excelwb = Nothing   Set excel = GetObject(, "Excel.Application")End Sub

regards,

Luc

Celvin Kattookaran

This is a VBScript that I use (it works on the current PBCS version, has its own issues though)

'#################################################################################''  Script      : ReportExtract.vbs'  Author      : Celvin Kattookaran  - Original Version'  Description : To Extract Webforms from PBCS and email them as an Zip file'  Date        : 4th August 2016'  ------------------------------------------------------------------------------'  ------------------------------------------------------------------------------'  Updates    :'  Author Date  Description''' "1st argument is the location of the base folder"' "2nd argument is the location of the log folder"'''  ------------------------------------------------------------------------------'  celvin.v.kattookaran 4th August 2016 Creation' '' #################################################################################' ----------------------------------------------------------------------------------------------------------------------' -- Variable declarations --' ----------------------------------------------------------------------------------------------------------------------'  Option Explicit  Dim arg  Dim aArg(2)  Dim i  i=0For each arg in Wscript.Arguments    aArg(i) = arg    i = i + 1 NextSet objEXCEL = CreateObject("Excel.Application")' Opens the Reporting Macro WorkbookobjEXCEL.Workbooks.Open aArg(0) & "\Macros\" & Cstr(aArg(2))objEXCEL.visible = true' Run the macro in the workbook' Pass the log file location to the workbookobjEXCEL.Application.Run "Main.Main", Cstr(aArg(0)), Cstr(aArg(1))' Save the workbookobjEXCEL.ActiveWorkbook.SaveobjEXCEL.ActiveWindow.Close' Quit EXCELobjEXCEL.QuitSet objEXCEL = Nothing

You can then call this in a batch file

call %BASE_DIR%\Exes\ReportExtract.vbs %base_dir% %ZBB_LOG_DIR% CreateReports.xlsm

Regards

CK

LucLuc

Hello Celvin,

Thanks for the advise.

Would you help with below?

First of all I can't use your code, (due to issue with SmartView tab (it's opening like my first screenshot))

1. Set objEXCEL = CreateObject("Excel.Application") 
2. objEXCEL.Workbooks.Open aArg(0) & "\Macros\" & Cstr(aArg(2)) 

Is there an option for opening the excel file without links update using batch file? and handling from vbs code?

Have a nice day,

Luc

Celvin Kattookaran

You might have to look at the reg settings, I think somewhere there is a setting to load the com addins at startup.

Regards

CK