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
we have to connect the SmartView plugin in the excel by using credentials.
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.
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
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.
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.
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
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.
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.
Anil_G
Nidhi_Gupta1