Save As - Very Confused

[gjadcock]

I am trying to write a save as button which requests a file name using the standard saveas dialogue, this then performs a series of copy and paste routines then saves the file.

Private Sub SaveResults_Click()

fileSaveName = Application.GetSaveAsFilename(InitialFileName:="query1", _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
Set fs = CreateObject("Scripting.FileSystemObject")
Set fname = fs.GetFile(fileSaveName)
Workbooks.Add.SaveAs Filename:=fname.Name
Windows("web_Reports.xls").Activate
Sheets("Results").Select
ActiveSheet.Range("a:q").Copy
Windows(fname.Name).Activate
Sheets("Sheet1").Select
ActiveSheet.Range("a1").PasteSpecial SkipBlanks:=True
ActiveWorkbook.Save
MsgBox ("File sucessfully saved as :" & Chr(10) & Chr(10) & fileSaveName)
Workbooks(fname.Name).Close SaveChanges:=False
Windows("web_Reports.xls").Activate

End If


End Sub

I am getting an error at the line:

Workbooks.Add.SaveAs Filename:=fname.Name

I am sure I had this working but have changed the code as I also wanted to give the usual options if the file exists.

Also

How do I get the response from the File Exists alert box?

 

[DonkeyOte]

is fname.name providing you with the full path?

Step through to see value of fname - I don't know off the top of my head but try replacing .name with .fullname

 

[gjadcock]

fname.name gives me the file name and extension only without the path which is what I need. I managed to work out the save bit by seperating the statement onto two lines so it now reads:

Workbooks.Add
ActiveWorkbook.SaveAs Filename:=fname.Name

This seems to be OK. The problem now is though, if the file exists I get an Exclamation box asking what I want to do. How do I get the response from this dialogue for No and Cancel

Thanks

 

[DonkeyOte]

well what do you want to do in these instances?

You could set the saveas name prior to saving as then do a check to see it the file exists

eg

'set 10 prior to dialog box for selecting file name to save as
10

FNAME = your full path file name

If Dir(FNAME) <> "" then
MSG1 = MsgBox("Overwrite existing file?",vbyesno,"OVERWRITE")
If MSG1 = vbyes then
Application.DisplayAlerts = False
Else
GoTo 10
End If
End If

 

[AJ]

Here's a similar sort of thing for checking a file and getting rid of it if the user says ok to overwrite...


<font face=Courier New>    fileSaveName = Application.GetSaveAsFilename(InitialFileName:="query1", _
    fileFilter:="Excel Files (*.xls), *.xls")
    <SPAN style="color:darkblue">If</SPAN> fileSaveName <> <SPAN style="color:darkblue">False</SPAN> <SPAN style="color:darkblue">Then</SPAN>
<SPAN style="color:green">'       Use Dir function to see if file already exists.</SPAN>
        ChkFile = Dir(fileSaveName, vbNormal)
        <SPAN style="color:darkblue">If</SPAN> ChkFile <> "" <SPAN style="color:darkblue">Then</SPAN>
            DoubleCheck = MsgBox("The file " & ChkFile & " already exists. <SPAN style="color:darkblue">Do</SPAN> you want to replace the existing file?", vbYesNo + vbExclamation)
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:green">'       Delete existing file if overwrite is confirmed</SPAN>
        <SPAN style="color:darkblue">If</SPAN> DoubleCheck = vbYes <SPAN style="color:darkblue">Then</SPAN>
            Kill (fileSaveName)
        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:green">'       If we don't get a No above, then alls well</SPAN>
        <SPAN style="color:darkblue">If</SPAN> DoubleCheck <> vbNo <SPAN style="color:darkblue">Then</SPAN>

<SPAN style="color:green">'           Rest of your stuff here</SPAN>



        <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
</FONT>