Need help on Smartview plugin in excel

Hi Guys,

in one of the process we are using smart view plugin in some of the excel reports.

There are some steps we have to perform like

  1. we have to connect the SmartView plugin in the excel by using credentials.
  2. After connecting successfully we have to refresh the reports by using refresh all option in the SmartView.

We are able to perform the above steps by using UI automation. but would like to know if any alternative approaches are like using VBA or vb.net code to achieve that.

if anybody know details related to this please help us. thanks.

Regards,
kirankumar.

Hi @rahulsharma , @Palaniyappan , @Yoichi , @Lakshay_Verma , @lakshman , @prasath_S , @ppr

Could you please share your thoughts on this. thanks

Regards,
Kirankumar.

@kirankumar.mahanthi1

You mean without using SmartView plugin ?

Dim excel As Microsoft.Office.Interop.Excel.Application 'Init Excel application
Dim wb As Microsoft.Office.Interop.Excel.Workbook ' Init Excel Workbook Dim ws As Microsoft.Office.Interop.Excel.Worksheet 'Init Worksheet
`Dim ob As Microsoft.Office.interop.Excel.ListObject 'Init ListofObject

Try excel = New Microsoft.Office.Interop.Excel.ApplicationClass’create the instance of excel work book’
wb = excel.Workbooks.Open("ExcelFilePath->AbsolutePath")'Open the excel the file' excel.Visible=True
ws=CType(wb.Sheets("SheetName"),Microsoft.Office.Interop.Excel.Worksheet)'select a sheet and activiates' ws.Activate
ob=ws.ListObjects("Table1") 'Fetch your table ob.Resize(ws.Range(“A1”,“D115”)) 'Give Range here
wb.Save excel.Quit
Catch es As Exception System.Windows.MessageBox.Show(es.Message)
End Try

This is precisely for a object type table, you need to do some r&d on it ti refresh your reports

SmartView plugin already there in the excel. Need to know how to automate without using UI automation.

Hi @Lakshay_Verma ,

Thanks for helping me on this. SmartView is plugin from Orcale it will get the real time data from DB. the message which you shared is generic. if we want to automate SmartView activities by using VB.net. Could you please suggest on this.

Regards,
Kirankumar.

Hi @kirankumar.mahanthi1

I don’t remember much but we refresh the smart view using VBA by importing the module (which contains all the smartview functions in it) and call that functions and in your procedure refresh it.in connect function we just need to pass the sheet which has Iris formulas, username and password for smartview.

https://www.oracle.com/middleware/technologies/fm-sv-vba-obe.html

Thanks

1 Like

This can be done in 2 modiles

  1. Login
    Executing Smart View Retrievals using VBA
Example

Declare Function EsbAutoLogin Lib "ESBAPIN" (ByVal hInst As Long, ByVal Server As String, _
                                             ByVal User As String, ByVal Password As String, _
                                             ByVal AppName As String, ByVal DbName As String, _
                                             ByVal Opt As Integer, pAccess As Integer, _
                                             phCtx As Long) As Long
Sub ESB_AutoLogin ()
   Dim sts As Long
   Dim Server As String * ESB_SVRNAMELEN
   Dim User As String * ESB_USERNAMELEN
   Dim Password As String * ESB_PASSWORDLEN
   Dim AppName As String * ESB_APPNAMELEN
   Dim DbName As String * ESB_DBNAMELEN
   Dim pOption As Integer
   Dim pAccess As Integer
   Dim hCtx As Long 
   '**********************
   ' Initialize parameters
   '********************** 
   Server = "Server"
   User = "User"
   Password = "Password"
   AppName = ""
   DbName = ""
   pOption = ESB_AUTO_DEFAULT 
   '************************
   ' Login to Essbase Server
   '************************ 
   sts = EsbAutoLogin (hInst, Server, User, Password, AppName, DbName, pOption, pAccess, hCtx)
End Sub

Ref:- Oracle Enterprise Performance Management Workspace, Fusion Edition User's Guide

2 Refresh Report

Ref: - Executing Smart View Retrievals using VBA

Again, I do not have direct access to these plugins and this requires more r&d

1 Like

Hi @prasath_S ,

I have done some research on the link you mentioned related to oracle site they suggested some VBA functions.

i am able to achieve connecting to smart view with the VBA functions by giving the specific workbook and sheet. but coming to the refresh activity doesn’t have any option to point out specific sheet this is giving me issue because i have 20 excel files and 15 sheets are there in each and very excel file that i have to refresh one by one.

Thanks for helping me in this. Could you please share your thoughts on the refresh.

Regards,
Kirankumar.

1 Like

Hi @Lakshay_Verma ,

Thanks buddy for your valuable help. i will do some R&D on the refresh logic. thanks again.

1 Like

@kirankumar.mahanthi1

For refresh function (think it is HypMenuVRefresh) we just need to activate the sheet and call the function it will refresh the current active sheet, we can use HypMenuVRefreshAll(for all the sheets in a Excel)

But it all depends on how many cells have iris formula in your excel,if a excel has 15 sheets and each sheet contains 50000 rows of excel with iris formula in it it is best to refresh the sheet individually, as excel can crash if the load is very huge.

Thanks

1 Like

Sure thanks for your valuable help @prasath_S . i will ask my team to try this option. thanks again.

Regards,
Kirankumar.

1 Like