Jump to content

Recommended Posts

Posted

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

My UDFs and Tutorials:

  Reveal hidden contents

 

  • Moderators
Posted

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

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

  • 2 weeks later...
Posted (edited)

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

_Excel_RangeLinkAddRemove.au3

Edited by water

My UDFs and Tutorials:

  Reveal hidden contents

 

  • 2 weeks later...
Posted
  On 12/2/2013 at 11:23 AM, water said:

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.

Posted

Just add a @CRLF. Example

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

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted (edited)

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 by FlashpointBlack
Posted (edited)

Why StringStripWS works as it does is explained >here.

Edited by water

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted (edited)

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 by FlashpointBlack
Posted

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

My UDFs and Tutorials:

  Reveal hidden contents

 

  • 2 weeks later...
Posted

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

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted (edited)

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

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted

I have modified _Excel_RangeWrite for the next Beta like this:

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?

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted

Released Beta 4 of the UDF.

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

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted

Have you seen this report >> #2569

UDF List:

  Reveal hidden contents

Updated: 22/04/2018

Posted

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.

My UDFs and Tutorials:

  Reveal hidden contents

 

Posted

OK.

UDF List:

  Reveal hidden contents

Updated: 22/04/2018

Posted (edited)

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 by alongsnake
Posted

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

My UDFs and Tutorials:

  Reveal hidden contents

 

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
×
×
  • Create New...