topic RE: Excel VBO Error ("Cannot find column X") in Product Forum

Excel VBO Error ("Cannot find column X")

MenardoEspectac — Mon, 08 May 2017 07:47:00 GMT

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...

Is there anything odd about

John__Carter — Mon, 08 May 2017 14:01:00 GMT

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.

Hi John,

MenardoEspectac — Mon, 08 May 2017 14:56:00 GMT

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...

RE: Excel VBO Error ("Cannot find column X")

HaegeunKim — Sat, 21 Sep 2019 11:40:00 GMT

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
------------------------------

RE: Excel VBO Error ("Cannot find column X")

ehedgepeth1 — Tue, 25 Feb 2020 12:10:00 GMT

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
------------------------------

RE: Hi John,

vivek.kumar.goel — Fri, 28 Feb 2020 07:37:00 GMT

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.
------------------------------

RE: Excel VBO Error ("Cannot find column X")

DavidRubiano — Tue, 30 Jun 2020 10:55:00 GMT

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
------------------------------

RE: Excel VBO Error ("Cannot find column X")

NICHOLASLINCON — Sun, 10 Dec 2023 04:03:00 GMT

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
------------------------------

RE: Excel VBO Error ("Cannot find column X")

michaeloneil — Mon, 11 Dec 2023 09:14:00 GMT

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
------------------------------

Re: Excel VBO Error ("Cannot find column X")

sedmiston — Thu, 05 Dec 2024 16:34:38 GMT

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.

Re: Excel VBO Error ("Cannot find column X")

BernardoCris012 — Fri, 06 Dec 2024 08:36:07 GMT

Maybe you can try getting the total number of rows first, then use that as the last row for your range. For example you have data item output as "Number of Rows" instead of A1:AL190 as range use "A1:AL" & [Number of Rows]. Sometimes there are rows that has hidden value.