Jump to content
Sign in to follow this  
RossUK

ExcelCOM problem with .xls on Network

Recommended Posts

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

Share this post


Link to post
Share on other sites
weaponx

So for the live version you use:

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

???

Share this post


Link to post
Share on other sites
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 by RossUK

Share this post


Link to post
Share on other sites
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


TCP server and client - Learning about TCP servers and clients connection
Au3 oIrrlicht - Irrlicht project
Au3impact - Another 3D DLL game engine for autoit. (3impact 3Drad related)



460px-Thief-4-temp-banner.jpg
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.

 

Share this post


Link to post
Share on other sites
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??

Share this post


Link to post
Share on other sites
weaponx

What if you add a drive mapping for that folder?

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

Share this post


Link to post
Share on other sites
RossUK

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

Share this post


Link to post
Share on other sites
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 by RossUK

Share this post


Link to post
Share on other sites
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 by RossUK

Share this post


Link to post
Share on other sites
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?

:)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
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
Sign in to follow this  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.