Write a Large Array of Strings to a Worksheet

Submitted By: Kent Finkle

Writes the contents of an array into a range of spreadsheet cells.

PowerShell

Edit|Remove

'*  Script name:    LargeArrayString.vbs
'*  Created on:     3:43 PM 9/16/2007
'*  Author:         Kent Finkle
'*  Purpose:        Programmatically set a large array 
'*                  of strings to a range in Excel 2003.
'*                  Compare to writing one cell at a time.
 
Const NumStrings = 10000
Dim aValues()
Redim aValues(NumStrings,2)
Set xl = CreateObject("Excel.Application")
xl.visible = true
set wb = xl.workbooks.add()
set ws = wb.Worksheets(1)
 
for i = 0 to NumStrings
    aValues(i,0) = "Test" & i + 1
next
  
ws.Range("A1:A" & NumStrings).Value = aValues

'*  Script name:    LargeArrayString.vbs
'*  Created on:     3:43 PM 9/16/2007
'*  Author:         Kent Finkle
'*  Purpose:        Programmatically set a large array 
'*                  of strings to a range in Excel 2003.
'*                  Compare to writing one cell at a time.
 
Const NumStrings = 10000
Dim aValues()
Redim aValues(NumStrings,2)
Set xl = CreateObject("Excel.Application")
xl.visible = true
set wb = xl.workbooks.add()
set ws = wb.Worksheets(1)
 
for i = 0 to NumStrings
    aValues(i,0) = "Test" & i + 1
next
  
ws.Range("A1:A" & NumStrings).Value = aValues