Error Handle: File is already open.
Thanks, I found a way to use err number
"Jim Thomlinson" wrote:
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
--
HTH...
Jim Thomlinson
"Jim Thomlinson" wrote:
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
--
HTH...
Jim Thomlinson
"J@Y" wrote:
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
|