MS Excel VBO (Open Workbook) - In read only mode
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-18 06:44 PM
There is a great open workbook found in the BO provided by Blue Prism, however, I want to create a new page that gives me the open to Open WorkBook in Only Mode. Usually, the code is simple however the current Open WorkBook uses Invoke which is something I'm not familiar with using. How can a modified the code to open in read-only mode using invoke?
name = ExecWithTimeout(Timeout, "Open Workbook",
Function()
Dim instance = GetInstance(handle)
Dim workbooks As object = GetProperty(instance, "Workbooks")
Dim wb As Object = Invoke(workbooks, "Open", filename)
Invoke(wb, "Activate")
Return wb.Name
End Function)
7 REPLIES 7
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-18 07:00 PM
Hi cvs0115
You can simply alter the code from:
Dim wb as Object = GetInstance(handle).Workbooks.Open(filename)
to
Dim wb as Object = GetInstance(handle).Workbooks.Open(filename, ReadOnly:=True)
Then you open the Excel workbook as read only.
Good luck.
BR,
Mustafa
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-18 08:34 PM
Mustafa,
The code worked great on my look machine however it seems that I'm getting an error on our RR stating Compile error in hidden module: ThisWorkbook.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
07-08-18 08:35 PM
Capture
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-18 02:33 PM
Hi cvs0115
Great.
That compile error has nothing to do with BP, this is an Excel compile error, you can try this solution for Office 2010 on XP/Win 7:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other/compile-error-in-hidden-module-this-workbook/5da89258-34fa-4bcc-96bc-223787da6ada
It probably also works for other versions of the office and windows packages.
Good luck.
BR,
Mustafa
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
08-08-18 08:23 PM
Mustafa,
Thanks! You've been extremely helpful. However, I tried that a while ago and I did not have any luck. I believe it has to do with old dll files. I just took another approach and created a BO around it.
Thanks again,
Chris
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
10-08-18 02:06 PM
Hi Chris
No problem, you're welcome :)
BR,
Mustafa
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
13-08-18 04:28 AM
I use this version with invoke to open read only:
name = ExecWithTimeout(Timeout, ""Open Workbook"",
Function()
Dim instance = GetInstance(handle)
Dim workbooks As object = GetProperty(instance, ""Workbooks"")
Dim wb As Object = Invoke(workbooks, ""Open"", filename, 0, True)
Invoke(wb, ""Activate"")
Return wb.Name
End Function)
https://msdn.microsoft.com/en-gb/vba/excel-vba/articles/workbooks-open-…
Second parameter is UpdateLinks, you'd want 0 or 2 most likely:
The help file defines the values as:
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references
Third one is ReadOnly boolean
Some might find other parameters useful too...
