Jump to content

excel cell character count problem


Recommended Posts

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 by Champak
Link to post
Share on other sites
  • Champak changed the title to excel cell character count problem

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. 

Life's simple. You make choices and you don't look back.

Link to post
Share on other sites
Posted (edited)

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 by Champak
Link to post
Share on other sites

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.

Link to post
Share on other sites

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 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...