Tutorial :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?


Solution: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.


Solution:2

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


Solution:3

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  


Solution: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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »