topic RE: Using GetWorkbook in custom Excel Object in Product Forum

Using GetWorkbook in custom Excel Object

Jean_LucFarrugi — Wed, 27 Nov 2019 13:21:00 GMT

Dear All,

I would like to create an object which interfaces with Excel, including a stage which uses the following code:

Dim worksheet as Object
Worksheet = GetWorkbook(handle, Nothing). ActiveSheet
worksheet.UsedRange.AutoFilter (Field:= fieldToBeFiltered, Criteria1:= criteriaToFilter)

However the compiler is returning an error stating that the GetWorkbook function is not declared. It may be inaccessible due to its protection level.

I had initially used this code within the original Excel VBO which did not result in this error, yet I could not locate the function within this Object, or view the Application Model used...

How can I proceed to use this function in my own custom Excel object please?

------------------------------
Jean Luc Farrugia
------------------------------

RE: Using GetWorkbook in custom Excel Object

ManojDhamrala — Wed, 27 Nov 2019 13:43:00 GMT

Hi Jean,

You are encountering this error because, the function GetWorkbook() is defined in "Global Code" section of the original MS Excel VBO.

If you want to create your own customized Excel VBO then;
1. Copy & paste the Global Code from original MS Excel VBO to Global Code section in your object or
2. Make a copy (Save As) of original MS Excel VBO and amend the changes as your requirement

Hope this will resolve your issue.

------------------------------
Manoj Dhamrala
------------------------------

RE: Using GetWorkbook in custom Excel Object

vivek.kumar.goel — Thu, 28 Nov 2019 08:30:00 GMT

Hi,

Assuming, you tried to build the custom object from scratch. You might have forgot to add the function definition in your global code/ custom code for GetWorkBook.

Here is the code for your reference.

Protected Function GetWorkbook(Handle As Integer, Name as String) As Object

	Dim wb as Object = Nothing
	If String.IsNullOrEmpty(Name) Then
		wb = GetInstance(Handle).ActiveWorkbook
		If wb Is Nothing ' We need to create a deafult workbook
			wb = NewWorkbook(Handle)
		End If
		Return wb
	Else
		Return GetInstance(Handle).Workbooks(Name)
	End If

End Function

Use it inside your code and then it should work.

------------------------------
Vivek Goel
RPA Architect
Asia/Singapore
+6594554364
https://www.rpatools.com/
------------------------------

RE: Using GetWorkbook in custom Excel Object

Jean_LucFarrugi — Tue, 03 Dec 2019 09:55:00 GMT

Thank you for your replies! I have managed to create a custom Excel object which includes the filtering feature. However It works well when only a single criteria is used, I am attempting to filter only records that have "Failed" written, or that have blank values in the field of interest using the following code:

Dim worksheet as Object
Worksheet = GetWorkbook(handle, Nothing). ActiveSheet
worksheet.UsedRange.AutoFilter (Field:= fieldToBeFiltered,  Criteria1:=criteriaToFilter, Operator:=2, Criteria2:="=")

However when running the process showing Excel output, only the "Blank" cells are being selected to be filtered:

How can I proceed please?

------------------------------
Jean Luc Farrugia
------------------------------