Advertisement
andrechalella

VBA GetWorkbook for open or not open files w/ error handling

Dec 17th, 2017
1,264
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ' Reuses a workbook if it's already open, else opens it.
  2. ' This function prevents "Automation Errors" that happened to me
  3. ' in Excel 2017 when I used "On Error Resume Next" instead of
  4. ' "proper" error handling.
  5. '
  6. ' WARNING: translated and adapted without testing. May need rounding out.
  7. Function GetWorkbook(ByVal sPath As String, ByVal sFileName As String) as Workbook
  8. Dim wb as Workbook
  9. Dim sFullName As String
  10. Dim erro As Boolean
  11.  
  12.     sFullName = sPath & sFileName ' sPath has trailing \
  13.    
  14.     On Error GoTo erro:
  15.     Set wb = Workbooks(sFileName)
  16.     On Error GoTo 0
  17.        
  18.     If Not erro Then
  19.         ' File is already open. Let's check if it's the right one.
  20.        If wb.FullName <> sFullName Then
  21.             ' Uh oh, it doesn't match the desired path.
  22.            Call MsgBox("Open workbook has wrong path.", vbCritical)
  23.             End
  24.         End If
  25.     Else
  26.         ' We may have gotten here by an error, so let's reset our error flag.
  27.        erro = False
  28.         On Error GoTo erro
  29.         Set wb = Workbooks.Open(sFullName)
  30.         On Error GoTo 0
  31.        
  32.         If erro Then
  33.             ' Couldn't open. Probably 404.
  34.            Call MsgBox("Couldn't open workbook. Check the path.", vbCritical)
  35.             End
  36.         End If
  37.     End If
  38.    
  39.     Set GetWorkbook = wb
  40.    
  41.     Exit Function
  42.  
  43. erro:
  44.     erro = True
  45.     Resume Next
  46. End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement