Jump to content
Sign in to follow this  
thatguypursue

Large Ranges in Excel

Recommended Posts

thatguypursue

I'm working on a program that generates a very large, formatted Excel file (6000+ lines) from a delimited file. To make the process quicker, I'm generating a large string of cells to be formatted...

; Example

$bstr = "A1,A4,B4,C4" ;This goes on for a while. End string is greater than 255 characters.

$oExcel.Activesheet.Range($bstr).Font.Bold = True

I've found that if $bstr is greater than 255 characters, the command fails. I've managed to create a loop that cuts the string into 255 characters pieces, then apply bold. This has cut my formatting time from 30 minutes to 60 seconds. However, I believe it would take just a few seconds if I could execute the command with a bigger string.

Is there a way to select a .Range() with a string greater than 255 characters?

Share this post


Link to post
Share on other sites
water

I think that's a hard coded limit (have seen this limit when transposing arrays too).

If the cells you need to format do not change then I would format an empty sheet and store it as a template. You then just need to open the template and paste the data into the pre-formatted sheet.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
thatguypursue

Unfortunately, the format will change in almost each report. I'm looking at a Union function in VBA that might get me going. It seems to make a Range of Ranges. I'll read some more on it, and see if I can make it work in AutoIt...

Activesheet.Union(Range($bstr1),Range($bstr2))

Share this post


Link to post
Share on other sites
water

Set

$oExcel.ScreenUpdating = False

before setting the ranges and set it to True again when finished.

That should speed up your solution.

  • Like 1

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
thatguypursue

Nice! That shaved another 15 seconds off. Gonna keep playing around with my code, and will post any other improvements.

Share this post


Link to post
Share on other sites
thatguypursue

I've been playing around with Union. This code works in VBA. What would the AutoIt equivalent be?

Set RangeOne = ActiveSheet.Range("A1,A2,A3,A4")
Set RangeTwo = ActiveSheet.Range("B1,B2,B3,B4")

Set CombinedRange = Union(RangeOne, RangeTwo)

Activesheet.Union(CombinedRange).Font.Bold = True

I tried the following, but it didn't work...

Local $exc = _ExcelBookNew()

$RangeOne = $exc.ActiveSheet.Range("A1,A2,A3,A4")
$RangeTwo = $exc.ActiveSheet.Range("B1,B2,B3,B4")

$CombinedRange = $exc.ActiveSheet.Union($RangeOne, $RangeTwo)

$exc.Activesheet.Union($CombinedRange).Font.Bold = True

Share this post


Link to post
Share on other sites
water

Untested, because I have no Excel here:

Local $exc = _ExcelBookNew() 
$RangeOne = $exc.ActiveSheet.Range("A1:A4") 
$RangeTwo = $exc.ActiveSheet.Range("B1:B4") 
$CombinedRange = $exc.Union($RangeOne, $RangeTwo)
$CombinedRange.Font.Bold = True
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
thatguypursue

water, you are a genius, gentleman, and a scholar. It works beautifully. Here is my final product... 

; My code searches an array ($raw) 
; for a string "[B]".
; Removes "[B]", then bolds the proper cell.

$ye = Ubound($raw,2)

For $y = 0 To $ye - 1 Step 1
   ; Search column for "[B]"
   $bfind = _ArrayFindAll($raw, "[B]", 0, 0, 1, 1, $y)
   If IsArray($bfind) Then
      $bstr = ""
      ; Convert column number to letter
      $ya = _ExcelColumnLetter($y+1)
      $btot = Ubound($bfind)
      $u = 1
      For $x = 0 To $btot-1 Step 1 
         ; Replace each "[B]" with ""
         $rep = StringReplace($raw[$bfind[$x]][$y], "[B]", "")
         $raw[$bfind[$x]][$y] = $rep
         ; If our string of cells is approaching 255,
         ; or if we've added all cells to be bolded...
         If StringLen($bstr) > 230 Or $x = $btot-1 Then
            ; Add last cell to string
            $bstr = $bstr & $ya & $bfind[$x]+1
            ; Create range from string
            $excrng = $exc.Activesheet.Range($bstr)
            If $u = 1 Then
               ; Create starter range that becomes the union
               $excun = $excrng
               $u = $u + 1
            Else
               ; Add range to union
               $excun = $exc.Union($excun, $excrng)
               $u = $u + 1
            EndIf
            $bstr = ""
         Else
            $bstr = $bstr & $ya & $bfind[$x]+1 & ","
         EndIf
      Next
      ; Bold the generated union
      $excun.Font.Bold = True
   EndIf
Next

Func _ExcelColumnLetter($iColumn = 0)
    Local $letters
    While $iColumn
        $x = Mod($iColumn, 26)
        If $x = 0 Then $x = 26
        $letters = Chr($x + 64) & $letters
        $iColumn = ($iColumn - $x) / 26
    WEnd
    Return $letters
EndFunc

The end result is one bold applied to the Excel spreadsheet. Which is much faster, (even with the added work done by AutoIt), than thousands of bold applications.

Share this post


Link to post
Share on other sites
water

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

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
Sign in to follow this  

×