Count Workbooks that are open; Prevent another from opening

I’m running the code from here:
http://support.microsoft.com/kb/291295/EN-US/

That works great if I get into the Sub and hit F5!  Now, I’m trying to think 
of a way I can modify this slightly so as to prompt a user that the WB is 
already open if they try to open the file again.  The file will be saved back 
to SharePoint and I want to make sure there is ONE SINGLE incidence of this 
Excel WB open at a time (it’s fine if several other WBs are open).  
Basically, I don’t want someone to open the file, do some work, walk away, 
forget that it’s open, and try to open it again (from SharePoint).

I think I’m going to need something like:
workbook_open (in thisworkbook) 

or

workbook_activate (in thisworkbook) 

or

AUTO_OPEN (in a module)

I think, for this to work, I’m going to have to count the number of WBs open 
(with this WB name, right).  So, 

If count <=1 Then workbook_open
   Else call TestFileOpened
End if

Thanks!

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.
0
Utf
5/20/2010 7:56:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
863 Views

Similar Articles

[PageSpeed] 54

I modified the code a bit and am now using the version below:
Sub MyMacro()
Dim i As Long
Dim wbk As Workbook
    
    For Each wbk In Workbooks
        
        i = Application.Workbooks.Count
        If i.wbk.Name > 1 Then
            If IsFileOpen("I:\Ryan\Copy of Book2.xls") Then
                MsgBox "File already in use!"
                Else
            End If
        End If
    Next wbk

End Sub

' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns False.
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next
    filenum = FreeFile()
    Open filename For Input Lock Read As #filenum
    Close filenum
    errnum = Err
    On Error GoTo 0

    Select Case errnum
        Case 0
         IsFileOpen = False
        Case 70
            IsFileOpen = True
    End Select
End Function

I get an error on this line:
If i.wbk.Name > 1 Then

I guess i.wbk.Name is an invalid qualifier.  What I’m trying to do is count 
the number of Workbooks open and if that number >1 then prompt the user that 
the Workbook is already open – another Workbook will NOT open.

I think this code is pretty close, but I can’t seem to count the number of 
Workbooks of a specific name.  If a user has other Workbooks open (other 
names), I don’t mind.  I just don’t want multiple versions of THIS Workbook 
open.

Thanks!
Ryan--

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"ryguy7272" wrote:

> I’m running the code from here:
> http://support.microsoft.com/kb/291295/EN-US/
> 
> That works great if I get into the Sub and hit F5!  Now, I’m trying to think 
> of a way I can modify this slightly so as to prompt a user that the WB is 
> already open if they try to open the file again.  The file will be saved back 
> to SharePoint and I want to make sure there is ONE SINGLE incidence of this 
> Excel WB open at a time (it’s fine if several other WBs are open).  
> Basically, I don’t want someone to open the file, do some work, walk away, 
> forget that it’s open, and try to open it again (from SharePoint).
> 
> I think I’m going to need something like:
> workbook_open (in thisworkbook) 
> 
> or
> 
> workbook_activate (in thisworkbook) 
> 
> or
> 
> AUTO_OPEN (in a module)
> 
> I think, for this to work, I’m going to have to count the number of WBs open 
> (with this WB name, right).  So, 
> 
> If count <=1 Then workbook_open
>    Else call TestFileOpened
> End if
> 
> Thanks!
> 
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
0
Utf
5/21/2010 3:23:01 PM
> Dim i As Long
> ........
> i = Application.Workbooks.Count
> ........
> I get an error on this line:
> If i.wbk.Name > 1 Then

The variable 'i' is Dim'med as a Long... it can't have any "dotted" 
parameters following it. Normally, I would say you meant to type this...

 If wbk.Name > 1 Then

however, that won't work unless the workbooks is named as numbers (all 
digits), which seems unlikely. Did you, perhaps, mean to use the Count 
property you had just calculated in the line above?

If i > 1 Then

-- 
Rick (MVP - Excel)



