Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getsaveasfilename loop... < false and not already exists
I would like to know if there is a way to turn this:
Sub saveas() Workbooks.Open Filename:= _ "C:\Toxo QNS temp.xls" Do fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _ fileFilter:="Excel Files (*.xls), *.xls") Loop Until fname < False If Dir(fname) < "" Then MsgBox "You can't overwrite an existing file try again" Windows("Toxo QNS temp.xls").Close Call saveas into a working version of this: Workbooks.Open Filename:= _ "C:\Toxo QNS temp.xls" Do fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _ fileFilter:="Excel Files (*.xls), *.xls") Loop Until (fname < False) And (Dir(fname) Is "") The second code gives a type mismatch at the "(Dir(fname) Is "")" The goal being to stop accidental overwrites and not have to restart the macro to do it, so that i can drop the altered loop until snippet into all of the places it would be good to have. If there isnt a handy way to do it, I am fine with that i would just like to know so i stop trying to figure it out. Thank you in advance for any responses john |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getsaveasfilename loop... < false and not already exists
Try this code
Sub saveas() Workbooks.Open Filename:= _ "C:\Toxo QNS temp.xls" Do fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _ fileFilter:="Excel Files(*.xls), *.xls") If fname < False Then If Dir(fname) < "" Then MsgBox "You can't overwrite an existing file try again" fname = False End If End If Loop Until fname < False Windows("Toxo QNS temp.xls").Close Call saveas End Sub "goaljohnbill" wrote: I would like to know if there is a way to turn this: Sub saveas() Workbooks.Open Filename:= _ "C:\Toxo QNS temp.xls" Do fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _ fileFilter:="Excel Files (*.xls), *.xls") Loop Until fname < False If Dir(fname) < "" Then MsgBox "You can't overwrite an existing file try again" Windows("Toxo QNS temp.xls").Close Call saveas into a working version of this: Workbooks.Open Filename:= _ "C:\Toxo QNS temp.xls" Do fname = Application.GetSaveAsFilename("Save as dayToxoQNS", _ fileFilter:="Excel Files (*.xls), *.xls") Loop Until (fname < False) And (Dir(fname) Is "") The second code gives a type mismatch at the "(Dir(fname) Is "")" The goal being to stop accidental overwrites and not have to restart the macro to do it, so that i can drop the altered loop until snippet into all of the places it would be good to have. If there isnt a handy way to do it, I am fine with that i would just like to know so i stop trying to figure it out. Thank you in advance for any responses john |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getsaveasfilename loop... < false and not already exists
Worked great! thanks. It never occured to me to put ifs inside the do
loop. Everytime I learn 1 thing i end up wondering how to do 3 more. I did take out the msgbox, temp close and call since that was the stuff i wanted to do away with to begin with. The if part will go very easily into other Do getsaveasfilename loops to give the same functionality. Thanks again john |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - Do Loop or SaveChanges = false | Excel Discussion (Misc queries) | |||
VBA always creates linked chart even when using PasteExcelTable False, False, False in Office 2007 | Excel Programming | |||
need array of true/false if text exists | Excel Worksheet Functions | |||
Loop until a condition is False | Excel Programming | |||
True Or False, no matter what... it still displays the false statement | Excel Programming |