strate Posted July 8, 2005 Share Posted July 8, 2005 I searched for this and keep getting bad responses. I've decided to give COM a try and for the life of me cannot figure out how to open a worksheet. Here is my attempt. It fill flash the page on screen but closes before I have a chance to respond at the magbox $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $MyExcel.Visible = 1 $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") Msgbox (0,"ExcelTest","Is Excel SET?") exit INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
GaryFrost Posted July 8, 2005 Share Posted July 8, 2005 HotKeySet("{ESC}","_Excel_Exit") $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $MyExcel.Visible = 1 $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") Msgbox (0,"ExcelTest","Is Excel SET?") While 1 Sleep (10) WEnd Func _Excel_Exit() exit EndFunc SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
strate Posted July 8, 2005 Author Share Posted July 8, 2005 HotKeySet("{ESC}","_Excel_Exit") $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $MyExcel.Visible = 1 $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") Msgbox (0,"ExcelTest","Is Excel SET?") While 1 Sleep (10) WEnd Func _Excel_Exit() exit EndFunc<{POST_SNAPBACK}>Still just flashes the page up, then closes it, then prompts for whether or not the page opened. Excel is open but no worksheet is visible. Thanks for giving it a shot. INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
GaryFrost Posted July 8, 2005 Share Posted July 8, 2005 Don't know enough about the com objects myself, but someone should be able to, don't have the worksheet so, just took a stab at it. SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
strate Posted July 8, 2005 Author Share Posted July 8, 2005 (edited) Don't know enough about the com objects myself, but someone should be able to, don't have the worksheet so, just took a stab at it.<{POST_SNAPBACK}>I just closed the excel process and it asked me to save the worksheet, therefore the file was open just not visible.EDIT: Trying to see if the window is hidden will not respond with anything either. Edited July 8, 2005 by strate INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
GaryFrost Posted July 8, 2005 Share Posted July 8, 2005 (edited) HotKeySet("{ESC}","_Excel_Exit") $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $MyExcel.Visible = 1 $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") ;=========================== ; this line is important to show spreedsheet ;=========================== $oExcel.Windows(1).Visible = 1 Msgbox (0,"ExcelTest","Is Excel SET?") While 1 Sleep (10) WEnd Func _Excel_Exit() $oExcel.Close $MyExcel.Quit exit EndFunc Edited July 8, 2005 by gafrost SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
scriptkitty Posted July 8, 2005 Share Posted July 8, 2005 (edited) Here is a bit of code for ya: expandcollapse popup$ordernum=InputBox('What invoice?',"enter invoice number",'444083') if @Error=1 then Exit; pressed cancel $oExcel = Objcreate("Excel.Application") ; open an Excel Object $oMyError = ObjEvent("AutoIt.Error","MyErrFunc"); Install a custom error handler if @error then Msgbox (0,"ExcelTest","Error Getting an active Excel Object. Error code: " & hex(@error,8)) exit endif tooltip("Searching",0,0) $oExcel.Visible = 1 ; Let the guy show himself $oExcel.Workbooks.Open("c:\mydir\sample.xls", 0, 1).Worksheets.Select("ON") ; the 0,1 will do this readonly ("c:\mydir\sample.xls", 0, 1) ; use this for normal ("c:\mydir\sample.xls") $oExcel.Sheets("Orders").Select; how to change worksheet tabs $oMyError = "" $oExcel.Cells.Find($ordernum).Activate sleep(1000) $x=$oExcel.ActiveCell.Address; grab address of current cell $x1=$oExcel.ActiveCell.Value; grab value of current cell ;.ActiveCell tooltip("") if $ordernum=$x1 then $loc=stringsplit($x,"$") $oExcel.Sheets("Folders").Select $name=$oExcel.Cells(($loc[3]+1),3).Value $ETA=stringtrimright($oExcel.Cells(($loc[3]+1),6).Value,6) ; comment out the rest if you don't want it to close msgbox(1,"closeing","") $objXLBook = $oExcel.ActiveWorkbook $objXLBook.Close(0); this is close without saving. $oExcel.quit func MyErrFunc() sleep(100) EndFunc it is part of my excel loopup macro. I left in a few things so you can see how to do finds and things like readonly stuff. Edited July 8, 2005 by scriptkitty AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
strate Posted July 8, 2005 Author Share Posted July 8, 2005 HotKeySet("{ESC}","_Excel_Exit") $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $MyExcel.Visible = 1 $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") ;=========================== ; this line is important to show spreedsheet ;=========================== $oExcel.Windows(1).Visible = 1 Msgbox (0,"ExcelTest","Is Excel SET?") While 1 Sleep (10) WEnd Func _Excel_Exit() exit EndFunc<{POST_SNAPBACK}>Worked wonderfully thanks for your help I had to make a change to it though, for some reason it wouldn't let you click around the cells so I rearranged a couple lines. Where could I find a list or descriptions of "Objects" like for instance in "$oExcel.Windows(1).Visible = 1" how did you know how to format the line? HotKeySet("{ESC}","_Excel_Exit") $MyExcel = ObjCreate("Excel.Application"); Create an Excel Object if @error then Msgbox (0,"","Error creating Excel object. Error code: " & @error) exit endif if not IsObj($MyExcel) then Msgbox (0,"ExcelTest","I'm sorry, but creation of an Excel object failed.") exit endif $oExcel = ObjGet("H:\136041 COM Attempt.xls","Excel.Application") ;=========================== ; this line is important to show spreedsheet ;=========================== $oExcel.Windows(1).Visible = 1 $MyExcel.Visible = 1 Msgbox (0,"ExcelTest","Is Excel SET?") While 1 Sleep (10) WEnd Func _Excel_Exit() exit EndFunc INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
GaryFrost Posted July 8, 2005 Share Posted July 8, 2005 It's in the help for ObjGet SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
scriptkitty Posted July 8, 2005 Share Posted July 8, 2005 (edited) some things of intrest, or oddities. When you create an object, you have to set the visible value to true (or 1) in order to see it. When connecting to exhisting objects with objget() (not all but some), if you try to set the visible value, it will error on you. Just one example that objects opened are not quite the same. Also look in task manager for extra objects if your scripts crashed while testing, there might be a few invisable applications. Edited July 8, 2005 by scriptkitty AutoIt3, the MACGYVER Pocket Knife for computers. Link to comment Share on other sites More sharing options...
jefhal Posted July 8, 2005 Share Posted July 8, 2005 I tried this script with Beta 56 and Excel 2003 and Excel opened, but my file did not. I tried it without the optional class parameter: $oExcel = ObjGet("C:\Documents and Settings\Jeff\My Documents\BoydHall.xls") but still got the same result. Has the syntax changed with newer versions of Excel or are you using 2003? ...by the way, it's pronounced: "JIF"... Bob Berry --- inventor of the GIF format Link to comment Share on other sites More sharing options...
GaryFrost Posted July 8, 2005 Share Posted July 8, 2005 2003 here. SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
strate Posted July 9, 2005 Author Share Posted July 9, 2005 I have another problem. If $x1 <> "Quantity" Then While 1 $Row = $Row + 1 $oExcel.Range ($Column & $Row).Select $x1 = $oExcel.ActiveCell.Value; grab value of current cell $x = $oExcel.ActiveCell.Address MsgBox(0,"4",$Column & $Row) Sleep(1000) MsgBox(0,"5",$x1 & " " & $x) Sleep(1000) If $x1 = "Quantity" Then MsgBox(0,"Exit",$x1) ExitLoop EndIf WEnd EndIf My value for $x1 will put me into the loop. I get kicked out when my value isn't equal to quantity. Do I need to change the string type? INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
GaryFrost Posted July 9, 2005 Share Posted July 9, 2005 I have another problem. If $x1 <> "Quantity" Then While 1 $Row = $Row + 1 $oExcel.Range ($Column & $Row).Select $x1 = $oExcel.ActiveCell.Value; grab value of current cell $x = $oExcel.ActiveCell.Address MsgBox(0,"4",$Column & $Row) Sleep(1000) MsgBox(0,"5",$x1 & " " & $x) Sleep(1000) If $x1 = "Quantity" Then MsgBox(0,"Exit",$x1) ExitLoop EndIf WEnd EndIfMy value for $x1 will put me into the loop. I get kicked out when my value isn't equal to quantity. Do I need to change the string type?<{POST_SNAPBACK}>Looking at your code, you have $x1 = $oExcel.ActiveCell.Value; grab value of current cell, then your checking if $x1 = "Quantity" , if it does you have ExitLoop SciTE for AutoItDirections for Submitting Standard UDFs Don't argue with an idiot; people watching may not be able to tell the difference. Link to comment Share on other sites More sharing options...
randallc Posted July 9, 2005 Share Posted July 9, 2005 Hi,Have you tried using my simple Excel functions for read/ write cell?They show the options for opening worksheet, showing it, etc.I will update it later as I have it working better toNOT error out if the cell type is not right for reading or writing (change it to "general" format first)Best, RandallExcelDemonote ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
randallc Posted July 9, 2005 Share Posted July 9, 2005 (edited) Hi again, Here is the update, name slighlty changed. just run it see it reading with/without opening/ with/ without saving; with/without exit excel. (Range function ot added yet)[Please let me know if anyone has any suggestions or can make this a "proper" UDF] [it prompts you to select an XL file if it has a different name to mine!; make one first, or add your own lines] Best, Randall Edited July 9, 2005 by randallc ExcelCOM... AccessCom.. Word2... FileListToArrayNew...SearchMiner... Regexps...SQL...Explorer...Array2D.. _GUIListView...array problem...APITailRW Link to comment Share on other sites More sharing options...
strate Posted July 9, 2005 Author Share Posted July 9, 2005 Looking at your code, you have $x1 = $oExcel.ActiveCell.Value; grab value of current cell, then your checking if $x1 = "Quantity" , if it does you have ExitLoop<{POST_SNAPBACK}>The cell it exits the loop on is blank $x1 = 0. I guess I never stated that sorry. If I took out the part for exiting the loop it will read all the way down the page. INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
strate Posted July 9, 2005 Author Share Posted July 9, 2005 Hi again,Here is the update, name slighlty changed.just run it see it reading with/without opening/ with/ without saving; with/without exit excel.(Range function ot added yet)[Please let me know if anyone has any suggestions or can make this a "proper" UDF][it prompts you to select an XL file if it has a different name to mine!; make one first, or add your own lines]Best, Randall<{POST_SNAPBACK}>Thank you, I'll give these a try when I get a chance (@work). INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
strate Posted July 11, 2005 Author Share Posted July 11, 2005 I'm still having the same problem. The code will open the workbook, goto the correct worksheet, start in cell A1 and read down each row returning the value. A section of the code If $var = "Quantity" Then MsgBox(0, "Exit Row: " & $Row, "Read Cell = " & $var) ExitLoop EndIf Should tell the program when to exit. It doesn't work properly thought. It will read down the column only one row then exit. $var doesn't equal Quantity though. Why does it exit prematurly? If I take the same piece of code and remove it from the program it will work correctly and read the entire column until I stop it. Is it something to do with string types? Here is the code in full please give it a look. expandcollapse popupHotKeySet("{ESC}", "USER_EXIT") Global $Column, $Row Dim $var $ordernum = InputBox('What invoice?', "enter invoice number", '136041') If @error = 1 Then Exit; pressed cancel $Excel = Objcreate ("Excel.Application") ; open an Excel Object $oMyError = ObjEvent ("AutoIt.Error", "MyErrFunc"); Install a custom error handler If @error Then MsgBox(0, "ExcelTest", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8)) Exit EndIf ;~ ToolTip("Searching", 0, 0) $Excel.Visible = 1 ; Let the guy show himself $FilePath = "G:\Packaging\Process Sheets\" & $ordernum & ".xls" $Excel.Workbooks.Open ($FilePath, 0, 1).Worksheets.Select ("ON") $Excel.Sheets ("Pick List").Select; how to change worksheet tabs $Row = 1 _ExcelCOM($FilePath, "Pick List", "A", $Row, 0, 4, 0, 0) MsgBox(0, "Row: " & $Row, "Read Cell = " & $var) If $var = "Quantity" Then MsgBox(0, "1 Exit Row: " & $Row, "Read Cell = " & $var) ;~ ExitLoop EndIf $oExcel = ObjGet ($FilePath) If $var <> "Quantity" Then $var = "" While 1 $Row = $Row + 1 _ExcelCOM($FilePath, "Pick List", "A", $Row, 0, 4, 0, 0) MsgBox(0, "2 Row: " & $Row, "Read Cell = " & $var) If $var = "Quantity" Then MsgBox(0, "Exit Row: " & $Row, "Read Cell = " & $var) ExitLoop EndIf WEnd EndIf MsgBox(0, "closing", "") $objXLBook = $Excel.ActiveWorkbook $objXLBook.Close (0); this is close without saving. $Excel.quit If ProcessExists("EXCEL.EXE") Then Do ProcessClose("EXCEL.EXE") Until ProcessExists("EXCEL.EXE") = 0 EndIf Func MyErrFunc() Sleep(100) EndFunc ;==>MyErrFunc Func _ExcelCOM($sFilePath, $Sheet, $Column, $Row, $Save, $ExcelValue, $Visible, $Exit) $oExcel = ObjGet ($FilePath); Get an Excel Object from an existing filename If IsObj ($oExcel) Then with $oExcel .Windows (1).Visible = 1; Set the first worksheet in the workbook visible .Worksheets ($Sheet).Activate .ActiveSheet.Visible = 1 $selc = .activesheet.range ($Column & $Row).NumberFormat .activesheet.range ($Column & $Row).NumberFormat = "General" $var = .activesheet.range ($Column & $Row).value .activesheet.range ($Column & $Row).NumberFormat = $selc EndWith Else MsgBox(0, "Excel File Test", "Error: Could not open " & $sFilePath & " as an Excel Object.") EndIf Return $var EndFunc ;==>_ExcelCOM Func USER_EXIT() If ProcessExists("EXCEL.EXE") Then Do ProcessClose("EXCEL.EXE") Until ProcessExists("EXCEL.EXE") = 0 EndIf Exit EndFunc ;==>USER_EXIT INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station... Link to comment Share on other sites More sharing options...
seandisanti Posted July 12, 2005 Share Posted July 12, 2005 sorry, looks like i'm jumping in late on this one, but just kind of curious what it is exactly that you're trying to do? personally when i'm opening workbooks, i'll just have a run() open up excel, then send("^oFilePathAndNameHere{ENTER}") to get it open... to get the value of a cell, i just find the cell with keystrokes.... send("^{HOME}") to reach A1 etc... then i'll send("^C") to get the value and just grab from the clipboard. granted to get values instead of formulas you may have to do a little more coding, but it still keeps the code pretty short and simple... that's why i'm asking what exactly you're trying to do...and sorry i kind of just skimmed the code bits (which probably would have answered my question for me) but i'm at work and have very limited time to read but would like to help if i can understand what you are trying to do... 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