Sign in to follow this  
Followers 0
roba77

Activating excel workbooks which are opened by another program

8 posts in this topic

Hello, I am trying to automatically activate and save two seperate excel workbooks which are opened automatically by another program. I am using the following code, but the problem I run into is that it will only operate on the active workbook. It seems that all active excel sessions are included in the object $oExcel1, as if I run the _ExcelBookClose command, all excel workbooks are closed. So, is there a way to select only one currently running workbook, do some modifications to it, close it,and then select the other?

#include <Excel.au3>
$sFilePath="Z:\"&@MIN&"min"&@SEC&"infared.xls"
$sFilePath2="Z:\"&@MIN&"min"&@SEC&"Vis.xls"
Local $oExcel1 = ObjGet("", "Excel.Application") ; Get an existing Excel Object
If @error Then
MsgBox(0, "ExcelTest", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8))
Exit
EndIf
;$oExcel2 = _ExcelBookAttach("Microsoft Excel-Book1", "Title") I tried this however it did not recognize the window
_ExcelBookSaveAs($oExcel1, $sFilePath ,"xls" ,0 )
_ExcelBookClose($oExcel1)

;_ExcelBookSaveAs($oExcel2, $sFilePath2,"xls" ,0 ) ideally, I would like to run this code for the second window, however all windows are closed with the first close operation.
;_ExcelBookClose($oExcel2)

Share this post


Link to post
Share on other sites



Try _ExcelBookAttach with filename parameter. Other way is this:

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
               If $element.FullName <> "" Then
                   MsgBox(0,"",$element.FullName)
                   $element.Save
                   $element.Close
               Else  
                   $element.Activate
                   $oExcel.Application.Dialogs(5).Show
               EndIf
           Next
$oExcel.Application.Quit

Share this post


Link to post
Share on other sites

roba77, are you using excel 2003?


TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
There are those that believe that the perfect heist lies in the preparation.
Some say that it’s all in the timing, seizing the right opportunity. Others even say it’s the ability to leave no trace behind, be a ghost.

 

Share this post


Link to post
Share on other sites

Hello, apologies for my long delay on this thread. I have solved my issue by using the following code. I used keyboard shortcuts instead of the built in excel functions to give me the ability ot do custom file associations and choose between the two open workbooks.

The purpose of this code is to run once daily to save and close the workbooks which are collecting data. It will then restart the measurement, which will open two new workbooks, and repeat. In order to schedule this once daily I have a windows scheduler task which runs the scripts. These scripts work well when I am testing them, even when testing using the windows task scheduler. However when leaving the program to run unattended it will fail after one or two trials, usually after saving the first excel workbook. Any ideas what could be causing the problem, or what error checking I could implement into this? I am running windows XP with excel 2007 and the external software I am running is AvaSoft, which runs an external spectrometer.

#include <GUIConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>


WinActivate("History Channel Functions") ;open the window controlling the measurements
sleep(2000)
send("!e") ;close this window
sleep(1000)
;This is where the excel part begins to save both opened workbooks 
$infFilePath="D:Documents and SettingsrgauthierDesktopSpectrometer DropboxDropboxDropbox Excel DataDataInfared"&@YEAR&"-"&@YDAY&"-"&@HOUR&"-"&@MIN&".xls"
$visFilePath="D:Documents and SettingsrgauthierDesktopSpectrometer DropboxDropboxDropbox Excel DataDataVisible"&@YEAR&"-"&@YDAY&"-"&@HOUR&"-"&@MIN&".xls"

Local $oExcel=ObjGet("","Excel.Application")
if @Error Then
   MsgBox(0,"Error",hex(@error,8))
   Exit
EndIf

