Large Ranges in Excel

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

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

[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))

[water]

Set

$oExcel.ScreenUpdating = False

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

That should speed up your solution.

[thatguypursue]

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

[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

[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 February 17, 2014 by water

[thatguypursue]

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

expandcollapse popup

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

[water]