Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Personal.xls error - "file Is already open"

I have verified that ther is only one version of Personal.xls anywhere on the
C: drive and that it is in the xlstart folder. The macros are assigned to a
custom toolbar. I can run the macros from the menu just fine. But when I try
to run from the tool bar buttons I get the message that the "file is already
open". I'm not sure this is a Personal.xls problem or a toolbar problem.

Hopefully someone will have some ideas of how to fix this. Thanks, Irene


Post your answer or comment

comments powered by Disqus
Hi everyone - - - I thought that I had this working completely, but the test to see if the "file is already open" isn't working.

Something is wrong with my example.

I have marked in the code where the problem is. Everything else works.
It's marked as such:

HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
Can someone please tell me what I'm doing wrong ?

Sub NewExcelWithWorkbook()
    Dim oXL    As Object    'This is needed to open a new instance of Excel.
    'Without it, the file is only opened as a new Window
    Dim OpenFileName    '<-this isn't used
    Dim testFileFind As String
    Dim oWB    As Workbook
    Dim cl     As Range
    'This reads the cell 1 column to the Left so the path & file name can be read
    Set cl = ActiveCell.Offset(0, -1)


    'The following tests for a blank cell and ends processing
    'It is needed because dir() function will not work with a blank.
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path and File name."
        End
    End If

    'The following tests for the existance of the file
    testFileFind = Dir(cl)
    
    'If the file is not found there will be nothing in the variable and processing ends.
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
        End
    End If




'HERE IS MY PROBLEM - - - - - - - - - - - - - - - - - - - - - - - - - - -
        'THIS TESTS TO SEE IF THE FILE IS ALREADY OPEN OR NOT
    If FileAlreadyOpen("cl") = True Then
        MsgBox "File is already open"
    End
    Else
    End If






    'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
    Set oXL = CreateObject("Excel.Application")

    'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
    oXL.Visible = True

    Set oWB = oXL.Workbooks.Open(cl)

End Sub
Please take note: The path and file name constantly change.

The following Function IS CALLED to check if the File Is Open - or Not

'It all works if the Path & File Name are hard coded. I have to have it working with changing Paths & File names.

Function FileAlreadyOpen(FullFileName As String) As Boolean
' returns True if FullFileName is currently in use by another process
' example: If FileAlreadyOpen("C:FolderNameFileName.xls") Then...
Dim f As Integer
    f = FreeFile
    On Error Resume Next
    Open FullFileName For Binary Access Read Write Lock Read Write As #f
    Close #f
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        FileAlreadyOpen = True
        Err.Clear
        'MsgBox "Error #" & Str(Err.Number) & " - " & Err.Description
    Else
        FileAlreadyOpen = False
    End If
    On Error GoTo 0
End Function


I have the following Code to Check if a File is already Open.... and perform a procedure

Basically if file is already open then perform procedure... If file is not already open open it and then run the procedure...

For some reason this part:

If IsFileOpen(sPath) Then

MsgBox "File is Open"
GoTo ResumeProcess: Does not work shen the file is already open... I get run time error 1004 stating the file is already open...

I was hoping I would not receive any errors if file is already open and then just resume normal activity...

Code:
Private Sub CommandButton2_Click()

Dim WbBook1 As Worksheet
Dim WbBook2 As Worksheet
Dim sPath As String
sPath = "Nymas01financeBudgetBudget 2008ExportsPLATFORM.xls"

If IsFileOpen(sPath) Then

MsgBox "File is Open"
GoTo ResumeProcess:

Else



Workbooks.Open (sPath)

End If


ResumeProcess:

Set WbBook1 = Workbooks("PLATFORM.xls").Worksheets("PLATFORM")
Set WbBook2 = Workbooks("License Fees_2008.xls").Worksheets("PLATFORM not in FRX")


