Excel - Is file open
Excel - Is file open
Excel - Is file open
Excel - Is file open
Excel - Is file open
Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open
Excel - Is file open Excel - Is file open
Excel - Is file open
Go Back  Xtreme Visual Basic Talk > > > Excel - Is file open


Reply
 
Thread Tools Display Modes
  #1  
Old 04-22-2010, 08:18 PM
Toolish Toolish is offline
Newcomer
 
Join Date: Feb 2009
Posts: 3
Default Excel - Is file open


I have written a macro in Autodesk Inventor that opens up an excel spreadsheet and amends some data to the end of the sheet.

The problem I have is when this spreadsheet is already open on my PC the macro opens the spreadsheet again in a read only format which then causes an error as the data can't be added.

I have been using

objWorkbook = objWbs.Open("Z:\DRAWINGS\Complete_Parts_List.xls")

to open the workbook.

Is there a way to test if the file is already open before doing this?
Reply With Quote
  #2  
Old 04-23-2010, 05:41 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Toolish,

Welcome to the forum.

You can test if the file is open, but the real problem is to figure out what you are going to do about it if it is? You'll have to get the other user to close their workbook, which your program cannot do. I guess you could show an error message to the user, stating that another user is using this file, and asking them to try again after this file is no longer in use. Or you'll need to save your changes to another location.

To check if the workbook is open, there are some advanced techniques that you can use. See:

(1) Checking if a Workbook is Open in any Excel Instance (Mark Rowlinson).

(2) Get the name of the user who currently has the workbook open (Helen Toomik)

That said, I don't see why you would need to go through so much trouble. My advise would be to simply open the workbook and then check the Workbook.ReadOnly property. If it's True, then you have a problem and you will need to either close the workbook immediately and try something else, or continue with the program and save changes to another location.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 06-02-2010, 04:40 PM
Toolish Toolish is offline
Newcomer
 
Join Date: Feb 2009
Posts: 3
Default

Mike

Thanks for the help. If the workbook is open on my computer I want to reference to it within my code so as to modify the open version rather than starting a new version.

If it is open by another user on the network I want to force close it.

Is there a way to do this?
Reply With Quote
  #4  
Old 06-03-2010, 08:47 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi Toolish,

No, you can't force another application to close the file that it is using. You could force the other application to shut down, but this is very brutal and I would not condone that. (How would you feel if some other 3rd party application caused your Excel session to crash?) Lastly, forcing such a shutdown across a network would not be possible.

So you are going to have to find another way around this.

You could operate off of a copy of the workbook that is being used. Or you could identify the user that has the workbook open (see the posts above) so that you can at least know who to ask to close it before you can operate.

This is about all I can suggest for now.

-- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel - Is file open
Excel - Is file open
Excel - Is file open Excel - Is file open
Excel - Is file open
Excel - Is file open
Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open Excel - Is file open
Excel - Is file open
Excel - Is file open
 
Excel - Is file open
Excel - Is file open
 
-->