"ryguy7272" <ryguy7272@discussions.microsoft.com> wrote in message 
news:2984EFED-43E7-4EB5-9FA1-B6D2D40EAE44@microsoft.com...
> I modified the code a bit and am now using the version below:
> Sub MyMacro()
> Dim i As Long
> Dim wbk As Workbook
>
>    For Each wbk In Workbooks
>
>        i = Application.Workbooks.Count
>        If i.wbk.Name > 1 Then
>            If IsFileOpen("I:\Ryan\Copy of Book2.xls") Then
>                MsgBox "File already in use!"
>                Else
>            End If
>        End If
>    Next wbk
>
> End Sub
>
> ' This function checks to see if a file is open or not. If the file is
> ' already open, it returns True. If the file is not open, it returns 
> False.
> Function IsFileOpen(filename As String)
>    Dim filenum As Integer, errnum As Integer
>
>    On Error Resume Next
>    filenum = FreeFile()
>    Open filename For Input Lock Read As #filenum
>    Close filenum
>    errnum = Err
>    On Error GoTo 0
>
>    Select Case errnum
>        Case 0
>         IsFileOpen = False
>        Case 70
>            IsFileOpen = True
>    End Select
> End Function
>
> I get an error on this line:
> If i.wbk.Name > 1 Then
>
> I guess i.wbk.Name is an invalid qualifier.  What I’m trying to do is 
> count
> the number of Workbooks open and if that number >1 then prompt the user 
> that
> the Workbook is already open – another Workbook will NOT open.
>
> I think this code is pretty close, but I can’t seem to count the number of
> Workbooks of a specific name.  If a user has other Workbooks open (other
> names), I don’t mind.  I just don’t want multiple versions of THIS 
> Workbook
> open.
>
> Thanks!
> Ryan--
>
> -- 
> Ryan---
> If this information was helpful, please indicate this by clicking ''Yes''.
>
>
> "ryguy7272" wrote:
>
>> I’m running the code from here:
>> http://support.microsoft.com/kb/291295/EN-US/
>>
>> That works great if I get into the Sub and hit F5!  Now, I’m trying to 
>> think
>> of a way I can modify this slightly so as to prompt a user that the WB is
>> already open if they try to open the file again.  The file will be saved 
>> back
>> to SharePoint and I want to make sure there is ONE SINGLE incidence of 
>> this
>> Excel WB open at a time (it’s fine if several other WBs are open).
>> Basically, I don’t want someone to open the file, do some work, walk 
>> away,
>> forget that it’s open, and try to open it again (from SharePoint).
>>
>> I think I’m going to need something like:
>> workbook_open (in thisworkbook)
>>
>> or
>>
>> workbook_activate (in thisworkbook)
>>
>> or
>>
>> AUTO_OPEN (in a module)
>>
>> I think, for this to work, I’m going to have to count the number of WBs 
>> open
>> (with this WB name, right).  So,
>>
>> If count <=1 Then workbook_open
>>    Else call TestFileOpened
>> End if
>>
>> Thanks!
>>
>> -- 
>> Ryan---
>> If this information was helpful, please indicate this by clicking 
>> ''Yes''. 

0
Rick
5/22/2010 4:52:25 PM
Reply:

Similar Artilces:

Pivot table and counting.
I have a pivot table that gives me the number of rides done by a van. I can have the pivot table return the number of rides, but what I need is the number vans that participated for a certain day. Example, it is possible to have 27 trips done by 18 vans, how can I count the number of vans and not addup the van number? Any help appreciated. Thank you, Joe Hi Joe We need a bit more information on how your source data table is set up. What information do you have in each column? -- Regards Roger Govier sacredarms <sacredarms@discussions.microsoft.com> wrote: > I have a pi...

Error opening Excel File
Hi, I'm currently having these days an error while opening an excel sheet. The sheet is shared among five users. When one tries to open it, a message indicates that the file is locked for editing by another user and after checking with the user, I understand that he has already closed the file but the latter remains locked. Can someone help? ...

How do I open power point 1997 attachments with outlook 2000?
I have an attachment sent with Microsoft Power Point 1997, and when I click it, I can't open it or save it. What and were do I get add-on to open it? Outlook doesn't open Powerpoint files, you need a PowerPoint viewer http://www.google.com/search?hl=en&source=hp&q=powerpoint+viewer&aq=0&oq=powerpoint+&aqi=g10 Oldfogy wrote: > I have an attachment sent with Microsoft Power Point 1997, and when I click > it, I can't open it or save it. What and were do I get add-on to open it? Are you not running Office Standard 2000? That comes with PowerPo...

mail will not open
when trying to open some...not all...emails I get the message "there was an error opening this message.....an error has occurred". This doesn't happen to all messages nor does it happen to all messages from the same sender. "velona" <velona@discussions.microsoft.com> wrote in message news:5CFA4037-506D-4362-B110-13A9915D825A@microsoft.com... > when trying to open some...not all...emails I get the message "there was > an > error opening this message.....an error has occurred". > This doesn't happen to all messages nor doe...

Opening a subfolder of %MyDocuments% using Shell explorer.exe
All, I was hoping that someone might be able to help me with a problem I am having opening Windows Explorer from a forms element. The following code works fine: Shell "explorer.exe / e, ""%MyDocuments%\"", vbNormalFocus" However when I try to access a subdirectory of %MyDocuments% using the following, Windows Explorer just opens the root directory of my documents and ignores a subdirectory request. Shell "explorer.exe / e, ""%MyDocuments%\SomeFolder"", vbNormalFocus" Additionally when Explorer opens under either of the above it o...