WbBook2.Range("A2:A" & Rows.Count).Formula = "=IF(ISBLANK(GROUPLIST!A3)," & Chr(34) & Chr(34) &
",IF(COUNTIF(PLATFORM.xls!$B:$B,GROUPLIST!A3)>0," & Chr(34) & Chr(34) & ",GROUPLIST!A3))"
'Dim eSheet As Worksheet
'Set eSheet = Sheets("PLATFORM not in FRX")

'x = eSheet.Rows.Count

'eSheet.Range("A2").Formula = "=IF(ISBLANK(GROUPLIST!A3),"",IF(COUNTIF(PLATFORM.xls!$B:$B,GROUPLIST!A3)>0,"",GROUPLIST!A3))"
'eSheet.Range("A2:A" & x).FillDown

Workbooks("PLATFORM.xls").Close
End Sub
Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function

Any advice in this matter would be amazing!

Thanks in advance,

This routine is used to open another Excel File in a seperate instance of Excel.
I do not want the user to beable to click the macro button a second time and then open the workbook a second time.

Every thing works fine until I add the code as shown below.

'ADDED THIS - - - - - - - - - - - - - - TO TEST IF FILE ALREADY OPEN
'testFileFind is a variable with name of file
    'This line of code Goes to below FUNCTION - Should NOT
The "testFileFind" in this line of code is blank after going to Function. Earlier in the routine the "testFileFind" still holds the file name.
I think this is where the problem is.
'TO HERE - - - - - - - - - - - - - -

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
Set oXL = CreateObject("Excel.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
 oXL.Visible = True
 
 Set oWB = oXL.Workbooks.Open("c:extrafilesmiscexcelLinkMenu.xls")

'ADDED THIS - - - - - - - - - - - - - -
End
Else
    MsgBox "File " & "testfilefind" & "is already open."
End If
'TO HERE - - - - - - - - - - - - - - -
When the requested file is already open, the routine should stop here with a message stating that the file is already
open.

If the file is not open, the file should be opened and then the routine should stop.

In both instances, the routine goes to my Function below and comes up with an error.

I don't understand why it goes to this function.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

   On Error Resume Next
   iFilenum = FreeFile()
   Open FileName For Input Lock Read As #iFilenum
   Close iFilenum
   iErr = Err
   On Error GoTo 0

   Select Case iErr
       Case 0:    IsFileOpen = False
       Case 70:   IsFileOpen = True
       'ADD THIS
       Case Else: Error iErr
   End Select

End Function
I sure hope that someone can understand what I am talking about.

I'm having odd results using the code cited in this article,
http://support.microsoft.com?kbid=184982, to determine is an Excel file is
already open.

The problem I'm having is when an excel file is marked read-only. (Everthing
works as expected otherwise.) In my testing, when the Excel file is marked as
read only, the code below ALWAYS (wether the file is open or not) returns
Err.Number=75, Err.Description=Path/File access error.

Unfortunately, I can't assume that the file is not read-only so I need to
figure this problem out. Any idea how I can determine if a read-only file is
already open or not?

I tried removing the "Write"s from the Open statement, but that doesn't help
in this situation. What need to know is if the file is open before I attempt
to access it, so that when I'm done I can close or open it depending on the
status before I started my processing.

Thanks for any feedback...

FYI, Here's the code:

Public Sub x()
Debug.print FileLocked("C:", "some.xls")
End sub

Function FileLocked(FilePath As String, FileName As String) As Boolean
On Error Resume Next
' Adapted from http://support.microsoft.com/?kbid=184982
' If the file is already opened by another process,
' and the specified type of access is not allowed,
' the Open operation fails and an error occurs.
Open FilePath & FileName For Binary Access Read Write Lock Read Write As
#1
Close #1

' If an error occurs, the document is currently open.
If Err.Number <> 0 Then
' Display the error number and description.
MsgBox "Error #" & str(Err.Number) & " - " & Err.Description
FileLocked = True
Err.Clear
End If
End Function

