Update the Excel UDF that comes with AutoIt

[water]

I understand that for ColumnWidth a unit is the width of a character. So 100 is as wide as 100 characters are.

[FlashpointBlack]

Right, I was just wondering why everything seemed to work well for JLogan3o13

[JLogan3o13]

My apologies, ignore my post altogether. I was guilty of just skimming and not fully reading your post. I thought you were reporting that it wasn't resizing the height and width at all, rather than it wasn't returning the correct size.

[water]

For you to test: Function _Excel_RangeLinkAddRemove adds/removes links to/from a range.

_Excel_RangeLinkAddRemove.au3

Edited December 2, 2013 by water

[FlashpointBlack]

For you to test: Function _Excel_RangeLinkAddRemove adds/removes links to/from a range.

 

worked very well for me, and I noticed it here at just the right time! saved me from activating and deactivating about 3,000 cells for excel to do it by itself.

As an aside, since you seem to be the excel guru, is there a way to write a @CRLF to have multiple lines in a single cell? currently, I do this by including an HTML tag (<br>) and use a macro to convert those to newline characters in what I call "post processing", but I'd prefer not to have to do that, if at all possible.

[water]

Just add a @CRLF. Example

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")

[FlashpointBlack]

aha! it took me forever and a day to figure out what was going wrong here. As it turns out, my problem wasn't caused by your UDF, it was caused by the StringStripWS function, which apparently changes @CRLF to @CR, which excel doesn't recognize as a newline character, which I suppose makes sense, but it did take a bit of tracking down. Here's a quick example:

#include <Excel Rewrite.au3>
#include <ExcelConstants.au3>

$String = "blah" & @CRLF & "blah blah" & @CRLF & "blah blah blah!"
$NoExtraSpaces = StringStripWS($String,7)
$oExcel = _Excel_Open(true, False, True, True, True)
$oWorkBook = _Excel_BookNew($oExcel, 1)
_Excel_RangeWrite($oWorkbook, 1, $NoExtraSpaces,"A1",TRUE,TRUE)
msgbox(0,"Stripped String:",$NoExtraSpaces)
msgbox(0,"Replaced String:",StringReplace($NoExtraSpaces,@CR,"-=BREAK=-"))
_Excel_BookClose($oWorkBook, false)
_Excel_Close($oExcel)
exit

so now, instead of just using:

StringStripWS($String,7)

I now use:

StringReplace(StringStripWS($temp,7),@CR,@CRLF)

which is a ghetto fix if I've ever seen one, but if it works...Thanks for your help!

Edited December 14, 2013 by FlashpointBlack

[water]

Why StringStripWS works as it does is explained >here.

Edited December 14, 2013 by water

[FlashpointBlack]

I've had a problem lately when passing the _Excel_RangeWrite function a One-Dimensional array while forcing the autoit transpose function. It seems to just repeatedly write the first array item over and over. Here's an example that reproduces it for me:

#include <Excel Rewrite.au3>
#include <ExcelConstants.au3>
#include <array.au3>
Global $Test[10] = [1,2,3,4,5,6,7,8,9,10]
_ArrayDisplay($Test) ;verify the array was built correctly

$oExcel = _Excel_Open(true, False, True, True, True);Open and Prepare Excel
$oWorkBook = _Excel_BookNew($oExcel, 1) ;create a new workbook
_Excel_RangeWrite($oWorkBook, 1, $Test, "A1", TRUE, TRUE) ;write data & force autoit transpose function
msgbox(0,"Check!","Check the active worksheet for all data") ;a pause so you can check and close
_Excel_BookClose($oWorkBook, false) ;close book
_Excel_Close($oExcel) ;close excel
exit

I wind up with range A1:A10 being all "1". Allowing excel to do it's own transposing does solve this problem, but of course not for cell lengths greater than 255.

I understand _ArrayTranspose() can't work on a 1D Array, but perhaps there's a workaround of some type for when a 1D array is passed to the function that still write the proper data and can write > 255 characters as well.

Thanks!

Edit: I've made a ghetto addition to the _ArrayTranspose function to allow it to transpose a 1D array. Here's the complete function:

Func _ArrayTranspose(ByRef $avArray)
    If UBound($avArray, 0) = 1 Then ; Return SetError(1, 0, 0)
        Local $aTemp[1][UBound($avArray,1)]
        for $z = 0 to UBound($avArray,1)-1
            $aTemp[0][$z] = $avArray[$z]
        Next
        $avArray = $aTemp
        Return 1
    elseif UBound($avArray, 0) = 2 Then
        Local $vElement = 0, $iDim_1 = UBound($avArray, 1), $iDim_2 = UBound($avArray, 2), $iDim_Max = ($iDim_1 > $iDim_2) ? $iDim_1 : $iDim_2
        If $iDim_Max <= 4096 Then
            ReDim $avArray[$iDim_Max][$iDim_Max]
            For $i = 0 To $iDim_Max - 2
                For $j = $i + 1 To $iDim_Max - 1
                    $vElement = $avArray[$i][$j]
                    $avArray[$i][$j] = $avArray[$j][$i]
                    $avArray[$j][$i] = $vElement
                Next
            Next
            ReDim $avArray[$iDim_2][$iDim_1]
        Else
            Local $aTemp[$iDim_2][$iDim_1]
            For $i = 0 To $iDim_1 - 1
                For $j = 0 To $iDim_2 - 1
                    $aTemp[$j][$i] = $avArray[$i][$j]
                Next
            Next
            ReDim $avArray[$iDim_2][$iDim_1]
            $avArray = $aTemp
        EndIf
        Return 1
    Else
        Return SetError(1, 0, 0)
    EndIf

