Faster alternatives to _excelbookopen and close?

[andy09]

Hi,

I have an excel file rawdata.exl that gets overwritten every 10th second. (I can't do much about this)

I copy values from rawdata.xls by opening it with _excelBookOpen and reads the values I want into an array.

I've to close rawdata.xls each time in order for windows to overwrite it using _excelBookClose()

I takes quite a long time for windows to open and close this file all the time.

Is there a faster way using other excel commands?

Best rergards

Andy

[Spiff59]

You'd want to avoid using those functions over and over when processing massive numbers of spreadsheets or frequent updates, as in your case. They start and restart the Excel application each time called. I think this is basically the process flow you'll need:

$oExcel = ObjCreate("Excel.Application")    ; Start Excel
$oExcel.Visible = 0
While 1                                  ; Repeatedly update workbook
    $oExcel.WorkBooks.Open($FilePath)
   ; Do your work here...
    $oExcel.ActiveWorkbook.$Save
    $oExcel.ActiveWorkbook.$Close
Wend
$oExcel.Quit                                ; End Excel

[PsaltyDS]

If you don't have to catch every single change, then you could also do a FileCopy() to temp file and work with that for longer than the time between updates. Since you are performing read only operations on the data, staying in sync with the updates to the primary file is not an issue.

[andy09]

Hi,

Spiff59, I would like to get a solutio nas you described to work.

Allthough, I can't find the command

.WorkBooks.Open($FilePath)

In the help file. and also I can't get it to work.

Is there a command like that in autoit or are you referring to something else?

Best regards

Andy

[Spiff59]

Hi,

Spiff59, I would like to get a solutio nas you described to work.

Allthough, I can't find the command

.WorkBooks.Open($FilePath)

In the help file. and also I can't get it to work.

Is there a command like that in autoit or are you referring to something else?

Best regards

Andy

Those commands are models/objects/methods built into Excel's COM+ interface.

Once you've created an excel object ($oExcel in the example), you prefix the excel COM+ commands with that object name (or other objects built off the original object) and excel interprets the commands. The commands to use COM+, like ObjCreate(), are built into Autoit, the specific excel objects and methods are not. In the "Do your work here..." section of the example, you'd be interested in commands like:

$cellvalue = $oExcel.Activesheet.Range("B1").Value; read a cell value 
; or using a different manner of specifying the target cell range:
$cellvalue = $oExcel.Activesheet.Range($row, $column).Value; read a cell value 
$oExcel2.Activesheet.Range($row, $column).Value = $x; write a cell value

The MSDN reference for Excel's COM+ interface begins here I believe: Excel Object Model Overview

EDIT: Where and what error are you getting? I've never had the ObjCreate fail yet, so don;t bother with an "IsObj()" test after it anymore. Sure you have your pathname to the .xls file corrent?

Edited February 4, 2009 by Spiff59

[andy09]

THanks, I'll look into this. But do I neeed to declare anything in the beginning of the script to sue these excel COM+ commands (Include or similar in the head of the script file)??

I've tried this bu it doesn't work. The program complains about the declaration of variables. Maybe I can't use the array script inside the "workbooks open /close"?

CODE

$sFilePath1 = "C:\Test.xls"

$oExcel.WorkBooks.Open($FilePath1)

$aArray1 = _ExcelReadArray($oExcel, 2, 4, 70, 1) ;Direction is Vertical

$oExcel.ActiveWorkbook.$Save

$oExcel.ActiveWorkbook.$Close

Looking forward to your next reply. Thank you so much for everything so far.

Br

Andy

[Spiff59]

THanks, I'll look into this. But do I neeed to declare anything in the beginning of the script to sue these excel COM+ commands (Include or similar in the head of the script file)??

I've tried this bu it doesn't work. The program complains about the declaration of variables. Maybe I can't use the array script inside the "workbooks open /close"?

CODE

$sFilePath1 = "C:\Test.xls"

$oExcel.WorkBooks.Open($FilePath1)

$aArray1 = _ExcelReadArray($oExcel, 2, 4, 70, 1) ;Direction is Vertical

$oExcel.ActiveWorkbook.$Save

$oExcel.ActiveWorkbook.$Close

Looking forward to your next reply. Thank you so much for everything so far.

Br

Andy

You're not creating the initial object or link to the excel application, $oExcel is yet undefined in your example. You need the line:

$oExcel = ObjCreate("Excel.Application"); Start Excel

If you prefer the excel window be visible:

$oExcel.Visible = 0

Stick some of that in front of your test code. Looking at "program files/autoit/include/excel.au3" will show you what any function included in the Excel UDF is actually doing. I'll often chop pieces out of those functions. I would think as long those functions aren't destroying the $oExcel object you wish to keep active, that you can mix those functions in.

EDIT: Oops, I meant "If you prefer the excel window be invisible:", and you've got an "s" in front of the pathname in your declaration, but no "s" in the .Open command.

Edited February 4, 2009 by Spiff59