Solved Internal : Could not execute code stage because exception thrown by code stage: Cannot find column #

ctaramkoundinya

New Member
Trying to read an excel file, facing this issue. Please help.
Facing this error, for Action - GetWorksheetAsCollection.

Always last column is having issue. Pls help
 
Last edited:

VJR

Well-Known Member
Hi ctaramkoundinya,

Do check in the Excel if there is something questionable in the last column.
It may not be visible as there may be invisible characters like a new line, carriage return, line feed, TAB etc. It may also not be in the header row but in some other cell of that column.
Put a breakpoint on Get Ws as Collection and just before this action is executed go to the excel and Delete the last column (or even a few more columns to the right of data) and then see what it returns after running further. When you do this make sure you delete the column from the same Excel instance as that has been opened via Blue Prism. You can use the Show action and delete the column when it opens up.
 

ctaramkoundinya

New Member
Hi ctaramkoundinya,

Do check in the Excel if there is something questionable in the last column.
It may not be visible as there may be invisible characters like a new line, carriage return, line feed, TAB etc. It may also not be in the header row but in some other cell of that column.
Put a breakpoint on Get Ws as Collection and just before this action is executed go to the excel and Delete the last column (or even a few more columns to the right of data) and then see what it returns after running further. When you do this make sure you delete the column from the same Excel instance as that has been opened via Blue Prism. You can use the Show action and delete the column when it opens up.
Sure. Will try. What I did try till now was, removing few rows bring it down to <250~, then its working fine... Is there any limitation, I hope it doesn't. TQ
 

VJR

Well-Known Member
Sure. Will try. What I did try till now was, removing few rows bring it down to <250~, then its working fine... Is there any limitation, I hope it doesn't. TQ
250 rows is really very less for a collection to cause any issue. What might have happened is there could be some questionable data in a column of that row and that might have got deleted when you reduced the number of rows.
 

ctaramkoundinya

New Member
250 rows is really very less for a collection to cause any issue. What might have happened is there could be some questionable data in a column of that row and that might have got deleted when you reduced the number of rows.
Hi VJ,
I was able to find the problematic row.
But unable to find proper solution to remove line breaks, CR, LF,..etc.
Need your help how can we do it..code sample will help. I was trying to create an action with custom logic, but its failing some how. any simple way
 

ctaramkoundinya

New Member
Hi VJ,
I was able to find the problematic row.
But unable to find proper solution to remove line breaks, CR, LF,..etc.
Need your help how can we do it..code sample will help. I was trying to create an action with custom logic, but its failing some how. any simple way
Found the solution, able to correct it, successfully created a coded stage for the same.
Thank you
 

ctaramkoundinya

New Member
Solution:-

For Each oneCell In excel.Selection
oneCell.Value = excel.Substitute(excel.Substitute(CStr(oneCell.Value), vbLf, vbCr), vbCr, "-").Trim()
oneCell.Value = excel.WorksheetFunction.Clean(oneCell)
Next oneCell
 

adam10550

New Member
I am having this issue. It can't find column 11 which shows data as a table. Does anyone know the solution to this?
 

bbackus

New Member
Hello,

Has anyone had this problem since the post with the solution? I tried creating a code stage to select all the cells in my sheet and then perform the solution. The code can be shown below:


Code:
Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim Success
Dim oneCell As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

    ws.UsedRange.Select()

For Each oneCell In excel.Selection
oneCell.Value = excel.Substitute(excel.Substitute(CStr(oneCell.Value), vbLf, vbCr), vbCr, "-").Trim()
oneCell.Value = excel.WorksheetFunction.Clean(oneCell)
Next oneCell

Success = True

Catch e As Exception
    Success = False
    Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

I can't attach the sheet because the information is confidential however I can try to explain it. The sheet comes in and is 39 columns and ~6000 rows. There is text in row one and 3 empty rows below before the sheet begins. With the process I created, the rows above the table headers are selected and deleted. After the last row in the table, there are roughly 6 empty rows then text with many non standard characters in the A column. I also select and delete the content in that row. Once the extra text is deleted, I then try to apply the solution using the code above.

I have tried removing rows and columns to see what the problem is to no avail. Weirdly enough, when I removed all information and left the headers, I was able to pull in the headers and ~430 rows of empty data. Seeing that I also have run the remove blank rows action to try and get rid of those. I am still getting the error:

Internal : Could not execute code stage because exception thrown by code stage: Cannot find column 39.


Thanks in advance!
Bryant
 
Top