Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- ' Reuses a workbook if it's already open, else opens it.
- ' This function prevents "Automation Errors" that happened to me
- ' in Excel 2017 when I used "On Error Resume Next" instead of
- ' "proper" error handling.
- '
- ' WARNING: translated and adapted without testing. May need rounding out.
- Function GetWorkbook(ByVal sPath As String, ByVal sFileName As String) as Workbook
- Dim wb as Workbook
- Dim sFullName As String
- Dim erro As Boolean
- sFullName = sPath & sFileName ' sPath has trailing \
- On Error GoTo erro:
- Set wb = Workbooks(sFileName)
- On Error GoTo 0
- If Not erro Then
- ' File is already open. Let's check if it's the right one.
- If wb.FullName <> sFullName Then
- ' Uh oh, it doesn't match the desired path.
- Call MsgBox("Open workbook has wrong path.", vbCritical)
- End
- End If
- Else
- ' We may have gotten here by an error, so let's reset our error flag.
- erro = False
- On Error GoTo erro
- Set wb = Workbooks.Open(sFullName)
- On Error GoTo 0
- If erro Then
- ' Couldn't open. Probably 404.
- Call MsgBox("Couldn't open workbook. Check the path.", vbCritical)
- End
- End If
- End If
- Set GetWorkbook = wb
- Exit Function
- erro:
- erro = True
- Resume Next
- End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement