+ Reply to Thread
Results 1 to 4 of 4

error handling when creating a file

  1. #1
    Registered User
    Join Date
    06-08-2005
    Posts
    67

    error handling when creating a file

    I want to create a file, but it may already exist. If it does, I get the message asking if I want to overlay the file. I do not want that message, rather I would like to just send a msgbox. My code is:

    Sub test()
    Dim tear As Range
    Set tyear = Range("a1")

    File_name = tyear & ".xls"
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:= _
    File_name, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    On Error GoTo no_file
    ActiveWorkbook.Close
    GoTo endit
    no_file:
    MsgBox (File_name & " already exists.")
    endit:
    End Sub

  2. #2
    Forum Contributor
    Join Date
    12-12-2005
    Posts
    667
    Click References, Window Scrip Runtime and add code similar to this:
    Sub test()
    Dim fso As FileSystemObject
    Dim tear As Range
    Set tyear = Range("a1")
    File_name = tyear & ".xls"
    iIf fso.FileExists(ThenFile_name) then
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:= _
    File_name, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    On Error GoTo no_file
    ActiveWorkbook.Close
    else
    MsgBox (File_name & " already exists.")
    End If
    End Sub
    Best regards,

    Ray

  3. #3
    Registered User
    Join Date
    06-08-2005
    Posts
    67
    sorry, my original posting of the code might have been mislieading. This is more correct with the FileSystemObject reccomendation. This returns a compile error on the Dim fso statement. Probably because it is not Set to anything.

    Sub test()
    Dim fso As FileSystemObject
    Dim tyear As Range
    Set tyear = Range("e10")

    file_name = tyear & ".xls"
    If fso.FileExists(file_name) Then
    GoTo already_exists
    End If

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:= _
    file_name, FileFormat:=xlNormal, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
    ActiveWorkbook.Close
    GoTo endit
    already_exists:
    MsgBox (file_name & " already exists. Enter a different year.")
    endit:
    End Sub

  4. #4
    Registered User
    Join Date
    06-08-2005
    Posts
    67
    figured it out...needed Set fso = CreateObject("Scripting.filesystemobject")
    instead of the DIM statement

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1