Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SaveAs using two cells for filename
I'm trying to save a file with two cells of data to create the filename. Here
is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" ..SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? |
#2
|
|||
|
|||
Dim fname as String
With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one of those cells contains a date--and that the value has slashes in it (12/31/2005)? If yes, this might help later... Dim fname as String With ActiveWorkbook.worksheets(1) fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls" .SaveAs fname End With If not, just ignore it. David wrote: I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? -- Dave Peterson |
#3
|
|||
|
|||
I've almost got it...it saved the file in the right format, but saved it deep
in my settings. I want to save it to a particular path, which was earlier in the code, but it is not working now...I always want to save in the directory below. By the way, you were right, the E6 was a DATE. Can I put spaces in the date format like mm dd yyyy? Here is what I have: Dim fname With ActiveWorkbook.Worksheets(1) fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls" ChDir "C:\Franchise_GPC\Ben Info\Big Picture 2005\" ..SaveAs fname "Dave Peterson" wrote: Dim fname as String With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one of those cells contains a date--and that the value has slashes in it (12/31/2005)? If yes, this might help later... Dim fname as String With ActiveWorkbook.worksheets(1) fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls" .SaveAs fname End With If not, just ignore it. David wrote: I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? -- Dave Peterson |
#4
|
|||
|
|||
I got it....here's what I ended up with...
Dim fname As String With ActiveWorkbook.Worksheets(1) fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls" ..SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname End With Thanks again for the help!! "Dave Peterson" wrote: Dim fname as String With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one of those cells contains a date--and that the value has slashes in it (12/31/2005)? If yes, this might help later... Dim fname as String With ActiveWorkbook.worksheets(1) fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls" .SaveAs fname End With If not, just ignore it. David wrote: I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? -- Dave Peterson |
#5
|
|||
|
|||
You didn't use "mm dd yyyy" as your format <bg.
I don't like spaces in my filename, and it might make it easier to read if you use underscores: fname = .Range("B4").Value & "_" & _ Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls" But this is just a personal preference. David wrote: I got it....here's what I ended up with... Dim fname As String With ActiveWorkbook.Worksheets(1) fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls" .SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname End With Thanks again for the help!! "Dave Peterson" wrote: Dim fname as String With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one of those cells contains a date--and that the value has slashes in it (12/31/2005)? If yes, this might help later... Dim fname as String With ActiveWorkbook.worksheets(1) fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls" .SaveAs fname End With If not, just ignore it. David wrote: I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Thank you again....!!
"Dave Peterson" wrote: You didn't use "mm dd yyyy" as your format <bg. I don't like spaces in my filename, and it might make it easier to read if you use underscores: fname = .Range("B4").Value & "_" & _ Format(.Range("E6").Value, "yyyy_mm_dd") & ".xls" But this is just a personal preference. David wrote: I got it....here's what I ended up with... Dim fname As String With ActiveWorkbook.Worksheets(1) fname = .Range("B4").Value & Format(.Range("E6").Value, "mmddyyyy") & ".xls" .SaveAs "C:\Franchise_GPC\Ben Info\Big Picture 2005\" & fname End With Thanks again for the help!! "Dave Peterson" wrote: Dim fname as String With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one of those cells contains a date--and that the value has slashes in it (12/31/2005)? If yes, this might help later... Dim fname as String With ActiveWorkbook.worksheets(1) fname = format(.Range("B4").Value,"yyyymmdd") & .Range("E6") & ".xls" .SaveAs fname End With If not, just ignore it. David wrote: I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |