Hello,
In the code below, I first check to see if a certain workbook is open from a separate workbook. If it is, I would like to save and close it.
The workbook is open, but when it attempts to save and close it (in line Workbooks("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm").Close), I recieve Run Time Error '9': Subscript out of range.
Any ideas on how I can solve this?
Thank you in advance
Sub Sample()
Dim Ret
Ret = IsWorkBookOpen("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm")
If Ret = True Then
Workbooks("S:\Public\PMG\Fixed Income\FI-AA\Attribution\AttributionUS2013v3.xlsm").Close True
End If
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
'Case 75: IsWorkBookOpen = True 'this is read only open
Case Else: Error ErrNo
End Select
End Function
Bookmarks