How to Save As Variable File Name

[michele]

Hi,

I want to have my macro save as 'active cell' - 'active cell'
quote.xls.

Any idea on how to do this?

I'm trying to save quotes with the customer name and quote number, i.e.
Staples - 20503 Quote.xls where it would find Staples in the cell i'm
on and 20503 in the cell i've moved to one cell to the left of Staples.
Of course, the contents of the cells will be different each time.

I've got everything working for this project except this so I'd really
appreciate the help.

Thanks,

Michele

 

[Guest]

Here is the code.

The code will take the value from the activecell and the value of the cell
left to the activecell (e.g B1 and A1)

savename = ActiveCell.Value & " - " & ActiveCell.Offset(0, -1).Value & "
Quote.xls"
ActiveWorkbook.SaveAs Filename:= _
"C:\" & savename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


"(e-mail address removed)" schreef:

 

[FrigidDigit]

Thanks,

I am also going to use the suggestion!

Is there a way to save to the active directory onstead of a fixed drive
path?

Thanks

 

[Dave Peterson]

Depends on what you mean by active directory.

curdir will return the active directory
thisworkbook.path will return the directory of the workbook running the code
activeworkbook.path will return the directory of the activeworkbook

So you could use:
ActiveWorkbook.SaveAs Filename:=curdir & "\" & savename, ....

Or one of those others

 

[FrigidDigit]

Thanks Dave!!

Depends on what you mean by active directory.

curdir will return the active directory
thisworkbook.path will return the directory of the workbook running the
code
activeworkbook.path will return the directory of the activeworkbook

So you could use:
ActiveWorkbook.SaveAs Filename:=curdir & "\" & savename, ....

Or one of those others

 

[michele]

Hi,

I'm using this code and much appreciate it, but now the client wants to
be able to add a bit of text in between the filename. So I've made it
stop before the save with the filename showing and I can add to the
filename, but when it saves it doesn't include what I've added. Here
is the code. Any ideas what I need to do to fix it?

savename = qrPath & ActiveCell.Value & " - " &
ActiveCell.Offset(-3, 2).Value & " Quote.xls"
With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = savename
.Show
End With

ActiveWorkbook.SaveAs filename:= _
savename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

I'd really appreciate some advice.

Thanks,

Michele

 

[Dave Peterson]

I think I'd double check what's in Activecell and activecell.offset(-3,2).
Maybe those cells are empty???

And is there a reason why you show the SaveAs dialog, then follow up with your
own SaveAs command?

 

[michele]

Hi,

The cells are not empty. The filename comes up fine; it's just that
when I modify the filename it doesn't save as that name.

LOL. Why do I show and then saveas....because I don't know what I'm
doing. I thought I could make it stop so I could modify the filename,
but I assume it's in the wrong place. Do you know if I can do what I'm
trying to do?

P.S. - You answered this post at the same time as I sent the second one
believing that no one would see this old post.

Thanks very much,

Michele

 

[Dave Peterson]

I think I'd dump this section:

With Application.FileDialog(msoFileDialogSaveAs)
.InitialFileName = savename
.Show
End With

And just use the second portion to do the save.

And if there's a chance that the file could already exist:

application.displayalerts = false

ActiveWorkbook.SaveAs filename:= _
savename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

application.displayalerts = true

And I think I'd add a msgbox just to verify that savename is actually what you
think it is.

msgbox savename

(I'm not sure I'd rely on the value in the activecell--any cell could be active,
couldn't it?)

 

[michele]

Hi,

This works, but it doesn't stop and let me update the filename. I'm
okay on the activecell since I move to it just before. The filename is
always correct, except I can't add to it.

You see the file name is "'company' - 'quote#' quote.xls", (were
company and quote# come nicely from cells), but since the customer
often does several quotes the same day for the same company they want
to be able to add something in the middle, i.e. "'company' 'stuff here'
- 'quote#' quote.xls". So I need the saveas to stop and allow filename
updates.

I hope this makes sense.

Thanks,

Michele

 

[Dave Peterson]

I get you.

How about something like this:

Option Explicit
Sub testme01()

Dim SaveName As Variant
Dim qrPath As String

qrPath = "C:\my documents\excel\test\"

SaveName = qrPath & ActiveCell.Value & " - " & _
ActiveCell.Offset(-3, 2).Value & " Quote"

SaveName = Application.GetSaveAsFilename _
(InitialFileName:=SaveName, filefilter:="Excel Files, *.xls")

If SaveName = False Then
MsgBox "Please try later" 'user hit cancel
Else
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=SaveName, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
End If

End Sub

I used my own folder and changed SaveName to a variant--it can return False if
the user hits the cancel button.