Re-Writing a file that is open in Excel

[Mike Trader]

My program allows the user to process some files and then examine
them in Excel. Once the user has made some decisions based on what
he sees, he can then proceed with many more files.

My problem is that if he forgets to close all of the windows in
Excel before proceeding, then my program halts with ERR = 70 when
trying to write new data to the file he left open in Excel.

I can find the window and close it, but if he has several open,
then the ones not showing cant be found. I could try sending a
Quit to Excel (drastic), but I would really like to just write
to the file while its still open in Excel.
The user can figure it out afterwards. But I hate to have my
program thwarted by an open file.

Is this possible somehow?

------------------
Kind Regards
Mike

[Lance Edmonds]

Excel will "own" the file lock, so you'll either have to close Excel or convince Excel to close the file(s).

I've not researched it, but there have been Excel code posted here. If you search the BBS for "<U>Excel API</U>" you may find some code that can help you get Excel to close the file without closing Excel.

------------------
Lance
PowerBASIC Support
mailto:[email protected][email protected]</A>

[Edwin Knoppert]

Using word, if the file WAS locked, word makes a copy.
Maybe excel too?


------------------

[Mike Trader]

Lance,
Im researching ...

Edwin thats brilliant.

I tried this:

LOCAL FandP AS ASCIIZ * %MAX_PATH 
 
FandP = Path + Name ' Path  + myfile.csv
open FandP FOR RANDOM LOCK READ WRITE AS #100
    CALL SHELLEXECUTE( 0, "open", FandP, BYVAL 0, BYVAL 0, %SW_SHOWNORMAL )
sleep 4000
close #100

unfortunatly Excel said:
"cannot Access "myfile.csv" "
bummer.

I could make a copy of the file in the TEMP folder I guess
and point Excel to it, but the next time I want to overwrite it
I will have the same problem unles I detect it and start naming them
myfile1.csv, myfile2.csv, myfile3.csv

The files are maximum 1.8MB each. Theres about 600 the user could choose

any other ideas?

------------------
Kind Regards
Mike

[This message has been edited by Mike Trader (edited August 24, 2001).]

[Lance Edmonds]

Excel will certainly fail there because you specified both <U>READ</U> and WRITE locking... make it WRITE locking only and try the idea again...


------------------
Lance
PowerBASIC Support
mailto:[email protected][email protected]</A>

[Mike Trader]

Doh,
OK lemme try again ...

Well that produces the warning:

" myfile.csv is locked for editing by 'another user'
Click 'Notify' to open a read-only copy of the document and
receive notification when the document is no longer in use "

If I CLOSE the file before the user can hit the NOTIFY button
(as opposed to the "READ ONLY" button Im good)
But if the Excel is not launched and I CLOSE too soon, Excel
will hold the lock. I guess I could do a WHILE loop and detect
the window in Excel with EnumWindowsProc() and then as soon as
I detect it release the lock before the user can hit the button.

So I think that will work great!

BTW in researching I found this link:
ftp.powerbasic.com/pub/support/usersubmitted/ directory

Does PB still have an FTP site?
can I d/l stuff?




[This message has been edited by Mike Trader (edited August 24, 2001).]

[Edwin Knoppert]

If you create a .xls file using "werkblad 2.1" (old fashion stuff) you might have better luck.
You can try this yourself.

Export to ...2.1 open this with PB and try to open it in excel.
(CLOSE EXCEL FIRST )

Export to 2.1 is not difficult..


------------------

[Lance Edmonds]

re: the powerbasic ftp site... please refer to http://www.powerbasic.com/support/pb...hread.php?t=41



------------------
lance
powerbasic support
mailto:[email protected][email protected]</a>

[Michael Mattias]

check the source code forum at:

http://www.powerbasic.com/support/pb...ad.php?t=23058

the second code list there is some code i posted to update excel thorugh dde. (the "update" code is at the end of the listing).

no locking problems, automatic immediate update to the excel user, stuff like that.

i tested with a "*.xls" file; but it's worth a try to see if excel can handle a dde update of any file it happens to have open.



------------------
michael mattias
racine wi usa
[email protected]

[Mike Trader]

THx Michael,
this will require some study. I have not used DDE befor.

------------------
Kind Regards
Mike