Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Excel file get error with file names that have spaces in the | Setting up and Configuration of Excel | |||
EXCEL:Can't open any file without error saying it's ALREADY open??? | Excel Worksheet Functions | |||
How to handle error 8007000e Memory Error | Excel Programming | |||
Handle password for open workbook? | Excel Programming | |||
open file file then error message | Excel Discussion (Misc queries) |