Global $wkbook
$oExcel.visible=1
$oExcel.WorkBooks(1).Activate
$wkbook=$oExcel.ActiveWorkBook.name
;MsgBox(0,"Workbooks",$wkbook)
send("!fa")
sleep(1000)
send($visFilePath)
send("{ENTER}")
$oExcel.WorkBooks(2).Activate
$wkbook=$oExcel.ActiveWorkBook.name
;MsgBox(0,"Workbooks",$wkbook)
send("!fa")
sleep(1000)
send($infFilePath)
send("{ENTER}")
sleep(1000)
send("!{F4}")
;this is where the excel part ends


WinActivate("AvaSoft") ;start the measurement program
sleep(1000)
send("!ahs") ;start measuring (accessed through command windows) this will start two new instances of excel

Share this post


Link to post
Share on other sites

Send is unreliable as you will find out soon.Did you check the example i gave you?

Share this post


Link to post
Share on other sites

Hello, yes I am seeing that.

Is is possible to specify a new filename with the

$element.Save

command? I need the file name to be based on the computers clock. In addition, I need to save and close two sheets, which are open as "Sheet1" and "Sheet2" with separate file names for each. When looping through the elements, will they always be activated in the order in which they were created? eg.always work on sheet1 first then sheet2?

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Is is possible to specify a new filename with the

$element.Save

command? I need the file name to be based on the computers clock. In addition, I need to save and close two sheets, which are open as "Sheet1" and "Sheet2" with separate file names for each.

You should be able to use $element.SaveAs("C:filename.xls") and it should save in your current version of Excel's default format.

Edit: As for your other question, I believe it will loop through each workbook in the order in which it was created. Here are all the methods for the Workbook class, which is what your $element is: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbookclass_methods(v=office.11)

Edited by MrMitchell

Share this post


Link to post
Share on other sites

Thanks! This was very helpful, here is my final code based upon your ideas:

#include <GUIConstantsEx.au3>
#include <Date.au3>
#include <WindowsConstants.au3>
; Under Vista the Windows API "SetSystemTime" may be rejected due to system security
WinActivate("History Channel Functions")
sleep(2000)
send("!e")
sleep(1000)
$infFilePath="D:Documents and SettingsrandrewsDesktopTestInfared"&@YEAR&"-"&@YDAY&"-"&@MON&"-"&@MDAY&"-"&@HOUR&"-"&@MIN&".xls"
$visFilePath="D:Documents and SettingsrandrewsDesktopTestVisible"&@YEAR&"-"&@YDAY&"-"&@MON&"-"&@MDAY&"-"&@HOUR&"-"&@MIN&".xls"
Global Const $xlNormal = -4143 ;parameter to save as .xls
Global Const $iAccessMode = 1
Global Const $iConflictResolution = 2
Global  $errcnt=0
$sType = $xlNormal ;sets the filetype to save
$oExcel = ObjGet("", "Excel.Application")
$errcnt=0
For $element In $oExcel.Application.Workbooks
  
   If $element.FullName = "Sheet1" Then
   ;MsgBox(0,"",$element.FullName)
   $element.SaveAs($infFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution) 
   $element.Close    
   ElseIF  $element.FullName = "Sheet2" Then  
   ;MsgBox(0,"",$element.FullName)
   $element.SaveAs($visFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution) 
   $element.Close 
   Else             
   $element.Activate 
   $errcnt=$errcnt+1
   $errFilePath="D:Documents and SettingsrandrewsDesktopTestError"&@YEAR&"-"&@YDAY&"-"&@MON&"-"&@MDAY&"-"&@HOUR&"-"&@MIN&"-("&$errcnt&").xls"
  
    $element.SaveAs($errFilePath, $sType, Default, Default, Default, Default, $iAccessMode, $iConflictResolution) 
    sleep(500)
   $element.Close   
   EndIf      
Next
$oExcel.Application.Quit

sleep(1000)
WinActivate("AvaSoft")
sleep(1000)
send("!ahs")

So far this looks to be a lot more stable and has some error checking in it as well.

Thanks for your help

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  
Followers 0