Error in Executing VB Script

[syed]

Hi,

I am using below code in MSExcel VBO object to unhideAll rows and columns across all sheets in excel
For Each ws In GetWorkbook(handle,Nothing).Worksheets
ws.Activate
ws.Columns.EntireColumn.Hidden = False
ws.Rows.EntireRow.Hidden = False
Next ws.

the code is working fine when this particular page of code is placed in MSExcel VBO object. When I moved this to separate object and passed the handle to that object but it is throwing key not present in dictionary.

Where I am making mistake?

 

[mdhazlee]

I encountered this problem before. When running your process or object that calls the edited MSExcel VBO, you'll need to check and ensure that all the actions that involves the MSExcel comes from the same object. So you cannot use a combination of the original object (for e.g. to create instance and open file) and then use your edited one to do the next step. Pls check and change them to use your edited MSExcel VBO, I think it should fix the problem.

 

[syed]

Thanks MdHazlee,

Yeah that is one way to fix the issue. But i don't want to move all the funcitonality in MSExcelVBO to edited object. I changed the code as below

For Each ws In GetObject(, "Excel.Application").Workbooks(workBookName).Worksheets
ws.Activate
ws.Columns.EntireColumn.Hidden = False
ws.Rows.EntireRow.Hidden = False
Next ws

1. Create and open excel using MSExcel VBO
2. And pass the workbookName to edited object which has the above code. So, its working fine.

 

[mdhazlee]

@syed, thank you for sharing your solution. I didn't know that just using the workBookName to refer to an instance of excel would work! That sure saves a lot of time instead of creating multiple versions of the same VBO. Just have to be careful of the uncommon scenario where multiple versions of WB with same name (residing within different folders) are simultaneously opened, your solution seems the logical way forward.