Excel VBO Error ("Cannot find column X")

[MenardoEspectac]

I just want to inquire about an issue we are encountering with regards to a process that uses Excel VBO on a Windows 10 environment with Office 365. The step is that we are trying to copy a range of cells from an excel file and we are using an action stage to pass parameters to action Get Worksheet Range as Collection. The premise is, this stage works on Windows 7 with Office 2013, but an issue arises when we run on the environment I have indicated above. For example, if we are trying to copy a range of A1:AL960, everything works fine. Beyond that (A1:AL961) we get the error "Internal : Could not execute code stage because exception thrown by code stage : Cannot find column 38.". There are different levels that this error occurs and I have findings that this is not connected to any maximum numbers of cells that is allowable to be copied. For example, A1:AL960 is 3640 cells. While A1:V3524 is 81052 cells and both ranges works. Although, one additional row would give us the error. The VBO object we are using is GetWorksheetRangeAsCollection. Any ideas? or has anyone experienced this in the past? Thanks a lot...

[John__Carter]

Is there anything odd about the column headers? GetWorksheetRangeAsCollection wants to create a .Net datatable before passing the output as a collection. So if, for example, there is a header with invalid characters for a column name in the datatable, you might see errors like this.

[MenardoEspectac]

Hi John, Nothing is unusual in terms of the headers in terms of the format and the naming. All columns are being captured successfully, from A1 until AL960. When pushed to AL961 though, we are getting the error. Thanks in advance for ideas, experiences...

[HaegeunKim]

Any update? 
I have a same issue. When using business object "get workseet as collection", the error "Cannot find column X" occurred.

------------------------------
Haegeun Kim
Manager
F&F
------------------------------

[EbonyHedgepeth]

Hello,

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

I am having the same issue. There is nothing in the headers that is an invalid character.
​
7 columns but there is over 40000 rows. How exception be rectified.
I have tried get worksheet range as collection and get worksheet as collection.

------------------------------
Ebony Hedgepeth
------------------------------

[VivekGoel]

If you know the column limits(range), then try MS Excel VBO::Get Worksheet as Collection Offset. it works for me.

------------------------------
Vivek Goel
"If you like this post, please press the "Recommend" Button.
------------------------------

[DavidRubiano]

Hi,

I have seen this kind of problem when the column header includes new lines:

For example: 
"Column Header 1"

"Column
Header 2"

The second option will fail when trying to use Get Worksheet as Collection Offset. Best solution is to rename the column header

------------------------------
David Rubiano
Senior Consultant
Capgemini
Europe/Stockholm
------------------------------

[NICHOLASLINCON]

in my case this was a hidden character I had to send a replace text in range with the hidden character in find what and replace it with empty , this character :
"    ", It seems strange but the hidden character in the spreadsheet was a large space

  

------------------------------
NICHOLAS LINCON
------------------------------

[michaeloneil]

Hi @Menardo Espectacion

Check there are no hidden rows beyond row 960, i've had this happen sometimes when there are a few hidden rows and trying to get the range as a collection.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

[sedmiston]

We had this same error and found that one column of data had a weird tab space after it. Doing a find and replace before we get the collection fixes our issue.