format question when open csv file
I receive a csv file that I open in excel. The first field in this file is a nine alphanumeric character. When I open the file excel formats that field in how it sees fit. Example 1157687E6 would display in scientific notation. I make a quick update to the file and resave as .csv file. The csv file resaves that field as the scientific notation, so the people I pass this file to have problems with it. Is there a way to open a csv file in excel where excel does not reformat the field or give me an option to not reformat. >> Is there a way to open a csv file in excel where exc...

Cycle Counting
Hello, I am looking for ideas on how to cycle count serial number tracked items without increasing workload. Our serialized items are barcoded but the problem is the are situated on the pallet incorrectly and the pallet is placed on a third level of a racking system. If we could just cycle count based on item count and not include the serial number it would make it easy. Is this possible in Great Plains. We are running Great Plains 8.0. Any suggestions are greatly aprreciated. Thank You, Brian Morris Database Administrator ...

How can macro open daily generated files?
Hi all, please can anybody help me with xls macro? I receive on daily basis txt files with name that contains actual date. Typically "Data_20100120.txt". I want to use macro, open files, modify data and add them to another workbook. That's no problem, but I don't know, how to daily open files with different name (date). I am not sure, how can I modify open sentence..... Workbooks.OpenText Filename:="C:\Data_20100120.txt" Many many thanks for your help. Valdemar Give this a try: myDate = Format(Date, "yyyymmdd") Workbooks.OpenTe...

Count
Hi, I would like to know how which formula I should use to count some codes & to ignore the duplicated codes, for example in column A: Code 1234 4321 4321 1234 1234 3214 Now, I want Excel to count & the result is 3 which are: 1234-4321-3214 Yes I can use PivotTable, but I would like to use a formula because I have to deal with this every day. I appreciate your support. -- MFS22 COUNTIF will do it. =COUNTIF(Rng,Rng), where the criterion is the same as the range: e.g =COUNTIF(A3:A8,A3:A8) HTH Peter "MFS" wrote: > Hi, > I wo...

How do I count the # of times a value reoccurs and plot it over ti
I wish to plot a number of errors made by each users and compare it over time. I have a column of user ID's and a column of dates. I want the chart to count the number of times each user ID appears and plot against it's corresponding date. However, each time I try to do this, the chart appears wrong. Any ideas? In article <9595B2A1-8282-4AB3-8511-A6776E108164@microsoft.com>, Stephen@discussions.microsoft.com says... > I wish to plot a number of errors made by each users and compare it over > time. I have a column of user ID's and a column of dates. I want t...

Openning Grouped Columns in Protected Sheets
We want to put such a protection to the sheet that, the protection will not prevent the user from openning grouped columns. This is possible only through a macro, for example: Sub a() With Sheet1 .EnableOutlining = True .Protect , True, True, True, True End With End Sub This setting does not get saved with the workbook, so it must be reset by running this macro each time the workbook is opened. -- Jim Rech Excel MVP ...

How do I open Excel 4.0 files in Office 2000?
I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now running Office 2000 professional and when I try to open these files I just get a security warning and the files won't open. Hi have you tried setting the security level to 'Medium' ('Tools - Macros - Protection') "Tom_Hunn" wrote: > I have Excel files with Macros, originally saved in Ver 4.0 (Office 97). Now > running Office 2000 professional and when I try to open these files I just > get a security warning and the files won't open. ...

Can not open
Know very little about this but I want to open a PUB file from a floppy from our churches pc on mine but it tells me I have the wrong version. Both PC,s have Office 2002 with PUB on them why can I not open it? Thanks Tom But, which versions of Publisher do you have on each? You need to verify the versions as well as let us know if either system is running Norton Antivirus. -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" with no warranties, and confers no rights. "Tom" <ttyso...

count number of characters in rich edit control
How can i count number of characters in rich edit control 2 If u mean controls of type Rich TextBox control, try RichControl1 RC1; CString Mes=RC.GetText(); int n=Mes.GetLength(); "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i count number of characters in rich edit control 2 EM_GETTEXTLENGTHEX, WM_GETTEXTLENGTH, WM_GETTEXT, EM_STREAM* "Rahul" <Rahul@discussions.microsoft.com> wrote in message news:7743023C-EA83-4ACA-AB67-F1AE7279F1EB@microsoft.com... > How can i co...

Line count
I am working with a program called Dictaphone. We use Word 2007 in this program. The line count on Dictaphone and the line count in Word 2007 are different even though the Dictaphone text is retrieved from Word 2007. Why would they be different? What is the difference between virutal line count and physical line count? ...

