Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SaveAs method error | Excel Discussion (Misc queries) | |||
SaveAs causes 0x800A03EC error | Excel Programming | |||
error message with saveas | Excel Discussion (Misc queries) | |||
SaveAs Error | Excel Programming | |||
Error on SaveAs Macro | Excel Programming |