Can't find a solution to character limit problem

[CharlesStamp]

Hi. I've been putting a script together but have encountered a problem and haven't been able to find a solution. The script should copy some text in a webpage and output it to Excel. As long as the text it copies is no more than around 260 characters, it's fine. But anything longer than that and it just leaves Excel blank.

This one works:

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <String.au3>
#include <FF.au3>
#include <FFex.au3>

; Start Firefox
If _FFStart("http://ff-au3-example.thorsten-willert.de/") Then
Global $oAppl = _Excel_Open()
    
; Connect to Excel
Global $oWorkbookEnd = _Excel_BookOpen($oAppl, @ScriptDir & "\endDB.xlsx")
    
; Get page source
$sHTML = _FFReadHTML()
If Not @error Then ClipPut($sHTML)
EndIf

Sleep(2000)

    


    
Sleep(2000)
If _FFIsConnected() Then
$sCheckWord = "Sample Page"
Sleep(2000)
If _FFSearch($sCheckWord) Then

Sleep(2000)
Global $sCopied = _StringBetween($sHTML, "<H1>FF.au3 ", "static")
Else
Sleep(2000)

Global $sCopied = _StringBetween($sHTML, "<type=""", "/css")
EndIf
EndIf
Sleep(2000)



    ; Write a 1D array to the active sheet in the active workbook
    Global $aArray1D = [$sCopied]
    _Excel_RangeWrite($oWorkbookEnd, $oWorkbookEnd.Activesheet, $aArray1D, "A1")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")

    
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\endDB.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

 

But the next one doesn't. The only difference between them is that the second one is capturing a string of about 300 characters, and the first one captures a string of around 80.

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <String.au3>
#include <FF.au3>
#include <FFex.au3>

; Start Firefox
If _FFStart("http://ff-au3-example.thorsten-willert.de/") Then
Global $oAppl = _Excel_Open()
    
; Connect to Excel
Global $oWorkbookEnd = _Excel_BookOpen($oAppl, @ScriptDir & "\endDB.xlsx")
    
; Get page source
$sHTML = _FFReadHTML()
If Not @error Then ClipPut($sHTML)
EndIf

Sleep(2000)

    


    
Sleep(2000)
If _FFIsConnected() Then
$sCheckWord = "Sample Page"
Sleep(2000)
If _FFSearch($sCheckWord) Then

Sleep(2000)
Global $sCopied = _StringBetween($sHTML, "<H1>FF.au3 ", "Dokumentation")
Else
Sleep(2000)

Global $sCopied = _StringBetween($sHTML, "<type=""", "/css")
EndIf
EndIf
Sleep(2000)



    ; Write a 1D array to the active sheet in the active workbook
    Global $aArray1D = [$sCopied]
    _Excel_RangeWrite($oWorkbookEnd, $oWorkbookEnd.Activesheet, $aArray1D, "A1")
        If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")

    
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\endDB.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oAppl)
    Exit
EndIf

 

I'm not sure if it's an issue with Autoit or Excel. Can anyone suggest a way around it?

[Melba23]

CharlesStamp,

This thread offers an explanation, but not, alas, a solution.

M23

Edited May 31, 2015 by Melba23
Fixed link

[CharlesStamp]

Thanks. Is there any alternative, such as copying that variable from the clipboard or using OpenOffice Calc instead?

When I try to view that thread it says:

Sorry, there is a problem

You do not have permission to view this content.

Error code: 2F173/H

[Melba23]

CharlesStamp,

My apologies - that thread was in a private forum section. The essence of the discussion was as follows:

My understanding of the problem is:
Excel (2010) has no problems with content > 255 characters when entered by the user. But there are still some COM methods (transpose, there might be more) which only accept 255 characters and return errors or invalid results when processing larger cells.

 So it would seem that if you were to avoid the Excel UDF you might well be able to paste a larger number of characters.

M23

[water]

Function _Excel_RangeWrite uses the Transpose method which - unfortunately - is limited to 255 characters per cell.
This method has been chosen to improve performance for most cases. To cope with cells > 255 characters you need to set parameter $bForceFunc = True.
So when you change

_Excel_RangeWrite($oWorkbookEnd, $oWorkbookEnd.Activesheet, $aArray1D, "A1")

to

_Excel_RangeWrite($oWorkbookEnd, $oWorkbookEnd.Activesheet, $aArray1D, "A1", True)

everything should be fine (but a bit slower).

[Melba23]

water,

Thanks - I knew it would be a good idea to point you here.

M23

[CharlesStamp]

I've just tried changing those lines to set $bForceFunc to True but unfortunately it still doesn't work. It returns the message "1D array successfully written" as it did before, but the cell's still empty.

[water]

This line is the culprit. What do you want to do with this line?

Global $aArray1D = [$sCopied]

 

[CharlesStamp]

This line is the culprit. What do you want to do with this line?

Global $aArray1D = [$sCopied]

 

​The script I posted was a stripped-down version of the one I'm using. So although in this case it's only getting one string from the page and transferring it into Excel (making $aArray1D an unnecessary extra step), I actually need it to get several strings. The one I'm using looks like

Global $aArray2D[1][9] = [[$sCopied1[0], $sCopied2[0], $sCopied3[0], $sCopied4[0], $sCopied5[0], sCopied6[0], $sCopied7[0], $sCopied8[0], $sCopied9[0]]]
 
_Excel_RangeWrite($oWorkbookEnd, $oWorkbookEnd.Activesheet, $aArray2D, "A" & $listPosition)

If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

The other variables $sCopied1 to $sCopied8 are under 200 characters and work fine. But if $sCopied9 is over 263 characters, it only copies blank cells to Excel for every variable that time round.

I'm now using Word to get the string that was causing a problem. But I'll try the above script again (without $aArray1D and with $bForceFunc True) and let you know what happens.

 

EDIT: Looks like my syntax was off for the array, but I think I've got the hang of it now. Thanks again.

Edited June 4, 2015 by CharlesStamp
Updating status