Can not open outlook 2007
Hi I have outlook 2007 and i went to check if i had some emails this morning, once i checked if i did, i closed outlook down. I came to check again tonight and outlook will not open. I have done a repair but it still doesn't work and i also uninstalled office 2007 and reinstalled office but still the same. Is there anything else i can do to get outlook 2007 to work?? Regards Martin Hickie I have the same problem and it also started yesterday. "Martin" wrote: > Hi > > I have outlook 2007 and i went to check if i had some emails this morn...

Author of Excel workbook appear in footer
Is there a way/code to have the author of the excel file appear automatically in the custom header/footer of an Excel 97 file. Maybe some type of code. Any help would be greatly appreciated. Thank you. Try this Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ActiveWindow.SelectedSheets wkSht.PageSetup.RightFooter = ActiveWorkbook.BuiltinDocumentProperties("Author") Next wkSht End Sub You must place it in the Thisworkbook module When you print it will add the name in the footer Right click on the Excel icon next to File in the men...

Counting sales
I have two different datasets. Each with a common identifier. I need a formula that will reference the identifiers in both datasets and count the number of sales a particular agent made. Example. look up this identifier in the other dataset and then give me the value that is 2 columns over from the identifier in the second dataset. See if this helps: http://contextures.com/xlFunctions02.html -- Biff Microsoft Excel MVP "Dave" <Dave@discussions.microsoft.com> wrote in message news:E7245B37-033F-400E-9CEE-9822BE7CB0F5@microsoft.com... >I have two...

Trying to open a blank document
Every time I open Word a particular document opens with it. I seem to be unable to open a blank document. I deleted the document that appears every time I open Word and still that same document is there when I open Word and /or a new blank page on Word. Any advice on how to correct this? See http://word.mvps.org/faqs/apperrors/blankdocnotblank.htm. -- Stefan Blom Microsoft Word MVP "Chairlady1" <Chairlady1@discussions.microsoft.com> wrote in message news:C7D6D85F-9ED4-4D23-944A-8A977F9F672A@microsoft.com... > Every time I open Word a particular docum...

Linking workbooks: links not live
Hi, all. I'd appreciate your comments on w I keep one detailed master matrix in Excel 2007 for some documentation, to track my workflow. I created a second workbook to give managers a less detailed view, by hiding columns, saving the new workbook with a new name, and linking the master and the quickview. For reasons I don't fully understand, I can't Enable This Content to automatically update links, and not all the links have been working. When I repeated the process, the links appeared in the quickview, but were not live. I checked, and there was a 1:1 match ...

Counting Cells with Conditional Formatting
Is there a way to look at a row of data that is either highlighted yellow or red and to count only the data that is highlighted yellow and give me that number? None of my data is numerical....just one letter data, i.e.: "S" or "U" I just want to count how many of the cells are highlighted yellow in a specific row, if that makes more sense. Here's a UDF (Put in a Standard module) and use it in cell A1, like =CountByColor(A8:H8,6) << where 6 (at present) = Yellow = change to =CountByColor(A8:H8,3) to get the reds HTH Function CountByColor(InRa...

COUNT ? need formula
Hi, I want to track the results of my teams sales performance. I record if it is a sale, no sale, cancelled etc in column M. I thought I'd be able to use the COUNT function/formula to be able to search for all the SALES in column M and place the result in a cell ( column O )but I can't work it out. Any ideas? Damian Hi! Try this: =COUNTIF(M1:M100,"sale") Biff "Diamond Jones" <kwanzaNOSPAM@optusnet.com.au> wrote in message news:43a0e897$0$17704$afc38c87@news.optusnet.com.au... > Hi, > > I want to track the results of my teams sales perf...

Outlook takes forever to open
Outlook opens half way, then I get an error message. Sometimes taking upto 10 minutes. ...

Count Function
I am creating a report for the number of pupils being collected at a certain collection time e.g. 14:00hr and 15:00hrs using the formula =Count([Child Last Name]) in the collection time footer However when i try to count the overall number of pupils in the footer using the same formula, 'Error' appears on screen? Thanks There are potentially a dozen footer sections in a report. Which one is giving you a problem? What is the name of the control? What is the exact formula? -- Duane Hookom Microsoft Access MVP "ELo" wrote: > I am creating a r...

Counting a count
How do I count a count?? This is what im trying to do: I have a database with a table named tblMain and has incorporated everything within that table but contains info from about 8 other tables. I have not doesigned this database and it appears to have a very poor structure...anyway There are three fields involved in this process...'Request' (either 'dispensation', 'waiver' or 'dispensation and waiver'), 'Department' (which consists of 7 other drop down menu options) and 'RequestStatus' which contains the status of each request by another...