excel book open not opening file

[roofninja]

I am having some trouble figuring why this is not working. What this should be doing, is open an existing excel file or create a new one. The create a new one part works. What I don't know, is why it will not take an existing file and add to it. This is a testing script, but will be added to an existing working one. All I can figure is that I am not opening the file correctly or not passing the correct path to file??

#Include <Excel.au3>
#Include <File.au3>
While ProcessExists("EXCEL.EXE")
          $ms =MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.") ;Retry=4 and Cancel=2
          If $ms=2 Then Exit
WEnd
$oExcel = FileOpenDialog("file",@ScriptDir,"(*.xls)")
If @error Then
          Local $oExcel = _ExcelBookNew(0);this is here to create the xls file if it does not exist.
          _ExcelWriteCell($oExcel,"Testing2",1,4);title line
Else
          MsgBox(0,"","open selected book__")
           Local $oExcel = _ExcelBookOpen($oExcel,0, False);this will open the chosen xls file.
EndIf
_ExcelWriteCell($oExcel,"test5",3,5) ;this writes something to file
_ExcelBookSaveAs($oExcel,@WorkingDir&"excel_test.xls","xls",0,0)
_ExcelBookClose($oExcel)
ProcessClose("EXCEL.EXE")
MsgBox(0,"Done","Done",10)

Sorry, but when I copied the file over, the spacing didn't match up.

Edited May 10, 2012 by roofninja

[MrMitchell]

I am having some trouble figuring why this is not working. What this should be doing, is open an existing excel file or create a new one. The create a new one part works. What I don't know, is why it will not take an existing file and add to it. This is a testing script, but will be added to an existing working one. All I can figure is that I am not opening the file correctly or not passing the correct path to file??

#Include <Excel.au3>
#Include <File.au3>
While ProcessExists("EXCEL.EXE")
          $ms =MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.") ;Retry=4 and Cancel=2
          If $ms=2 Then Exit
WEnd
$oExcel = FileOpenDialog("file",@ScriptDir,"(*.xls)")
If @error Then
          Local $oExcel = _ExcelBookNew(0);this is here to create the xls file if it does not exist.
          _ExcelWriteCell($oExcel,"Testing2",1,4);title line
Else
          MsgBox(0,"","open selected book__")
           Local $oExcel = _ExcelBookOpen($oExcel,0, False);this will open the chosen xls file.
EndIf
_ExcelWriteCell($oExcel,"test5",3,5) ;this writes something to file
_ExcelBookSaveAs($oExcel,@WorkingDir&"excel_test.xls","xls",0,0)
_ExcelBookClose($oExcel)
ProcessClose("EXCEL.EXE")
MsgBox(0,"Done","Done",10)

Sorry, but when I copied the file over, the spacing didn't match up.

You're using $oExcel for two different things...as a string filename and as an Excel obj. You can see how that might create an issue with:

Local $oExcel = _ExcelBookOpen($oExcel,0, False);this will open the chosen xls file.

I suppose it could work but it's bad practice.

Also, this:

Local $oExcel = _ExcelBookNew(0);this is here to create the xls file if it does not exist.

I think this will create the new Workbook regardless, and open it in a hidden state.

If you want to attach to the existing workbook, you have to know something about it that you could use to attach to it, see _ExcelBookAttach(). I suppose you could blindly attach to the first Workbook it finds but that would be bad practice.

[roofninja]

It is bad practice on how I am using ExcelBookOpen. So what should I do?

Yes, the ExcelBookNew will create a new Workbook. I only need it to create a new Workbook when a Workbook is not selected with the FileOpenDialog. There will be times with that happens.

I don't think I want to attach to an existing Workbook? What the program should be doing is to add lines from a file to the bottom of the Workbook. If the Workbook doesn't exist, the create one and then add lines from file to Workbook.

[Reg2Post]

OP, you are re-assigning your path variable to the return value for the _ExcelBookOpen UDF and then telling the function to open the return value as a path in Excel.

Just don't assign the return value to a variable if you don't need it or change the variable name.

You are just not using the path parameter correctly if you do so.

Local $oExcel = _ExcelBookOpen($oExcel,0, False)

[MrMitchell]

It is bad practice on how I am using ExcelBookOpen. So what should I do?

Yes, the ExcelBookNew will create a new Workbook. I only need it to create a new Workbook when a Workbook is not selected with the FileOpenDialog. There will be times with that happens.

I don't think I want to attach to an existing Workbook? What the program should be doing is to add lines from a file to the bottom of the Workbook. If the Workbook doesn't exist, the create one and then add lines from file to Workbook.

here's an example of how I would begin to go about it...

#Include <Excel.au3>
#Include <File.au3>
Dim $oExcel, $sExcelFile, $ms
Dim $fExcelVisible = 1 ;Change this to 0 for Production
While ProcessExists("EXCEL.EXE")
$ms = MsgBox(5,"","Process error.  You have an Excel sheet open.  You must close it in order to let this program work.  Please close it now.") ;Retry=4 and Cancel=2
    If $ms=2 Then Exit
Sleep(250)
WEnd
$sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xls)") ;Changed $oExcel to $sExcelFile
If FileExists($sExcelFile) Then
MsgBox(0,"","open selected book__")
    $oExcel = _ExcelBookOpen($sExcelFile,$fExcelVisible, False);this will open the chosen xls file.
If IsObj($oExcel) Then ConsoleWrite("Excel book " & $sExcelFile & " has been opened." & @CRLF)
Else
$oExcel = _ExcelBookNew($fExcelVisible);this is here to create the xls file if it does not exist.
_ExcelBookSaveAs($oExcel,$sExcelFile) ;Save it immediately
    _ExcelWriteCell($oExcel,"Testing2",1,4);title line
EndIf
_ExcelBookClose($oExcel, 1) ;Save and close
ProcessClose("EXCEL.EXE")
MsgBox(0,"Done","Done",10)

[roofninja]

I like what I see, but I have a question in your example. In the Else statement of the IF where the _ExcelBookNew is used, why are you saving the file and then adding stuff to it? Don't you have to save it again before you close the WorkBook?

[MrMitchell]

I'm creating it and saving it right away. Then add to it. Then at the end save and close it. You could just as easily move the saveas to the end of the script but this is just the way I sometimes do it. Not really any good reason, just preference.

[Reg2Post]

I'm creating it and saving it right away. Then add to it. Then at the end save and close it. You could just as easily move the saveas to the end of the script but this is just the way I sometimes do it. Not really any good reason, just preference.

I find myself using saveas immediately after creating a non-temp workbook if I need the path name for a function instead of trying to figure out if it is book1, book2, book3, etc. Most VBA code needs full file paths and will not take an object handler.