Macro VBA code to name Save-As file

[Guest]

Hi,

The following code works to name a Save_As file (but I want to add data from
two cells to the name of the file):
ActiveWorkbook.SaveAs Filename:= _
"C:\Filename.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

I want the complete file name to be made up of :
"C:\Filename " & <the string of data from merged cell C1:E1> & “ “ &
<the string of data from cell I1> & ".xls" _

I don’t seem to be able to retrieve data from the cells in a format that
doesn’t generate error messages like “Mismatch,†“Expected expression,†or
“Sub or Function not defined.â€

I have exhausted all of my ideas and would appreciate any suggestions.

 

[Bob Phillips]

Untested, but try this

ActiveWorkbook.SaveAs Filename:= _
"C:\Filename" & Range("C1").Value & " " & Range("I1").value & ".xls"
_
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Assuming your cells don't have illegal characters - i.e., characters that
Windows won't allow in a filename - then

Dim strFileName as String
strFileName = "c:\Filename " & range("C1").Text & " " & range("I1").Text &
".xls"

Then use your first syntax below but with
Filename:=strFileName

STANDARD SOAPBOX COMMENT -
Try to never use merged cells. The screw up sorting and selecting ranges
and all sorts of stuff. You can accomplish most of the same things that
merging 'offers' you by Centering Across Selection or by using Text Boxes

 

[Guest]

Duke! My Hero! Thank you very much!

 

[Guest]

Hi Bob,

Thanks for attempting to help. I tested the untested code you sent, but it
does not work. (The problem has been solved, however, because the code I
received earlier from Duke Carey did work.) Thanks again.

 

[Bob Phillips]

Well that is strange. They do the same thing!

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

I owe you an apology. I tested your code again, and it works. I must have
copied some extraneous material.

I am so sorry. How can I rectify the Evaluation that said the code did not
work?

 

[Bob Phillips]

You can't and there is no need ). It doesn't matter, I was just confused
as to why. The important thing is that you have a solution.

Regards

Bob