Error Handle: File is already open.
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 |
Error Handle: File is already open.
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 |
Error Handle: File is already open.
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 |
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 |
All times are GMT +1. The time now is 04:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com