Vba Code: Open Workbook, if Open Do nothing

[DougStroud]

I have added a line of code to open a workbook. But I need to tell it that if it already open, then do nothing

If Workbooks("Test.xls").???????????
Workbooks.Open "C:\Test.xls"


thanks,

ds

 

[gaj104]

Hi,

I use the following functions:

Sub OpenFile()

Dim fName As String

fName = "C:\Test.xls"

If IsFileOpen(fName) = False Then Workbooks.Open (fName)

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. Otherwise, a run-time error will occur because there is
   ' some other problem accessing the file.

Function IsFileOpen(filename As String) As Boolean

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

Hope it helps

 

[Richard Schollar]

Hi Doug

The following is a simpler alternative:

Dim iStatus As Long
Err.Clear
On Error Resume Next
Set wb = Workbooks("myWorkbook")
iStatus = Err
On Error Goto 0
If iStatus Then 'workbook isn't open
   Workbooks.Open "myWorkbookName&Path"
Else
   'workbook is open
   wb.Activate
End If

 

[DougStroud]

Thanks guys,
Gaj104, I will go back and work through your code in a bit- thank you.

Richard, I used your code- it was easy to implement and worked right off. I have one question relating to the purpose.
When you add in Else If to activate the sheet, why?
I commented it out and it works fine for my needs. But wondering what you were looking for by adding it in there?

thank you,

Doug

 

[Norie]

Doug

You do realise you didn't mention what you wanted to do if the workbook was open?

What Richard posted is probably just a small example.

 

[Richard Schollar]

Doug

That line was just for an example of coding to apply to the already open workbook...

 

[DougStroud]

Richard,
coding to apply to the already open workbook...

Ok, that makes good sense.


Thnk you

 

[DougStroud]

Ran into a small problem, don't know what is causing it:

I am getting an error: Object Variable not set or With Block not set on the following line:

If iStatus Then     'workbook isn't open

Sub OpenUpload()
Dim wbU As Workbook
Dim sPath As String
Dim iStatus As Error

sPath = "H:\8-27-06\My Documents\Excel\EC Excel Files\ecboardco uploaded files\"
 Err.Clear
     On Error Resume Next
     Set wbU = Workbooks("Complete_Upload_File.xls")
     iStatus = Err
     On Error GoTo 0
     If iStatus Then     'workbook isn't open
          Workbooks.Open sPath & wbU
          'Else: wbU.Activate
     End If
End Sub

 

[DougStroud]

Solved,
One of my variables was declared incorrectly.