Jump to content
Sign in to follow this  
andy09

Faster alternatives to _excelbookopen and close?

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by Spiff59

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by Spiff59

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.