How to check if excel is already open when using .workbooks.open 3

[gryff15]

I have a program that gets an excel file to get its data and do some processing. The system hangs when using
oExcel.WorkBooks.Open(SampleExcel) when the excel being uploaded is already open. The error goes "This action cannot be completed because the other program is busy.. [Switch to][Retry]".
How can we validate so we can tell the user to close the file first? Or can we let it proceed even when it is open?

oExcel = Createobject("Excel.Application")
oExcel.WorkBooks.Open(SampleExcel) && system hang here if file is open

&& some processing of the data

oExcel.WorkBooks.Close()
oExcel.Quit
Release oExcel

- gryff15 -

 

[GriffMG]

Morning Gryff15

I would try and rename the file before I used it, if you can, it's yours to open, if not it is already open.
If it's yours, name it back and open it.


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[gryff15]

Hi Griff,

I don't quite get what you meant by renaming it? How am I able to get the same file with a different name?

- gryff15 -

 

[GriffMG]

You don't

Just TRY and rename it, if you can, rename it back and open it.
If you can't then it's open.

m.FILENAME = "d:\$incoming\popatt.xls"
IF TRYOPEN(m.FILENAME)
	** do whatever
ELSE
	MESSAGEBOX("unable to open",48,"Wow!")
ENDIF

FUNCTION TRYOPEN
	PARAMETERS m.OPENFILE
	PRIVATE m.OPENFILE,m.FILEEXT,m.FILESTEM,m.FILEPATH,m.FLG
	m.FLG = .F.
	m.FILEEXT = JUSTEXT(m.OPENFILE)
	m.FILEPATH = JUSTPATH(m.OPENFILE)
	m.FILESTEM = JUSTSTEM(m.OPENFILE)
	ON ERROR DO FILEERR
	RENAME (m.OPENFILE) TO (m.FILEPATH+"\"+m.FILESTEM+"."+m.FILEEXT+"!")
	IF FILE(m.FILEPATH+"\"+m.FILESTEM+"."+m.FILEEXT+"!")
		m.FLG = .T.
		RENAME (m.FILEPATH+"\"+m.FILESTEM+"."+m.FILEEXT+"!") 	TO (m.OPENFILE)
	ENDIF

	ON ERROR
	RETURN(m.FLG)


PROCEDURE FILEERR
	RETURN

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Triple advice here:

1) You automate another process, an error or dialog happening in it doesn't trigger an error in VFP, you get a message such as "This action cannot be completed because the other program is busy.. [Switch to][Retry]" as a timeout. The first step to defend against that is to make your Excel instance visible at least while you develop and test things. You have a case to test, open a file in Excel, automate Excel and try to open the same file in a new instance with workbooks.open().

2) See descriptions of the _VFP properties OLERequestPendingTimeout, OLEServerBusyRaiseError, and OLEServerBusyTimeout. Btw. if you wonder what a server has to do with it Excel.Application is an automation server, a COM server AKA OLE class.

3) Specifically in Excel you have the property DisplayAlerts of the Excel.Application object/server, which you can set False or 0 or .F. (from VFP) - see

https://learn.microsoft.com/en-us/office/vba/api/excel.application.displayalerts

That's not only applicable to suppress the "Save Changes?" question when you close something. Instead of the user dialog that's unseen as you don't automate Excel while it's visible, you then will get an error triggered in VFP when you use Workbooks.Open().

The major lessons to learn from 1) is that you can't expect errors happening in another process to raise an error event in VFP and cause the catch block in a TRY...CATCH or the general ON ERROR routine. That is well known when using SQLPassthrough functions, where getting the return value 0 from SQLExec, for example, means you have to pull out what error happened with AERROR(). That's not generally that way, too. In your case, the call to Workbooks.Open opens up a dialog to the user that's putting it into a modal state and the call does not end and return, no error happens, just a call into the OLE class taking too long to return. Therefore after a timeout, you get that default "busy" message with the RETRY/SWITCH buttons, which most commonly both don't resolve the issue. You can't switch to something invisible. And retrying the same method call, you get the same modal blocking.

In 2) you get hands on how that timeout process works. It's best to try out the meanings of the properties by implementing your own COM server (an OLEPUBLIC class) that has a method which calls Messagebox(), for example, to cause a non-returning modal blocking state.

Also take a look into COMReturnerror(), while you're experimenting with your own COM server to see how this mechanism works and can raise an error from the COM server in the client that called. In a class, you would program the Error event of it and within that do a COMRETURNERROR() call, which will show up in the client as an OLE error (1427 or 1429) reported by LineNo() or AERROR() in the line calling the COM server method.