When I open an Excel file from an Outlook email, I don't have access to my
macros that are located in PERSONAL.xls. If Excel is already open when I
open the Excel attachment, then my macros ARE available. Anyone have any
ideas?

Hi,

I'm using the following code to run a task:

Sub Send_info()

Dim wbData As Workbook
Dim wbArchive As Workbook
Dim WS As Worksheet

Set wbData = ActiveWorkbook
Set wbArchive = Workbooks.Open("C:abc.xls")

For Each WS In wbData.Worksheets
    WS.Range("A:G").Copy wbArchive.Sheets(WS.Name).Range("A1")
Next WS

End Sub
Is there a way I can add to this so that if the file is already open it will just paste the data and ONLY if it is closed it will open the file?

Thanks in advance. :-)

Hi all,

I'm looking for a bit of help with VBA which I'm relatively new to. What I know I've taught myself from helpful forums such as this. I have the following section of code


	VB:
	
 
If IsNumeric(ActiveCell.Value) = True And ActiveCell.Value > 100000 Then 
    SetCurrentDirectoryA prometheus & ActiveCell.Value 
    Application.FindFile 
    MsgBox "Please select a valid job number", vbExclamation + vbOKOnly, "Open Job" 
    Exit Sub 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which looks for a value in the selected cell and, if that value meets the citeria, presents the user with an open file dialogue. This works beautifully until the user choses to open an already open file. At this point Excel says ' this file is already open do you wish to open another copy'. Clicking yes opens a second copy and the macro proceeds but clicking no stops the macro in its tracks. What I would like is to change this code so that after clicking no the macro ends and presents a message box. Even better would be (though more complicated I suspect!) for the macro to test if the file is open first and then either open it and continue or continue using the already open version. Any hints?!

Thanks in advance for any help anyone can offer.

Archie

Hi all

I have a proceedure that opens a certain file the problem is that when the
proceedure is run again it reopens the file where it loses some information,
is there a way to check to see if that file is already open?

Thanks in Advance

Jason

The codes here allow me to source and open a file.

I want to add in a condition to check if the file that i choose is already open, then i do not want to reopening it. I just want to point the file name to the existing file.
How can i add in the condition to do the checking? Any help is greatly appreciated!

Private Declare Function SetCurrentDirectoryA _
Lib "kernel32" (ByVal lpPathName As String) As Long

Public Function GetOpenFilenameFrom(Optional sDirDefault As String) As Variant

Dim sDirCurrent As String
Dim lError As Long

sDirCurrent = CurDir

If sDirDefault = vbNullString Then

sDirDefault = CurDir
Else

If Len(Dir(sDirDefault, vbDirectory)) = 0 Then
sDirDefault = sDirCurrent
End If
End If

If Not Left(sDirDefault, 2) = "" Then

ChDrive Left(sDirDefault, 1)
ChDir (sDirDefault)
Else

lError = SetCurrentDirectoryA(sDirDefault)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error accessing the network file path"
ChDir (sDirDefault)
End If

GetOpenFilenameFrom = Application.GetOpenFilename _
("Excel Files (*.xl*), *.xl*,All Files (*.*),*.*")

If Not Left(sDirCurrent, 2) = "" Then

ChDrive Left(sDirCurrent, 1)
ChDir (sDirCurrent)
Else

lError = SetCurrentDirectoryA(sDirCurrent)
If lError = 0 Then _
MsgBox "Sorry, I encountered an error resetting the network file path"
ChDir (sDirCurrent)
End If

End Function

Public Function FileFolderExists(strFullPath As String) As Boolean

On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True

EarlyExit:
On Error GoTo 0

End Function

Public Sub TestFolderExistence()

If FileFolderExists("") Then
MsgBox "Folder exists!"
Else
MsgBox "Folder does not exist!"
End If

End Sub

Is there an If then statement that will test to see if a workbook is already open prior to issuing the workbooks.open statement.

If test.xls is not already open then
workbooks.open filename:= "C:test.xls"
end if

