Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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 dont seem to be able to retrieve data from the cells in a format that doesnt 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. -- Dolores |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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) "Dolores" wrote in message ... 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. -- Dolores |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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 "Dolores" wrote: 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 dont seem to be able to retrieve data from the cells in a format that doesnt 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. -- Dolores |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
Duke! My Hero! Thank you very much!
-- Dolores "Dolores" wrote: 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 dont seem to be able to retrieve data from the cells in a format that doesnt 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. -- Dolores |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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. -- Dolores "Bob Phillips" wrote: 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) "Dolores" wrote in message ... 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. -- Dolores |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
Well that is strange. They do the same thing!
-- HTH RP (remove nothere from the email address if mailing direct) "Dolores" wrote in message ... 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. -- Dolores "Bob Phillips" wrote: 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) "Dolores" wrote in message ... 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. -- Dolores |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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? -- Dolores "Bob Phillips" wrote: Well that is strange. They do the same thing! -- HTH RP (remove nothere from the email address if mailing direct) "Dolores" wrote in message ... 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. -- Dolores "Bob Phillips" wrote: 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) "Dolores" wrote in message ... 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. -- Dolores |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macro VBA code to name Save-As file
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 "Dolores" wrote in message ... 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? -- Dolores "Bob Phillips" wrote: Well that is strange. They do the same thing! -- HTH RP (remove nothere from the email address if mailing direct) "Dolores" wrote in message ... 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. -- Dolores "Bob Phillips" wrote: 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) "Dolores" wrote in message ... 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. -- Dolores |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Change case...help please | Excel Worksheet Functions | |||
Excell2003 (SP-1) File > Save and File > Save As.. grayed out | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |