Forum Stats

  • 3,851,373 Users
  • 2,263,968 Discussions
  • 7,904,689 Comments

Discussions

Categories

VBA and Smart View add-ins

LucLuc
LucLuc Member Posts: 3
edited Oct 7, 2016 3:30AM in Reports

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. smartView fail.JPG

Would you mind help me/ give some advise?

Kind Regards,

Luc

Tagged:
LucLucDan Gagnon
· Share on TwitterShare on Facebook

Answers

  • Srini Chavali-Oracle
    Srini Chavali-Oracle Member Posts: 29,596 Employee
    edited Sep 30, 2016 9:56AM

    I suggest you post in a VBA community

    · Share on TwitterShare on Facebook
  • Dan Gagnon
    Dan Gagnon Member Posts: 844
    edited Oct 3, 2016 11:09AM

    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
    · Share on TwitterShare on Facebook
  • LucLuc
    LucLuc Member Posts: 3
    edited Oct 4, 2016 10:04AM

    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

    · Share on TwitterShare on Facebook
  • Celvin Kattookaran
    Celvin Kattookaran Member Posts: 3,143 Gold Trophy
    edited Oct 4, 2016 10:27AM

    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

    Dan Gagnon
    · Share on TwitterShare on Facebook
  • LucLuc
    LucLuc Member Posts: 3
    edited Oct 4, 2016 10:40AM

    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

    · Share on TwitterShare on Facebook
  • Celvin Kattookaran
    Celvin Kattookaran Member Posts: 3,143 Gold Trophy
    edited Oct 4, 2016 11:03AM

    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

    · Share on TwitterShare on Facebook
This discussion has been closed.