ExcelCOM problem with .xls on Network

[RossUK]

Hi, Getting an error on my script and have no idea how to resolve it. Hope you can help

I have made a script that adds data to an excel file. I tested the script from the C:\ and all worked fine, added to an open xls located in the same place as the script. The excel file needs be located in a Network folder for the real thing though and ExcelCOM throws up an error each time I use _ExcelCOM().

Here's some of the script:

#include <ExcelCom.au3>

Opt("WinTitleMatchMode", 2) 

$RFIDateSub = ClipGet();this is a string from a word document

;Activate Excel
dim $FilePath,$var

$FilePath=@ScriptDir & "\Register.xls";@ScriptDir is where the xls is located, \\Prjsvr\ProjGrp1\

_XLshow($FilePath,1)

WinActivate("Microsoft Excel - Register.xls")

;Row input box
Msgbox(0,"Row", "Press OK and type in the row you want the new record entered into")

$row = InputBox ( "Row Select", "Type in the row you want the new record entered into")

;Enter data from .dot into the spreadsheet
Sleep(50)
_ExcelCOM($FilePath,1,"E",$row,"into",0,$RFIDateSub,1,0); Input $row, and ClipGet

Last line throws up

MsgBox(0, "Excel File Test", "Error: Could not open " & $s_FilePath & " as an Excel Object.")

from the ExcelCOM.au3

Any ideas??

[weaponx]

So for the live version you use:

$FilePath="\\Prjsvr\ProjGrp1\" & "Register.xls"

???

[RossUK]

Yeah it points to \\Prjsvr\ProjGrp1\Register.xls, I think it should be:

$FilePath="\\Prjsvr\ProjGrp1" & "\Register.xls"

but same thing, it gets the path right but doesn't like the fact its on the Network

Basically it comes up with an error saying 'Error: Could not open \\Prjsvr\ProjGrp1\Register.xls as an Excel Object.'

Edited August 23, 2007 by RossUK

[bogQ]

Yeah it points to \\Prjsvr\ProjGrp1\Register.xls, I think it should be:

$FilePath="\\Prjsvr\ProjGrp1" & "\Register.xls"

but same thing, it gets the path right but doesn't like the fact its on the Network

Basically it comes up with an error saying 'Error: Could not open \\Prjsvr\ProjGrp1\Register.xls as an Excel Object.'

r you sharing your folder with full access to the user-folder?

or

r you using map network drive option with it?

i had some simular problems on network testing, i can delite file, i can creite file, but i cant read file and resave file, and mapnetworkdrive solwed it

[mattw112]

I don't know if this helps but I'm having an excel com issue right now too. Seems my script wont work on certain machines because excel is not installed on them. Maybe it is similar with you running from network??

[weaponx]

What if you add a drive mapping for that folder?

DriveMapAdd ( "X:", "\\Prjsvr\ProjGrp1\" )
$FilePath="X:\Register.xls"

[RossUK]

OK im home from work now, I'll try your suggestions tomorrow morning and see if they work. Thanks for the feedback

[RossUK]

Well I've made some progress. I have tried the drive mapping but that brings up an AutoIt error on execute. I decided to see if Locodarwin's ExcelCOM UDF would work. It has worked and here is what I have used:

$RFISubject = ClipGet()

;Activate Excel
dim $FilePath

$FilePath=@ScriptDir & "\Register.xls"

$oExcel =_ExcelBookOpen($FilePath,1); Open book in COM object, make it visible, not ReadOnly

WinActivate("Microsoft Excel - Register.xls")

;Row imput box
Msgbox(0,"Row", "Press OK and type in the row you want the new record entered into")

$row = InputBox ( "Row Select", "Type in the row you want the new record entered into")

;Enter data from .dot into the spreadsheet (UDF2)
Sleep(50)
_ExcelWriteCell($oExcel, $RFIDateSub, $row, 5); Input $row, and ClipGet

So thats all OK, except I want to open the Register not readonly, as I need to save it later on. The function below opens the workbook, and the $fReadOnly defaults to False, so it should open as such. However it opens as readonly whatever I do, so if someone could work this one out everything will be solved.

using;===============================================================================
;
; Description:    Opens an existing workbook and returns its object identifier.
; Syntax:          $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 1)
; Parameter(s):  sFilePath - Path and filename of the file to be opened
;                   $fVisible - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                   $fReadOnly - Flag, whether to open the workbook as read-only (True or False) (default=False)
; Requirement(s):   None
; Return Value(s):  On Success - Returns new object identifier
;                  On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - File does not exist
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):        None
;
;===============================================================================
Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False)
    $oExcel = ObjCreate("Excel.Application")
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If NOT FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    With $oExcel
        .Visible = $fVisible
        .WorkBooks.Open($sFilePath, Default, $fReadOnly)
        .ActiveWorkbook.Sheets(1).Select()
    EndWith
    Return $oExcel
EndFunc;==>_ExcelBookOpen

;===============================================================================

Edited August 24, 2007 by RossUK

[RossUK]

Well I know whats wrong now, if I manually open Excel then open Register.xls is can only open it as Read Only or Notify. But if I try and map it to drive "X:" it doesn't open at all .

I've tried doing:

DriveMapAdd ( "X:", "\\Prjsvr\ProjGrp1\" )
$FilePath="X:\Register.xls"

then it should do this line:

$oExcel =_ExcelBookOpen($FilePath,1); Open book in COM object, make it visible, not ReadOnly

and:

_ExcelWriteCell($oExcel, $RFIDateSub, $row, 5); Input $row, and ClipGet

but it doesn't

Edited August 24, 2007 by RossUK

[PsaltyDS]

Still sounds to me like the file or the CIFS share are set for ReadOnly perms to your user, and the calls to Excel are not going to override that.

If you run Excel manually, manually change the cell and try to save it, what happens?