topic RE: Read Hidden Columns from Excel in Product Forum

Read Hidden Columns from Excel

_Amlansahoo — Thu, 12 Dec 2019 12:14:00 GMT

Hi Team,

How can I read values from hidden cells in a excel.
If now I am trying to read values from excel it's not reading values for hidden fields.

Please help me on this if possible.

------------------------------
Amlan Sahoo
RPA Consultant
Equinix
------------------------------

RE: Read Hidden Columns from Excel

NicholasZejdlik — Tue, 31 Dec 2019 22:03:00 GMT

I ran into this same issue, so I added a page to the MS Excel VBO object to unhide a range. The code stage looks like this:

Dim Excel = GetInstance(Handle)
Excel.ActiveSheet.Range(Range).EntireColumn.Hidden = False

Handle is the numeric reference to the Excel application, and Range is a string consisting of an Excel range ("A1" or "B:G", for example).

Any hidden columns in the range will be unhidden, and you'll be able to read values from it as normal.

------------------------------
Nicholas Zejdlik
RPA Developer
Fujitsu America, Inc.
America/Chicago
------------------------------

RE: Read Hidden Columns from Excel

_Amlansahoo — Thu, 02 Jan 2020 07:27:00 GMT

Thanks Nicholas Ii will definitely help me.

------------------------------
Amlan Sahoo
RPA Consultant
Equinix
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Mon, 10 Jan 2022 10:14:00 GMT

Dim Excel = GetInstance(Handle)
Excel.ActiveSheet.Range(Range).EntireColumn.Hidden = False

the given code is working fine with unhiding columns in a excel

Also please let me know how to unhide a rows in excel to read a hidden rows values

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------

RE: Read Hidden Columns from Excel

devneetmohanty07 — Mon, 10 Jan 2022 10:29:00 GMT

Hi Tamilarasi,

For unhiding the rows in a given range, you can use the following code:

Dim Excel = GetInstance(Handle)
Excel.ActiveSheet.Range(Range).EntireRow.Hidden = False

Let us know if this helps.

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Mon, 10 Jan 2022 10:49:00 GMT

Hi Devneet,

Dim Excel = GetInstance(Handle)
Excel.ActiveSheet.Range(Range).EntireRow.Hidden = False

The given code is not working fine and I tried with range (A1:N50, 1:500)
Please let me know If we can achieve the same in different way .

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------

RE: Read Hidden Columns from Excel

devneetmohanty07 — Mon, 10 Jan 2022 11:13:00 GMT

The range you are providing seems bit weird to me, if you want to provide multiple ranges, you can provide the Text value in the Range parameter something as "A1:N50,A60:N100"

This code seems to be working as:

Dim Excel = GetInstance(Handle)
Excel.ActiveSheet.Range("A1:N50,A60:N100").EntireRow.Hidden = False

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Mon, 10 Jan 2022 11:31:00 GMT

What I meant is I tried the range with two ways A1:N50 and 1:500

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------

RE: Read Hidden Columns from Excel

devneetmohanty07 — Mon, 10 Jan 2022 17:14:00 GMT

Hi Tamilarasi,

In that case the first range should work where you are providing the "A1:N50" range as this is working on my machine. In case you are getting any error, please share the error details so that we can know more about it.

FYI, The same code is also being used in the 'Unhide Row(s)' action of the 'MS Excel - Extended' VBO which can be found in the following DX Exchange link: MS Excel - Extended

Please find the below snapshot for more details, Here in the Range argument provide A1:N50 as per your requirement:

Input Arguments:

Code:

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Tue, 11 Jan 2022 10:57:00 GMT

Hi Devneet,

I have used the Unhide Row(s)' action of the 'MS Excel - Extended' VBO and while I am running getting the below error. Please find the screenshot for your reference.

please let me if anything need to be added more......Thanks for your help.

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------

RE: Read Hidden Columns from Excel

devneetmohanty07 — Tue, 11 Jan 2022 11:01:00 GMT

Hi,

The Key not present in dictionary error usually occurs if the handle which is being provided is incorrect as handle refers to the current instance of the workbook being stored in the dictionary variable. Can you cross check if any other actions before it which uses the handle on the same workbook is working fine ?

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Tue, 11 Jan 2022 11:19:00 GMT

Hi Devneet,

I am able to perform the action (open workbook, activate workbook and show) with the same handle and only in action Unhide rows I am getting the issue...

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------

RE: Read Hidden Columns from Excel

devneetmohanty07 — Tue, 11 Jan 2022 11:35:00 GMT

Here are you using Excel VBO for the rest and Excel Enhanced VBO for this Unhide Row action? If yes, then you are likely going to face this error, the reason being is both are separate business objects and the dictionary item of one is not shared with another. I would recommend in such a case replacing the similar actions from Excel Enhanced VBO only.

Can you confirm on the same?

------------------------------
----------------------------------
Hope it helps you and if it resolves you query please mark it as the best answer so that others having the same problem can track the answer easily

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant
Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Read Hidden Columns from Excel

TamilarasiS — Tue, 11 Jan 2022 14:04:00 GMT

Hi Devneet,

Thanks for your reply and After replaced all the actions to Excel Enhanced VBO, the code executed successfully. but the rows in my excel didn't get unhide.
Need to check whether the issue is with the excel.

------------------------------
Tamilarasi S
Associate software engineer
TechMahindra
Asia/Kolkata
------------------------------