excel cell character count problem

[Champak]

Not specific error with autoit. I'm using the latest excel and I'm getting an error 7 on excel_rangeread with a com error of "-2147352567". As far as autoit is concerned it seems I have more than 256 characters in a cell which as far as I can see I don't, or I'm trying to read 65000 cells which I'm not. I tried the force parameter, but that doesn't change anything. Is there a way within excel to scan all cells to see if I have more than 256 characters just in case? Or is there another way to determine what is going on with this file to correct it so autoit will play nice?

Edited June 23, 2022 by Champak

[SkysLastChance]

So if I am understanding correctly you are getting the 7 error code weather you have the $bForceFunc set to true/false?

You also said you are not trying to read 65000 cells. However, the only way you would get that error both way is if you were trying to transpose more then the limit of the excel version. (Someone correct me if I am wrong here.) 

https://www.autoitscript.com/autoit3/docs/libfunctions/_Excel_RangeRead.htm

"if you transpose > 65535 cell the $bForceFunc is forced to True." 

Without seeing your code. My guess is something is off with your $vRange. 

[Champak]

Nothing special about the code, it works with every other file except this one in particular. That's why I'm trying to figure out if I can find where in the file is causing this. The particular file I'm working with is 1800 rows and A:AM, so it is more than 65000, didn't realize it passed that. But I'm working with another file with the exact same file size and it's not throwing an error, so the cell count is transposing with no issue with the other one.

$oExcel = _Excel_Open(Default, Default, Default, Default, True)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $FilePass)
    If @error Then MsgBox(0,1,1)
    $aLVArray_1 = _Excel_RangeRead($oWorkbook)
    If @error Then MsgBox(0,0,@error & @CRLF & @extended)

Also, I just put True in there when this just started happening today. What lead to this error starting to happen...or the only thing different that took place before this happened...I was changing the size of columns and hiding columns and edited a couple cells (less than 30 characters). It doesn't make sense that this would cause that, but that is what preceded this issue. Prior to that this file was opening without any errors. I put the cell visibility and size back to normal since.

Edited June 23, 2022 by Champak

[SkysLastChance]

Just for the lol's what if you try this.

$aLVArray_1 = _Excel_RangeRead($oWorkbook, Default,"A1:AM1800",Default,True)

I have had problems with "Used Range" in the past. 

Edited June 23, 2022 by SkysLastChance

[Champak]

No that didn't do anything either. Even though I went ahead and made another file that is working fine, I just want to know what is going on now. I'll probably make a quick script when I have time to scroll through all the sells and check the string length just to see if something is reporting too many characters...but I highly doubt that is the case based how this all of a sudden started happening with this file that was working with no issue before.

[water]

The wiki provides a script to check the data for specific problems and even fixes them: https://www.autoitscript.com/wiki/Excel_UDF#Excel_RangeWrite_writes_no_data 

[Champak]

My rangwrite issue is throwing an error, that example says if it's not getting an error or writing nothing. Besides, I'm not understanding how to use that in my situation. That function looks like it's reading an array of what was in an excel file, but my issue is I can't even get the excel file into an array to begin with. So how do I use that function? I'm assuming the array with the bad data in the function should be the array I read off of my excel file.

Thanks.

[water]

Can you post the Excel file so we can play with it?
And please post your script.

[Champak]

I think I found it, as of now at least. There was a hidden column in one of the columns above "Z" that I didn't notice and almost on every line there were a crazy amount of hashtags. I have no idea what caused that because nothing in my code that writes to that column. The only thing I can think of is the other program that I'm interacting with somehow corrupted that column. I'll give it a week and see if anything pops up.

Thanks.