Jump to content

Open a Excel Worksheet


Recommended Posts

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

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

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

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

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

Here is a bit of code for ya:

$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 by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Link to comment
Share on other sites

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

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 by scriptkitty

AutoIt3, the MACGYVER Pocket Knife for computers.

Link to comment
Share on other sites

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

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

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?

<{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

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, Randall

ExcelDemonote

Link to comment
Share on other sites

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 by randallc
Link to comment
Share on other sites

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

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

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.

HotKeySet("{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

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

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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...