roba77 Posted August 8, 2012 Share Posted August 8, 2012 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) Link to comment Share on other sites More sharing options...
Juvigy Posted August 9, 2012 Share Posted August 9, 2012 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 Link to comment Share on other sites More sharing options...
bogQ Posted August 9, 2012 Share Posted August 9, 2012 roba77, are you using excel 2003? TCP server and client - Learning about TCP servers and clients connectionAu3 oIrrlicht - Irrlicht projectAu3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related) 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. Link to comment Share on other sites More sharing options...
roba77 Posted August 20, 2012 Author Share Posted August 20, 2012 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. expandcollapse popup#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 Link to comment Share on other sites More sharing options...
Juvigy Posted August 21, 2012 Share Posted August 21, 2012 Send is unreliable as you will find out soon.Did you check the example i gave you? Link to comment Share on other sites More sharing options...
roba77 Posted August 22, 2012 Author Share Posted August 22, 2012 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? Link to comment Share on other sites More sharing options...
MrMitchell Posted August 22, 2012 Share Posted August 22, 2012 (edited) Is is possible to specify a new filename with the$element.Savecommand? 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 August 22, 2012 by MrMitchell Link to comment Share on other sites More sharing options...
roba77 Posted August 22, 2012 Author Share Posted August 22, 2012 Thanks! This was very helpful, here is my final code based upon your ideas: expandcollapse popup#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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now