Jump to content
CharlesStamp

Can't find a solution to character limit problem

Recommended Posts

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:

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

#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?

Share this post


Link to post
Share on other sites
Melba23

CharlesStamp,

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

M23

Edited by Melba23
Fixed link

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


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

Share this post


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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
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).


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
Melba23

water,

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

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


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

Share this post


Link to post
Share on other sites
water

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

Global $aArray1D = [$sCopied]

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


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

Share this post


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

×