views:

1108

answers:

4

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?

+1  A: 

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
paxdiablo
How can I programmically import CSV into a new page of an existing Excel workbook? Could you give an example?
Sergey Stadnik
+3  A: 

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.

Mike Woodhouse
Great suggestion. I didn't know that method existed.
Tmdean
+1  A: 

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

Tester101
There are some quirks with accessing Excel via OLEDB, but its much faster than opening up the Excel object and manipulating it like that.
AnonJr
A: 

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
AnonJr