Thanks

Hi,

I want a macro to check is a file, which is called upon, is already
open and if so skips the opening line of the code and proceeds with
the rest, otherwise proceed with opening the file.

Currently Excel asks me if I want to reopen the file and unsaved data
will be lost.

Cheers,

Harold

I use the following code at the begining of each macro that opens an Excel File. What I am looking for is to some how incorporate a procedure like this to check to see if the email that I want to open is already open or not.

NEXT, look at the 2nd set of code.

Dim oWB As Object
Dim PWB As String
Dim FSS As String


PWB = ("c:MyMenuFilesMyMenuiJngo.xls")
FSS = "MyMenuiJango.xls"

    'First I need to check to see if "MyMenuiJango.xls" is open or not
If FileAlreadyOpen("c:MyMenuFilesMyMenuiJango.xls") = True Then
    ActivateWorkbook (FSS)
    Application.Quit
    
End
Else


The following code is currently what I use to open my 3 email accounts. This sample is for only 1, which is my hotmail account.

Next, see below this code.

Sub OpenMyeMailNo1()
Dim oShell
Dim sUrl As String
 sUrl = "http://" & Sheets("Setup").Cells(8, 5).Value
    Set oShell = CreateObject("Wscript.Shell")
    oShell.Run (sUrl)
End Sub

This eMail code gets the name of the eMail and .com . . . EX: Hotmail.com from a sheet in the workbook named Setup from cell E8.

I know how to check to see if a file is already open or not, (don't want it opened 2X). BUT . . . How do I check to see if Hotmail or whatever email of the three I'm referring to is open or not.

Hi,
I searched and there are a ton of links on how to open access files using VBA, but I couln't find (or maybe i wasn;t looking in the right place) code on how to bring the access file in foreground if it's already opened. Below is the code I use with no luck.
    'open the access tool if not already open
    If Not IsFileOpen(strPath) Then         'IsFileOpen checks and returns true if file is already open depending on strPath
(file path) 
        
        'close any open access instances
        Call CloseAllAccess
    
        Set oApp = CreateObject("Access.Application")
        oApp.AutomationSecurity = 1
        oApp.OpenCurrentDatabase strPath    
        oApp.Visible = True
        oApp.DoCmd.OpenTable "tbl_Material"
        oApp.DoCmd.OpenForm "frmCPanel"
        Set oApp = Nothing
    Else
        Set oApp = CreateObject("Access.Application")
        oApp.AutomationSecurity = 1
        oApp.OpenCurrentDatabase strPath                    'i am pretty sure this line shouldn;t be here, but i can;t figure
what to put instead
        oApp.DoCmd.OpenTable "tbl_Material"
        oApp.DoCmd.OpenForm "frmCPanel"
    End If
Thanks

Every time I start XL, I get the error message:

A document with the name 'PERSONAL.XLSB' is already open. You cannot open two documents with the same name, even if the documents are in different folders. To open the second document, either close the document that is already open, or rename one of the documents. I'm running Excel 2007 SP2 on Vista. ( Please don't post replies that involve changing either of those, no matter how passionate you may be about it! )

I have found that the two files are located in:
C:UsersCritchoAppDataRoamingMicrosoftExcelXLSTART
andC:UsersCritchoAppDataLocalMicrosoftOfficexlStart
In the past, I have deleted one of the files, and it has been regenerated without any action from me to request such.

I have also renamed one of the files to C:UsersCritchoAppDataRoamingMicrosoftExcelXLSTARTPERSONAL2.XLSB, but that didn't help.

So now I have 3 macro workbooks and still get the error each time I start XL. While it's not a critical error, it's an annoyance; but I'm more concerned about where all my macros are stored.

So.... how do I:
Stop XL from regenerating these filesmore importantly - Merge all 3 versions back into one workbook so I don't loose any of the macros I have written (few hundred in there I'm guessing)
I only ever use the one login to this laptop, it's not like I'm joining a domain at work then using a homegroup at home or anything like that, I'm wondering what makes it pick between the local and roaming AppData sets.

