Manipulating Excel files from Windows Scripting Host

Question

Is there a fast way to manipulate the contents of an existing XLS file from Windows Scripting Host?

We have Excel templates we received from a customer. Our task is to fill these templates with the data we fetch from an Oracle database.

The current approach is to use Windows Scripting Host and VBScript:

1. Get data from Oracle using ADODB:

   Set db = CreateObject("ADODB.Connection")
   SQL = "SELECT ..."
   Set rs=db.execute(SQL)
   

2. Create an Excel object in Windows Scripting Host using VBScript:

   Set objExcel = CreateObject("Excel.Application")  
   Set objWorkbook = objExcel.Workbooks.Open(xls_final)  
   Set objSheet = objWorkBook.Sheets(1)
   

3. And then fill in the template cell-by-cell like this:

   If rs.EOF = False Then
      rs.MoveFirst
      Do Until rs.EOF
         objSheet.Cells(RowNumber, 1).Value = rs("COLUMN1")
         objSheet.Cells(RowNumber, 2).Value = rs("COLUMN2")
         objSheet.Cells(RowNumber, 3).Value = rs("COLUMN3")
         rs.MoveNext
      Loop
   End If
   objWorkbook.Save 
   rs.Close
   

The problem is that some of these files contain a lot of data and it takes hours to fill them like this. Is there a faster way to do it?

Answer 1

I think you're fine up to here:

Set db = CreateObject("ADODB.Connection")
SQL = "SELECT ..."
Set rs=db.execute(SQL)

Set objExcel = CreateObject("Excel.Application")  
Set objWorkbook = objExcel.Workbooks.Open(xls_final)  
Set objSheet = objWorkBook.Sheets(1)

But the remainder is going to be appallingly slow, as you've discovered. Interactions with worksheets has a high overhead, which you're paying for every column in every row. There are some ways round this.

The simplest is

objSheet.Cells(1,1).CopyFromRecordset rs

which I recommend you try first.