SAVE AS MACRO

N

Neil Holden

Below is the code that brings up the SAVE as prompt, and save to a default
location, I thought i had the correct code to automatically insert a cell
reference and the current date in the file name. Can you assist please?

Dim flToSave As Variant 'brings up save as dialogue filling in file name
with Job number
Dim flName As String
Dim flFormat As Long
Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design"
If Right(DefaultFolder, 1) <> "\" Then
DefaultFolder = DefaultFolder & "\"
End If

DefaultFilename = Range("C1")

If Right(UCase(DefaultFilename), 2) <> "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") & ".xls"
DefaultFilename = DefaultFilename & ".xls"
End If

flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel
Files (*.xls),*.xls", _
Title:="Save File As...")


If flToSave = False Then

Exit Sub

Else

ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat


End If
 
P

Patrick Molloy

hi. this is another branch - you should stay with the original thread if
possible.

try this:

Dim flName As String
Dim flFormat As Long
Dim msg As String
Dim Style As String
Dim sFilename As String

msg = "Are you sure you want to save the Smith quote?"
Style = vbYesNo + vbInformation + vbDefaultButton2


If MsgBox(msg, Style) = vbYes Then

flFormat = ActiveWorkbook.FileFormat

DefaultFolder = "M:\Design\"

ChDrive "M"
ChDir DefaultFolder
DefaultFilename = Range("C1")

'what this for idf you're asking the user to select?
If Right(UCase(DefaultFilename), 3) <> "XLS" Then
DefaultFilename = DefaultFilename & Format(Date, "ddmmyyyy") &
".xls"
End If

flToSave = Application.GetSaveAsFilename(flName, filefilter:="Excel
Files (*.xls),*.xls", _
Title:="Save File As...")

If flToSave = False Then

Exit Sub
End If

ThisWorkbook.SaveAs Filename:=flToSave, FileFormat:=flFormat

End If
 
P

Patrick Molloy

SOMETHING must happen. when you F8 through the code, does it get as far as
the filesave?
at what point does it exit the sub. If it gets past teh filesave without
error, then it saved the file somewhere, so what is the address? you can use
?activeworkbook.fullpath in the Immediate window.
 
N

Neil Holden

Sorry something does happen, a message prompt appears and then the save as
dailog appears but the what ever is in C1 doesnt appear in the file name,
also the current date doesn't appear.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PLEASE HELP!! SAVE MACRO 3
SAVE AS MACRO 8
SAVE AS MACRO 6
MACRO to Approve or Decline 2
EXCEL CONNECTED WITH OUTLOOK 1
INPUT BOX in excel 2003 2
SAVE AS MACRO 5
Emailing in excel 2003 5

Top