Error Handle: File is already open.

[J@Y]

I have the following script for adding a workbook. What error handling can I
add so that it tells the user the file name they try to create already exists
and is open. Then exits the program. I would liek to use a more specific
method than "On error goto ErrHandle"

Set ReportBook = Workbooks.Add()
Set ReportPage = Worksheets.Add
ReportPage.Name = "Report"


Do
fName = Application.GetSaveAsFilename(Title:="Specify Report Name")
Loop Until fName <> False
If UCase(Right(fName, 4)) <> ".XLS" Then fName = fName + "xls"

ReportBook.SaveAs Filename:=fName

 

[Jim Thomlinson]

You could do something like this...

Dim ReportBook As Workbook
Dim ReportPage As Worksheet
Dim fname As String

Set ReportBook = Workbooks.Add()
Set ReportPage = Worksheets.Add
ReportPage.Name = "Report"


Do
fname = Application.GetSaveAsFilename(Title:="Specify Report Name")
Loop Until fname <> "False" 'note False is a string
If UCase(Right(fname, 4)) <> ".XLS" Then fname = fname + "xls"

If Len(Dir(fname)) > 0 Then
MsgBox fname & " already exists. File Not Saved"
Else
ReportBook.SaveAs Filename:=fname
End If

 

[Jim Thomlinson]

Sorry. I did not read your question correctly. You wanted to know if the file
was open. Give this a look see...

Dim ReportBook As Workbook
Dim ReportPage As Worksheet
Dim fname As String
Dim wbk As Workbook

Set ReportBook = Workbooks.Add()
Set ReportPage = Worksheets.Add
ReportPage.Name = "Report"


Do
fname = Application.GetSaveAsFilename(Title:="Specify Report Name")
Loop Until fname <> "False"
If UCase(Right(fname, 4)) <> ".XLS" Then fname = fname + "xls"

If Len(Dir(fname)) > 0 Then
MsgBox fname & " already exists."
On Error Resume Next
Set wbk = Workbooks(Dir(fname))
On Error GoTo 0
If wbk Is Nothing Then
MsgBox fname & " is not open. File saved."
ReportBook.SaveAs Filename:=fname
Else
MsgBox fname & " is open. File Not Saved."
End If
Else
ReportBook.SaveAs Filename:=fname
End If

 

[J@Y]

Thanks, I found a way to use err number

Sorry. I did not read your question correctly. You wanted to know if the file
was open. Give this a look see...

Dim ReportBook As Workbook
Dim ReportPage As Worksheet
Dim fname As String
Dim wbk As Workbook

Set ReportBook = Workbooks.Add()
Set ReportPage = Worksheets.Add
ReportPage.Name = "Report"


Do
fname = Application.GetSaveAsFilename(Title:="Specify Report Name")
Loop Until fname <> "False"
If UCase(Right(fname, 4)) <> ".XLS" Then fname = fname + "xls"

If Len(Dir(fname)) > 0 Then
MsgBox fname & " already exists."
On Error Resume Next
Set wbk = Workbooks(Dir(fname))
On Error GoTo 0
If wbk Is Nothing Then
MsgBox fname & " is not open. File saved."
ReportBook.SaveAs Filename:=fname
Else
MsgBox fname & " is open. File Not Saved."
End If
Else
ReportBook.SaveAs Filename:=fname
End If