I'm hoping someone might have an idea to help me speed up this code a little bit. To give you an idea of the size of the data I have to work with ... its approx 4600 rows and approx 30 columns. With my current code it takes approx 10-15 minutes to process the file. I have to dump the data into Excel because the higher-ups want the report with all kinds of Excel subtotalling and they then want it emailed to them so they have it first thing in the morning when they get in.
My vbs file queries our AS400/iSeries for the data and then dumps it into an array (this seems to run fast). Then to get the data into Excel I loop through the array horizontally and vertically using the code below. Here is the loop code:
Anyone know of a faster way to get the data into the spreadsheet? Thanks in advance,
Matt Laski
Network Administrator
Pulsafeeder SPO
My vbs file queries our AS400/iSeries for the data and then dumps it into an array (this seems to run fast). Then to get the data into Excel I loop through the array horizontally and vertically using the code below. Here is the loop code:
Code:
numCols = Ubound(myArray,1)
numRows = Ubound(myArray,2)
For rowCounter = 0 to numRows
For colCounter = 0 to numCols
objXLbook.WorkSheets(1).Cells(rowCounter + 1, colCounter + 1).value = trim(myArray(colCounter, rowCounter))
Next
Next
Anyone know of a faster way to get the data into the spreadsheet? Thanks in advance,
Matt Laski
Network Administrator
Pulsafeeder SPO