Using GetWorkbook in custom Excel Object
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
27-11-19 01:21 PM
Dear All,
I would like to create an object which interfaces with Excel, including a stage which uses the following code:
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
------------------------------
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
------------------------------
3 REPLIES 3
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
27-11-19 01:43 PM
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
------------------------------
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
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
28-11-19 08:30 AM
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/
------------------------------
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
03-12-19 09:55 AM
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:
How can I proceed please?
------------------------------
Jean Luc Farrugia
------------------------------
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
------------------------------
