VBA code

[tljenkin]

Hi All,

I am trying to compile a simple code to check ...

1) if a file is open,

2) if it is, run the rest of the code,

3) if it is not, open the file and run the code

4) if nothing is selected at 3), show an error message to that effect and close sub

Please see code below and help me figure out whats wrong

Thank you

Function AlreadyOpen(TargetFile As String) As Boolean
Dim wkb As Workbook
On Error Resume Next
Set wkb = Workbooks(TargetFile)
AlreadyOpen = Not wkb Is Nothing
Set wkb = Nothing
End Function


Sub CopyBudToMRP()
Dim TargetFile As String
TargetFile = Application.GetOpenFilename _
(Title:="Please choose Binary Budget file to open!", _
FileFilter:="Excel Files *.xlsb(*.xlsb),")
If AlreadyOpen(TargetFile) Then
Else
If TargetFile = False Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Workbooks.Open Filename:=TargetFile
End If
End If
End Sub

 

[Jeffrey Mahoney]

Please try these functions and a little rearranging of your code. The IsWBOpen has worked for me for a long time. It needs just the file name and not the path, that's why I included the GetFileName Function.

Jeff

'Check to see if current user has file open (This Machine)
Function IsWBOpen(ByRef szBookName As String) As Boolean
' Rob Bovey
    On Error Resume Next
    IsWBOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function


Function GetFileName(PathFile As String) As String

  Dim X As Long
  Dim A As String
  Dim BS As Integer
  
  A = PathFile
  For X = Len(A) To 1 Step -1
    If Mid(A, X, 1) = "\" Then
      BS = X + 1
      Exit For
    End If
  Next X
  If BS > 0 Then
    GetFileName = Mid(A, BS)
  Else
    GetFileName = A
  End If
End Function




Sub CopyBudToMRP()
  Dim v As Variant
  Dim TargetFile As String
  Dim PathFile As String
  
  v = Application.GetOpenFilename(Title:="Please choose Binary Budget file to open!", _
    FileFilter:="Excel Files *.xlsb(*.xlsb),")
  If v = False Then
    MsgBox "No file selected.", vbExclamation, "Sorry!"
    Exit Sub
  Else
    TargetFile = GetFileName(CStr(v))
  End If
  
  If IsWBOpen(TargetFile) Then
    'It is open
  Else
    PathFile = v
    Workbooks.Open Filename:=PathFile
  End If
End Sub

 

[tljenkin]

Many many thanks