Oracle Smart View Connection in Excel

[ashish.kumar21031995]

Hi All ,
Does anyone have the code to setup connection in Oracle Smart View through code in excel.
and also if anyone can help how to refresh in oracle smart view tab , it will be very helpful .

 

[VJR]

Hi ashish.kumar21031995,

I doubt that the Blue Prism code stage would be able to directly interact with the Smart View Add in, but that is something that needs to be tried and researched. For eg; The HypMenuVRefresh() function may not be abled to be called from Blue Prism. But again you can search for the corresponding Smart View Dll, add it to the Business Object and try using a Code stage.

These are just a few links on the forum..
http://rpaforum.net/threads/can-i-spy-excel.1418/post-4351
http://rpaforum.net/threads/smart-view-on-blue-prism.1637/post-5253

If directly through code stage its not possible then other options are running a macro from BP, or spying the Smart View menus from BP and interacting with it.

 

[fergupa]

@VJR

I tried using the VBA command to connect with a HFM database as described in the SmartView Developer guide (here). I am able to get it to work in VBA just fine. I added the following in a code stage but received the following error

Description: Compiler error at line 3: 'System.Empty' is not accessible in this context because it is 'Friend'.

Here is the code stage:

Dim x as long
x = HypConnect(Empty ,"Username", "Password", "Connection_Name")

As you can see by the error it does not like 'Empty' in the first parameter for HypConnect.

I tried adding "Sheet1" in replace of 'Empty', however I receive this error message:

Description: Compiler error at line 3: Too many arguments to 'Public Sub HypConnect()'.

Please advise if you have any suggestions.

I wish BP would have more 'connectors' available. My guess is that there are many companies that are duplicating efforts in building common VBO's, such as SmartView.

Thanks!

 

[VJR]

Hi fergupa,

Have you added any dll? Or have you declared the HypConnect API function? If so how does the declaration function look like.

Probably you haven take it from here.
https://docs.oracle.com/cd/E51367_01/financialsop_gs/FASVD/ch05s02.html

If it is giving the error on the sheet name probably it requires the reference to the sheet from Blue Prism instead of directly the sheet name.

For eg; Below is the code present in the Activate Worksheet action of the Ms Excel VBO and you might need to interact with that sheet using the object in a similar fashion.

Dim ws as Object = GetWorksheet(handle,workbookname,worksheetname,createifmissing)
sheetexists = ws IsNot Nothing
If sheetexists then ws.Activate()

 

[fergupa]

Thanks @VJR

Can you point me to a reference which will help me better understand how to add DLL's to BP? I see that Smartview uses the 'HSAddIn.dll' and I may need to get that into BP. Thanks!

 

[VJR]

Thanks @VJR

Can you point me to a reference which will help me better understand how to add DLL's to BP? I see that Smartview uses the 'HSAddIn.dll' and I may need to get that into BP. Thanks!

I have not used SmartView with BP nor do I have it installed on my machine so I can only suggest you a few trial and errors.
Try adding this dll in the Information stage | Code options tab of the Object just where the other dlls are added (eg; as in Ms Excel VBO) but by browsing to the path. Make sure to register the dll on your system. Also I have seen some instances where an external dll needs to be added to the Blue Prism Automate folder and only then it works, but not on c:\ drive etc. At times you need to close and re-open BP after copying the dll.
Also add the below line to the Global code. Check the Ms Excel vbo for any Declare Function statements.

Declare Function HypConnect Lib "HsAddin" (ByVal vtSheetName As Variant, ByVal vtUserName As Variant, ByVal vtPassword As Variant, ByVal vtFriendlyName As Variant) As Long

You did not get the error on the HypConnect function itself but got on the parameter so looks like you have already done something related to adding of the function.
So prior to trying any of the above, after pointing ws to the desired sheet as in the above GetWorksheet code, pass ws variable to the Hypconnect function instead of the Sheet1.