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

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"
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

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"
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