VB Guru to look at my SaveAs Code Please

[ElectricSkywalker]

Hi all,
Being relative new to VBA, I am still having a few difficulties writing the stuff. I am basically learning everything I can from this web site, and a couple of books I have....yet I am still on a hugh learning curve.

Can someone please have a look at my code below, and tell me what I have to change in order to get it to do a Save AS and automatically default to C:MyDocuments (its not going to the default c:MyDocuments at the moment)

Ohh, I also have a second question......I am finding if I try attempt to re-save the file....with the same name (using the below macro)....the "File already Exists" Screen pops up. Then if press cancel...the macro saves, but my code comes up saying "File Not Saved". My Question is.....How do I edit the "File already Exists" code so that it doesn't come up.....or if it does come up...write in the "IF" statement = to edit it based on the results.


My Code is:

Sub SaveAsFileName()
Dim myFile As String

On Error Resume Next

Title = "Enter Name of File to be saved"

myFile = Range("d9") & " " & Format(Range("d14"), "dd mmmm yyyy") & " Expense Claim Form"

FileSaveName = Application.GetSaveAsFilename("C:MyDocuments" & myFile, "Microsoft Excel Workbook (*.xls),*.xls")

If FileSaveName = False Then
MsgBox "Your File was not Saved"
Exit Sub
Else
ActiveWorkbook.saveas Filename:=myFile
MsgBox (myFile & " has now been Saved")
End If

End Sub



Thanking you all in advance

Greg

 

[lenze]

Here's another approach

http://216.92.17.166/board/viewtopic.php?topic=26979&forum=2

 

[oldham94l]

i stopped the "do you want to overwrite file" message today by putting the line:

Kill ("C:worksheetname.xls")

just before i use the SaveAs code

 

[VoG]

To stop the warning:

Application.DisplayAlerts = False

Then set to True after you have done the save.

 

[Jack in the UK]

Hi ive just posted this as the codes work just fine and cover 1001 issues you will / might have on this one, i wrote this some time back, and was to develop back ups of sheets, but read all the lines ive heavy commented the code so your find all you need in here to copy from

HHT, should do i realy hope you get sorted on this.

JACK

PS Dedicated to Richie(UK) who has developed this original post into a compleate works of art soon to be a freebie

""""""""""""""

Sub Jacks_BACKUP_By_DirDateTime_commented()
'Designed and created by Jack in the UK 6th June 2002 in London.

'Jack Names the Sub Routine.
Dim JackintheUKs_Test_Dir_Exists As String
'Jacks Sets the Directory to Test if Exists.
Dim JackintheUKs_Directory_Location_MainTree As String
'JACK Sets Main Dir to Check and Set if NON Exists.
Dim JackintheUKs_Directory_Location_SubDir As String
'JACK Sets SUB Dir to Check and Set if NON Exists.
JackintheUK_Date_Format = Format$(Now, "dddd d mmmm yyyy")
'JACK Sets His DATE Fomat UK STYLE *dddd mmmm d yyyy* Would be US Version.
JackintheUKs_Time_Format = Format$(Now, "hh mm ss")
'Jack Sets His Time Format to Hours Minutes Seconds.

JackintheUKs_Directory_Location_MainTree = "C:JACKS Backups Archive"
'JACK Sets Location of Archive Directory to MainTree
JackintheUKs_Test_Dir_Exists = Dir(JackintheUKs_Directory_Location_MainTree, vbDirectory)
'Jack Tests to See if MainTree Directory Exists. As C:JACKS Backups Archive.
If (JackintheUKs_Test_Dir_Exists = "") Then MkDir (JackintheUKs_Directory_Location_MainTree)
'Jacks Sets IF Statment
'Jack Tests If MainTree Exists Then DO NOTHING, BUT Add File Into That Directory.
'Jack Tests If MainTree Does Not Exist Then CREATE IT.
ChDir "C:JACKS Backups Archive"

'Jack Changes Directory From Default C: to C:JACKS Backups Archive

JackintheUKs_Directory_Location_SubDir = "C:JACKS Backups ArchiveJACK " & JackintheUK_Date_Format
'Jack Sets Location of Archive Directory to Sub Directory.
JackintheUKs_Test_Dir_Exists = Dir(JackintheUKs_Directory_Location_SubDir, vbDirectory)
'Jack Tests to See if Sub Directory Exists. As C:JACKS Backups ArchiveJACK and date
If (JackintheUKs_Test_Dir_Exists = "") Then MkDir (JackintheUKs_Directory_Location_SubDir)
'Jacks Sets IF Statment
'Jack Tests If Sub Directory Exists Then DO NOTHING, BUT Add File Into That Directory.
'Jack Tests If Sub Directory Does Not Exist Then CREATE IT.

ActiveWorkbook.SaveCopyAs _
Trim(JackintheUKs_Directory_Location_SubDir & _
"JACKS Backup_" & _
JackintheUK_Date_Format & _
" TIME ~ " & _
JackintheUKs_Time_Format & _
" ~ Filename ~ " _
& ActiveWorkbook.Name)
ActiveWorkbook.Save

'Jack* ActiveWorkbook.SaveCopyAs _
'Jack Sets the Save Copy of Original Document to SaveAs (With Jacks Special Touch)

'JACKs NOTE **** the _ is an Extension command so can bolt on, Aides Ease or Read.

'Jack* Trim
'Jack Uses the VB Command of Trim to Fragment the Save Statement

'JACK* (JackintheUKs_Directory_Location_SubDir & _
'Jacks Sets the Save to SubDir IE C:JACKS Backups ArchiveJACK
'JACKS Note **** This is As There is no Needs to Set Main.

'JACKS Note **** the & is Required to bolt on another / extram part.

'JACK* "JACKS Backup_" & _
'Jack Sets TEXT to Read JACKS Backup

'JACK* JackintheUK_Date_Format & _
'Jacks Sets Date to be Added to File Name as Required Format.

'JACK* " TIME ~ " & _
'Jack Sets TEXT to Read TIME ~

'JACK* JackintheUKs_Time_Format & _
'Jacks Sets TIME to be Added to File Name as Required Format.

'JACK* " ~ Filename ~ " _
'Jack Sets TEXT to ~ Filename ~

'JACK* & ActiveWorkbook.Name)
'JAck Adds The Original File Name from Original WorkBook.


'JACK* ActiveWorkbook.Save
'Jacks Just Saves teh Original WorkBook


End Sub