Thanks for your help!
Cheers,
Critcho.

I currently have a macro which exports data to "Funds.xls", which is shared by several users. It tests if "Funds" is opened by another user, and if not, exports the data.

I use this macro to export data from several different spreadsheets to "Funds". Currently, I have to export data from one sheet and then save and close "Funds" before exporting another. If I try to export data from multiple sheets without saving and closing "Funds", I get the following message: "Funds.xls is already open. Reopening will cause any changes you made to be discarded."

I am trying to add a provision to my macro that will avoid reopening the sheet if I am the person in "Funds" and "Funds" is not in Read-Only, as follows:


	VB:
	
 ExportNAV_CFFtest() 
    rownum = Range("L60000").End(xlUp).Row 
    Range(Cells(rownum - 3, 12), Cells(rownum, 12)).Select 
    Application.CutCopyMode = False 
    Selection.Copy 
    Dim FundsSheet As Workbook 
    Set FundsSheet = Workbooks.Open(Filename:="J:AdministrationAccountingFUNDSFUNDS-DailiesCurrentFunds.xls") 
    If FundsSheet Is Nothing Then 
        Workbooks.Open FundsSheet 
        Range("G8").Select 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=True 
         
        [COLOR=lime]ElseIf FundsSheet Is Not Nothing[/COLOR] And FundsSheet.ReadOnly = False Then 
            Range("G8").Select 
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
            :=False, Transpose:=True 
        ElseIf FundsSheet.ReadOnly Then 
            MsgBox "Funds Sheet in Read-Only - Please Try Again" 
            Err.Clear 
        End If 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I am breaking on the highlighted code. The error is "Compile error: Invalid use of object".

Can you tell me what I am doing wrong?

EXCEL:Can't open any file without error saying it's ALREADY open???

It's a gigantic alert box when you opened an existing XLS file telling
you the file is ALREADY open and asks for ok, cancel etc.

OFFICE 1997 is the version.

I have created file called StartExcelApp.vbs
That is coded:

Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.open "MySampleAll.xls"
xlapp.ActiveWorkbook.RunAutoMacros 1

Is there a way to test first if MySampleAll.xls is already open so that I am
not starting a second copy. When the MySampleAll.xls is opened it runs an
auto macro that updates some data and then immediately “saves” the workbook.
The macro errors because the second iteration is now set to read only? I only
want 1 copy open so if this VBS is runs when MySampleAll.xls is already open,
I want it to end without opening it…

From Excel VBA, if I try to open a Word file that is already open, I get a message asking me if I want to open a Read-only version, create a copy or receive notification when it is free.

In this instance I just want to use the open file, so I cancel. This is returning a run-time error "4198" which my "On Error Resume Next" statement is NOT catching ...

