View Full Version : Solved: Sub SvMe()
Hello
I have been using this code obtained from these pages successfully but now need to adapt it slightly.
Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\Documents and Settings\ USER NAME \Desktop"
ActiveWorkbook.SaveAs Filename:=newFile
End Sub
instead of using the following I want to get the directory filepath from a cell containing the path
ChDir _
"C:\Documents and Settings\ USER NAME \Desktop"
I have tried several options but go round in circles. Any suggestions would be appreciated
Blade Hunter
02-28-2011, 08:50 PM
ChDir iif(right(Range("A1").text,1)="\",Range("A1").text,Range("A1").text & "\")
Change A1 to whatever Cell the dir is in.
Blade Hunter
02-28-2011, 08:53 PM
Or do the whole thing in one line:
Sub SvMe()
ActiveWorkbook.SaveAs Filename:=IIf(Right(Range("B1").Text, 1) = "\", Range("B1").Text, Range("B1").Text & "\") & Range("A1").Text & " " & Format$(Date, "mm-dd-yyyy") & ".xls"
End Sub
B1 is DIR and A1 is filename
Hello Blade Hunter
Thank you for the swift reply. I will try them in due course but in the meantime I tripped over this in my searching and it seems to work for me.
'I just substituted
ChDir _
"C:\Documents and Settings\ USER NAME \Desktop"
'for
ChDir Range("H9")
Blade Hunter
02-28-2011, 09:49 PM
Hello Blade Hunter
Thank you for the swift reply. I will try them in due course but in the meantime I tripped over this in my searching and it seems to work for me.
'I just substituted
ChDir _
"C:\Documents and Settings\ USER NAME \Desktop"
'for
ChDir Range("H9")
The only difference between that and my post is that if there is no "\" on the end mine will add it however, you don't need to change the active dir, you can put the DIR when you save as without changing the users currently active DIR.
The second post I made is a complete 1 line replacement for the whole module.
Bob Phillips
03-01-2011, 01:57 AM
Look ma, no IF!
Sub SvMe()
ActiveWorkbook.SaveAs Filename:=Replace(Range("B1").Text & "\", "\\", "\") _
& Range("A1").Text & " " & Format$(Date, "yyyymmdd") & ".xls"
End Sub
Hello
Thank you Blade Hunter & XLD for the replies.
Blade Hunter, I was unable to get the first version to work but the second is ok. By adding a piece for a time stamp gives an almost unique file name
& " " & Format$(Time, "hh-mm-ss")
Both Blade Hunter's & XLD's contributions work perfectly as well as the solution I stumbled across.
I will never fully understand this code business but out of the 3 options I now have is there a 'best' to use. Or is it a case that they are all equal and there may be many other ways of doing it.
Many thanks
Gil
:thumb
Bob Phillips
03-01-2011, 07:57 AM
Blade Hunter's (and my variation) are better because they have a little built in validation. But it is marginal.
Bob Phillips
03-01-2011, 07:57 AM
Oh BTW, I think my timestamp is better, having the year first as it allows for sorting.
mdmackillop
03-01-2011, 08:50 AM
Oh BTW, I think my timestamp is better, having the year first as it allows for sorting.
What about some line-breaks to keep it on the screen? :devil2:
Bob Phillips
03-01-2011, 02:59 PM
What about some line-breaks to keep it on the screen? :devil2:
Tis done :)
Blade Hunter
03-01-2011, 03:04 PM
Oh BTW, I think my timestamp is better, having the year first as it allows for sorting.
I have to agree, this is my default naming convention for this very reason :)
Blade Hunter
03-01-2011, 03:07 PM
Look ma, no IF!
Sub SvMe()
ActiveWorkbook.SaveAs Filename:=Replace(Range("B1").Text & "\", "\\", "\") _
& Range("A1").Text & " " & Format$(Date, "yyyymmdd") & ".xls"
End Sub
Very nice :)
Hello
I was just waiting for Blade Hunter to make his reply. The solutions suggested have now been put into practice sucessfully.
Many thanks to Blade Hunter and XLD for your help and to mdmackillop for his positive suggestion.
Gil
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.