Well, and finally 3) is another resolution but only very specific to Excel, I think Word has a similar property. But don't come to think because the timeout mechanism and choosing between timeout and raising an error, this mode of not allowing dialogs or any modal states is an OLE standard, too. So you only sometimes find solutions like DisplayAlerts, where the developers of the OLE automation server actually thought about the automation that happens in the non-visible mode of the application that's automated.

If you ask yourself why things aren't the same anywhere, the answer is a bit of opinion, I guess MS didn't want to make an OLE interface a hurdle to programming automation servers that have to adhere to a set of basics before you can start with the methods you actually want to provide to the automation server user. So there is no nonvisual mode that COM server code has to take into account or not depending on a standard setting. The other simpler answer is that the Office suite consists of software MS purchased from different original sources and while they certainly did a lot of things to make the suite look like it's one suite, the details show that the single parts of the suite have different roots.

Chriss

 

[gryff15]

Hi Griff and Chris Miller,

Thank you for your answers even though they are a little to much for me to absorb already. I really appreciate them and have tried researching on those automations. Still, I failed, but I think I got the idea regarding the error. When I end-task the VFP when it produced switch/retry message, I saw an error from SharePoint appeared telling that the file is accessed in another program or something, and I thought it had something to do with problem. The excel file is located at a one drive location where it gets synchronized to the cloud. I tried to copy the excel file to a local folder that is not synced to OneDrive and now my program proceeds when I upload that excel file, regardless if it is open or not.

- gryff15 -

 

[Chris Miller]

Indeed I not sure Excel is using exclusive access to files it opens as workbook. But then it was likely synced at the time you wanted to access it. That's indeed very probably if you create it with COPY TO or EXPORT to then open it in Excel and work on it with your code. I'd always just put files into a directory that is synced with the cloud, when I am finished with that file and it's ready to be shared or backed up that way. It does only mean multiple times syncing and all kinds of access problems, if you create a file in the place it is synched to then work on it further.

Cloud drive synching usually installs software that monitors the directory or directories (and subdirectories) to sync and act immediately, they don't wait for the file to sit unchanged for a while before they start syncing. It's actually very logic to avoid working within such directories and make it a last step to move a file there only after you're finished with it. They are for synching your final files, not for drafts and work in progress. I don't mean work in progress over days, weeks or years, but the usual first EXPORT yoou immediately open and apply changes in format or other stuff.

You will perhaps notice that you get such messages with some applications but not others. Notepad, for example, reads in a file as a whole - that's the reason opening large txt or log files takes longer in notepad than in Wordpad or notepadd++ or other editors not doing that - and then works in memory, saving all changes to the file only when you save, not continuously changing the file.

I think Excel would also rather work in memory than within the file, especially in the newer formats that are in fact zip archives on the drives which makes it even harder to work on the files themselves live. But also that is problematic, as the changes your code does and save could easily be overwritten by another Excel session, simply if that saves its changes made on the basis of an earlier version later.

Please consider such things happening when you program something - think on the deepest level and be a pessimist about what could happen. If the filew originate in your application, then keep them local and exclusive to yourself as long as you're not finished, maybe even in TEMP or any other faster local directory, before you finally put the files where their (preliminary) end result belongs.

You should as a developer even make your customers aware of the fact that sync directories are not meant to be used for work in progress as the can cause all kinds of problems with the concurrent access of applications working on the files while they sync. You can get into situations where the syncing even creates invalid files. It's also a very simple reason synced folders are really bad for storing DBFs for synced shares that are local directories in each VFP client. Such constructs are bound to fail, the multiple syncing of whole files the more changes you apply to them within a VFP session are just one aspect that's most obvious if you'd simply pay attention to what's happening in the syncing software. That's usually signaling what it does in the systray.

Chriss

 

[GriffMG]

On a local PC, or 'normal' networked drive, a local copy of Excel will open a file in
a shared mode, not exclusively (ops locks aside). You can test this for yourselves
by opening a spreadsheet in Excel, then using filetostr() to read it in VFP.

However, when open in Excel the file cannot be deleted or renamed by another process.
So trying to do so is indicative of an impending failure if you try to use automation
to open the file in excel when it is either open already locally or being accessed by
another user on a network share.




Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Ah, thanks for that, Griff.

I currently have no office suite installed and just use the online variations of the office suite.

I am aware the different office applications do different thing. Word creates a file in 8.3 name style with a tilde name~1.doc, for example. And that's also not what Excel does. File handling is not made one overarching concept of the office applications.

Chriss