+ Reply to Thread
Results 1 to 3 of 3

error with saveas....

  1. #1
    jeramie
    Guest

    error with saveas....

    this code saves the file like i want it to, but if prompted to overwrite and
    i click 'no', i get an error. how can i get rid of the error or wait for the
    next worksheet change?



    Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Page 1").Range("A5")
    If Not .Range("A5") Is Nothing Then
    SaveAs Filename:="H:\Daily Reports\" & Worksheets("Page
    1").Range("A12").Text & Worksheets("Page 1").Range("H9").Text & "to" &
    Worksheets("Page 1").Range("K9").Text & ".xls"
    End If
    End With

    End Sub


  2. #2
    Jim Cone
    Guest

    Re: error with saveas....

    Really?
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware

    '----------
    Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Err_Exit
    Dim strPath As String
    Dim lngValue As Long
    If Target.Address Like "$A$9" Then
    If Len(Target.Value) Then
    strPath = "H:\Daily Reports\" & Worksheets("Page1").Range("A12").Text & _
    Worksheets("Page 1").Range("H9").Text & "to" & _
    Worksheets("Page 1").Range("K9").Text & ".xls"
    lngValue = Len(Dir(strPath))
    If lngValue Then
    If MsgBox("Do you want to overwrite the existing file ? ", _
    vbQuestion + vbYesNo, "File Exists") = vbYes Then
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs strPath
    Application.DisplayAlerts = True
    End If
    Else
    ActiveWorkbook.SaveAs strPath
    End If
    End If
    End If
    Exit Sub

    Err_Exit:
    Beep
    Application.DisplayAlerts = True
    End Sub
    '------------


    "jeramie" <jeramie@discussions.microsoft.com>
    wrote in message...

    this code saves the file like i want it to, but if prompted to overwrite and
    i click 'no', i get an error. how can i get rid of the error or wait for the
    next worksheet change?

    Sub Worksheet_Change(ByVal Target As Range)
    With Worksheets("Page 1").Range("A5")
    If Not .Range("A5") Is Nothing Then
    SaveAs Filename:="H:\Daily Reports\" & Worksheets("Page
    1").Range("A12").Text & Worksheets("Page 1").Range("H9").Text & "to" &
    Worksheets("Page 1").Range("K9").Text & ".xls"
    End If
    End With
    End Sub


  3. #3
    jeramie
    Guest

    Re: error with saveas....

    Thanks, Jim, It works great!


    "Jim Cone" wrote:

    > Really?
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
    >
    > '----------
    > Sub Worksheet_Change(ByVal Target As Range)
    > On Error GoTo Err_Exit
    > Dim strPath As String
    > Dim lngValue As Long
    > If Target.Address Like "$A$9" Then
    > If Len(Target.Value) Then
    > strPath = "H:\Daily Reports\" & Worksheets("Page1").Range("A12").Text & _
    > Worksheets("Page 1").Range("H9").Text & "to" & _
    > Worksheets("Page 1").Range("K9").Text & ".xls"
    > lngValue = Len(Dir(strPath))
    > If lngValue Then
    > If MsgBox("Do you want to overwrite the existing file ? ", _
    > vbQuestion + vbYesNo, "File Exists") = vbYes Then
    > Application.DisplayAlerts = False
    > ActiveWorkbook.SaveAs strPath
    > Application.DisplayAlerts = True
    > End If
    > Else
    > ActiveWorkbook.SaveAs strPath
    > End If
    > End If
    > End If
    > Exit Sub
    >
    > Err_Exit:
    > Beep
    > Application.DisplayAlerts = True
    > End Sub
    > '------------
    >
    >
    > "jeramie" <jeramie@discussions.microsoft.com>
    > wrote in message...
    >
    > this code saves the file like i want it to, but if prompted to overwrite and
    > i click 'no', i get an error. how can i get rid of the error or wait for the
    > next worksheet change?
    >
    > Sub Worksheet_Change(ByVal Target As Range)
    > With Worksheets("Page 1").Range("A5")
    > If Not .Range("A5") Is Nothing Then
    > SaveAs Filename:="H:\Daily Reports\" & Worksheets("Page
    > 1").Range("A12").Text & Worksheets("Page 1").Range("H9").Text & "to" &
    > Worksheets("Page 1").Range("K9").Text & ".xls"
    > End If
    > End With
    > End Sub
    >
    >


+ 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