EndFunc   ;==>_ArrayTranspose

Edited December 17, 2013 by FlashpointBlack

[water]

I'm on vacation right now but will have a look at the problem after I have returned. Means: Next year

[FlashpointBlack]

Thanks, enjoy!

[water]

The only solution I have at the moment is to create a 2D array with only one column.

Global $Test[10][1] = [[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]]

[water]

I will enhance the Excel functions so 1D arrays are transformed to 2D arrays where needed. Will be in the next beta of the UDF.
Happy New Year!

Edited December 31, 2013 by water

[water]

I have modified _Excel_RangeWrite for the next Beta like this:

expandcollapse popup

Func _Excel_RangeWrite($oWorkbook, $vWorksheet, $vValue, $vRange = Default, $bValue = Default, $bForceFunc = Default)
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If Not IsObj($vWorksheet) Then
        If $vWorksheet = Default Then
            $vWorksheet = $oWorkbook.ActiveSheet
        Else
            $vWorksheet = $oWorkbook.WorkSheets.Item($vWorksheet)
        EndIf
        If @error Or Not IsObj($vWorksheet) Then Return SetError(2, @error, 0)
    ElseIf ObjName($vWorksheet, 1) <> "_Worksheet" Then
        Return SetError(2, @error, 0)
    EndIf
    If $vRange = Default Then $vRange = "A1"
    If $bValue = Default Then $bValue = True
    If $bForceFunc = Default Then $bForceFunc = False
    If Not IsObj($vRange) Then
        $vRange = $vWorksheet.Range($vRange)
        If @error Or Not IsObj($vRange) Then Return SetError(3, @error, 0)
    EndIf
    If Not IsArray($vValue) Then
        If $bValue Then
            $vRange.Value = $vValue
        Else
            $vRange.Formula = $vValue
        EndIf
        If @error Then Return SetError(4, @error, 0)
    Else
        If $vRange.Columns.Count = 1 And $vRange.Rows.Count = 1 Then
            If UBound($vValue, 0) = 1 Then
                $vRange = $vRange.Resize(UBound($vValue, 1), 1)
            Else
                $vRange = $vRange.Resize(UBound($vValue, 1), UBound($vValue, 2))
            EndIf
        EndIf
        ; ==========================
        ; Transpose has an undocument limit on the number of cells or rows it can transpose. This limit increases with the Excel version
        ; Limits:
        ;   Excel 97   - 5461 cells
        ;   Excel 2000 - 5461 cells
        ;   Excel 2003 - ?
        ;   Excel 2007 - 65536 rows ?
        ;   Excel 2010 - ?
        ; Example: If $oExcel.Version = 14 And $vRange.Columns.Count * $vRange.Rows.Count > 1000000 Then $bForceFunc = True
        If $bForceFunc Then
            If UBound($vValue, 0) = 1 Then ; _ArrayTranspose only works for 2D arrays so we do it ourselfs for 1D arrays
                Local $aTemp[1][UBound($vValue, 1)]
                For $z = 0 To UBound($vValue, 1) - 1
                    $aTemp[0][$z] = $vValue[$z]
                Next
                $vValue = $aTemp
            Else
                _ArrayTranspose($vValue)
            EndIf
            If $bValue Then
                $vRange.Value = $vValue
            Else
                $vRange.Formula = $vValue
            EndIf
            If @error Then Return SetError(4, @error, 0)
        Else
            Local $oExcel = $oWorkbook.Parent
            If $bValue Then
                $vRange.Value = $oExcel.Transpose($vValue)
            Else
                $vRange.Formula = $oExcel.Transpose($vValue)
            EndIf
            If @error Then Return SetError(4, @error, 0)
        EndIf
    EndIf
    Return $vRange
EndFunc   ;==>_Excel_RangeWrite

Does this work for you?

[water]

Released Beta 4 of the UDF.

For download and a history of changes please see post #1.

[guinness]

Have you seen this report >> #2569

[water]

I've seen the Trac ticket but haven't looked into it.

If there are no reported problems with the Word UDF in AutoIt 3.3.10.x until end of January, then I intend to add the rewrite of the Excel UDF to the next beta.

So I think is isn't sensible to fix bugs in the "old" UDF.

[guinness]

OK.

[alongsnake]

So, where do I put the Excel Rewrite file after I download it?

Edit: I was using V3.2.2.0 when I tried this and it worked (apperently Excel.au3 was not in the include file). I just updated to the newest version and it worked (at least it compiled). My question still stands though.

Edited January 8, 2014 by alongsnake

[water]

At least put Excel Rewrite.au3 and ExcelConstants.au3 into the directory where your script resides or - better - the user defined UDF directory.