The obvious question (and I'd like to know the answer so that I can hopefully remember if or when it happens in the future) is why am I not catching the Error ?

However, is there a way that I can switch to the already open file rather than attempt to open it again ?

My code is as follows :


	VB:
	
FilePath = FileDir & MyChoice 
On Error Resume Next 
Set WordApp = CreateObject("Word.Application") 
Set WordDoc = WordApp.Documents.Open(Filename:=FilePath) 
WordApp.Visible = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi Kaak,
I've tried your code (bottom of this message) and I got a "Compile
error: Else Without If" error message with the highlighter stopping at
[Else: MsgBox FileName & " is not open"].

If the file is already opened, how do I activate (go to) it, copy and
paste to the current worksheet? If the file isn't already opened, then
my original code would do the trick. I need a workaround for when the
target file is already opened. Otherwise, I would have to close it
first before triggering my original macro.

Thanks in advance again,
Ricky

Sub CheckForFile()

Dim FileName As String
Dim x As Workbook

FileName = "budget.XLS"

On Error Resume Next
Set x = Workbooks(FileName)

If Err = 0 Then MsgBox FileName & " is open"
Else: MsgBox FileName & " is not open"
End If

On Error GoTo 0

End Sub

*** Sent via Developersdex http://www.developersdex.com ***

Hi,
I have code which will check if the file "C of S.xls" is open. Ideally, if it is open, the sub will exit but if it it closed, the sub will open "C of S.xls".
When the file is already open, everything works fine. However, when the file is closed, I get the "subscript out of range, Run time error 9" debug error at the point in the routine where it looks for the "C of S.xls" file.
How can I avoid this happening?
I have attached the file.
Thanks.

Jockster

Hi everyone. I have the below code that opens all files within a specific
folder. But if one of the files is already open (very likely to happen) I
get an error. Can I add some sort of if stmt that will ignore the file if
it is already open, so the code won't error out? Thank you in advance!
Steph

Dim sFolder As String
Dim wb As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "ServerFolder1Folder2"
.SearchSubFolders = False
.filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(filename:=.FoundFiles(i))
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

OK, I'm throwing in the towel. Two days is more than enough time to spend on this before asking for help.

This subroutine, copied from another website and embellished somewhat by me, is designed to allow the user to choose the file to open via the open file dialog box. That part works great so long as the file is not already open. If the file is already open though, I need to be able to activate or otherwise reference it to copy a sheet from it to another already open (the one containing this code) workbook. It bombs under the 'Open File comment in the code below. As it is now written, it throws a 'subscript out of range' error when it executes the .Activate code. I think this issue arises because the string variable contains the full path/filename instead of the workbook name only. I also used the FileName variable as Workbook to no avail. That creates a whole new set of problems. Any helpful hints or pearls of wisdom are greatly appreciated.


	VB:
	
 OpenSingleFile() 
     'Open an Excel workbook
     
     
    Dim Filter As String, Title As String, FileName As String 
    Dim FilterIndex As Integer 
     
     
     ' File filters
    Filter = "Excel Files(*.xls,*xlxs),*.xls,*xlxs" 
     
     
     ' Default Filter to *.*
    FilterIndex = 3 
     
     
     ' Set Dialog Caption
    Title = "Select a File to Open" 
     
    FileName = Application.GetOpenFilename(Filter, FilterIndex, Title 
     
     ' Exit on Cancel
    If FileName = "False" Then 
        MsgBox "No file was selected.", , "Select File" 
        Exit Sub 
    End If 
     
     
     ' Open File
    If Not IsFileOpen(FileName) Then 
        Workbooks.Open FileName 
         '*** It will be the active workbook here b/c it was opened
    Else 
        Message = "The file " & FileName & " is already open." 
        vbResponse = MsgBox(Message, vbOKCancel, "Select file") 
         '*** I need to activate it here if already open
        Workbooks(FileName).Activate ' doesn't work - subscript out or range error
        MsgBox "The active workbook is " & ActiveWorkbook.Name 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
End Sub

Hi,

I need help with the code to check if a workbook (called standings-2010.xls) is already open.

Some code in another workbook makes an alteration to a sheet in standings-2010.xls. The problem is that standings-2010.xls may or may not be already open. I have included code to open it, but, if it is already open then I get the alert about it already being open and clicking 'do not reopen' on that produces an error in the code. Hence I want to check if it is open already then, if it is, skip the opening and just run the code.


	VB:
	
 
[COLOR=red][COLOR=black]if [/COLOR]standings-2010.xls Is Not Open [COLOR=black]then[/COLOR]

I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours, Any thoughts?

Thanks, Ted


	VB:
	
 Workbook 
 'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls") 
If wBook Is Nothing Then 'Not open
     'Do nothing
Else 'It is open
    i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and 
    cannot be updated.  Please try again when the workbook Is available", _ 
    vbCritical, "Committed Request Edit") 
    Goto EndUpdate 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.