Manupulating 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

One possibility would be to make it a two-stage process but it depends on where the bottleneck is.

If it's Excel, then just turn your recordset rows into a CSV-type file and then, when that's done, create the Excel object and import the entire file into a fixed location.

That's probably going to be faster than a cell-by-cell operation.

If you can't import CSV into a fixed location on the sheet (ot the cells are not in consecutive rows or columns), I'd import the CSV onto a new sheet, then do mass copies from there to your template sheet.

Moving ranges should also be faster than cell-by-cell operations.

It's the bulk import and mass copy that should give you some good improvement. I've had worksheets that processed individual cells that sped up by many factors of 10 when you use the more complex Excel functionality (think using =sum(a1..a999) instead of adding up each individual cell in VBA and putting that value somewhere).

As to how to do the import from VBA, I always rely on the "Record Macro" feature to get a baseline which can be modified (for those I'm not intimately acquainted with). This one imports c:\x.csv into the current sheet at C7:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\x.csv", _
    Destination:= Range("C7"))
    .Name = "x"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Now I'm sure most of that rubbish in there could be deleted but you'd be wise to do it one at a time to ensure no problems show up.

You could also modify that with something like the following to use a different sheet.

dim ws as worksheet
dim savealert as boolean
set ws = Sheets.Add
ws.select
' Put all that other code above in here. '
' Move all that data just loaded into a real sheet. '
savealert = Application.DisplayAlerts
Application.DisplayAlerts = False
ws.delete
Application.DisplayAlerts = savealert

Answer 2

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.

Answer 3

take a look at this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
It also might help if you connect to excel using ADODB, rather than manipulating Excel. If you need help read this artical.
http://support.microsoft.com/kb/257819

Answer 4

You can access it via an OLEDB connection, and it is infinitely faster.

Here's some code from a script I use to import data from a spreadsheet into a database. Obviously you will want to change the cursor type and the lock type, but you get the idea.

strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & objFile.Path & ";Extended Properties=""Excel 8.0;HDR=Yes"""
strSQL = "SELECT * FROM [RegistrationList$] ORDER BY DateToRegister DESC"

objExcel.Open strSQL, strExcelConn, adOpenForwardOnly, adLockReadOnly, adCmdText