Excel Loop Issue

[mrflibblehat]

Hi All,

I seem to be having an issue where not all data is being displayed in my new excel book.

The code below works for the most part but only seems to work up to cell 259 and then I get an error from the UDF.

Any Help would be much appreciated.

UDF Error - when running my code

C:\AutoIt3\Include\Excel.au3 (451) : ==> The requested action with this object has failed.:
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue^ ERROR

My Code

#include <Excel.au3>
#include <Array.au3>

;~ Open Excel Sheet
Local $var = FileOpenDialog("Open", @WindowsDir & "\", "Excel (*.xls;*.csv;*.xlsx)", 1 + 4)
Local $oExcel = _ExcelBookOpen($var)

;~ Read Sheet to Array
$vSheetArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 7)

;~ Create a seperate new excel book
$oExcel2 = _ExcelBookNew()

;~ Loop Through array, clean up and print to new book
For $i = 1 to $vSheetArray[0][0]

$vNum = StringSplit($vSheetArray[$i][7], " ")
$vDate = StringLeft($vSheetArray[$i][1], 10)

_ExcelWriteCell($oExcel2, $vDate, $i, 1)
_ExcelWriteCell($oExcel2, $vNum[1], $i, 2)

Next

;~ Close book
_ExcelBookClose($oExcel, 1, 0)

Edited May 8, 2013 by mrflibblehat

[guinness]

Have you looked at water's new Excel UDF?

[mrflibblehat]

Have you looked at water's new Excel UDF?

I didnt realise there was one, Il look into that right away,

Thanks for your reply.

[guinness]

No problem. If my memory serves me correct water has add a couple of new functions, one of which you're trying to create now and they've fixed a ton of bugs found in the current version of the Excel UDF.

Edited May 8, 2013 by guinness

[water]

mrflibblehat,

be warned that the UDF is in a very early alpha stage! Function names, parameters or functionality might change in the future.

I'm glad for every user testing the UDF and giving feedback to further enhance it.

[mrflibblehat]

Hi Water,

Your UDF does seem alot more stable and much quicker at reading information than the supplied UDF. However I am having some issues.

1) Is there a way to read all cells with information in only and miss out the blank cells? I cant seem to get _Excel_RangeRead() to do this similar to _ExcelReadSheetToArray()

2) The example of _Excel_OpenBook seems to crash out (Please confirm if this is just me) I get the below error

C:\Users\user\Desktop\Excel Rewrite Alpha 3\Excel Rewrite.au3 (368) : ==> The requested action with this object has failed.:
If $iSheets <> Default Then .SheetsInNewWorkbook = $iSheetsBackup
If $iSheets <> Default Then .SheetsInNewWorkbook = $iSheetsBackup^ ERROR

Thank you for your help.

Edited May 8, 2013 by mrflibblehat

[water]

Hi mrflibblehat,

glad you like the UDF!

• You mean blank cells at the end of the range or blank cells between cells with data (removing blank lines or columns)?
• You mean _Excel_BookNew? Works fine here woth Excel 2010 on Windows 7

[mrflibblehat]

Hi mrflibblehat,

glad you like the UDF!

• You mean blank cells at the end of the range or blank cells between cells with data (removing blank lines or columns)?

• You mean _Excel_BookNew? Works fine here woth Excel 2010 on Windows 7

Thank You for developing the UDF!

1) Yes blank cells at the end of a range

2) Yes, I am using Excel 2010 on Windows 7 also, Must be me Il have a look again.

[guinness]

2) The example of _Excel_OpenBook seems to crash out (Please confirm if this is just me) I get the below error

No crash for me either on Windows 7 x64 - Office 2010 (AutoIt v3.3.9.4.)

[water]

2) Yes, I am using Excel 2010 on Windows 7 also, Must be me Il have a look again.

Do you use the 32 or the 64 bit version of Excel?

[mrflibblehat]

Do you use the 32 or the 64 bit version of Excel?

Must have been a one off, it seems to be working good now

[water]

1) Yes blank cells at the end of a range

My UDF doesn't handle this at the moment. I will think about how to implement this.

[water]

How many blank rows do you have in the array?

You could loop though the array from the last row beginning back to the first row and check until you find a row with non empty cells. Thats then the number of rows you want to process.

[mrflibblehat]

How many blank rows do you have in the array?

You could loop though the array from the last row beginning back to the first row and check until you find a row with non empty cells. Thats then the number of rows you want to process.

The blank rows will vary depending on the Excel Spreadsheet used. I will have a play with the array today,

Thank You for the logic

[water]

I searched MSDN for the SpecialCells method but it doesn't help here.