Solved: Sub SvMe()

[Gil]

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]

ChDir iif(right(Range("A1").text,1)="\",Range("A1").text,Range("A1").text & "\")

Change A1 to whatever Cell the dir is in.

[Blade Hunter]

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

[Gil]

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]

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]

Look ma, no IF!



Sub SvMe()
ActiveWorkbook.SaveAs Filename:=Replace(Range("B1").Text & "\", "\\", "\") _
& Range("A1").Text & " " & Format$(Date, "yyyymmdd") & ".xls"
End Sub

[Gil]

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]

Blade Hunter's (and my variation) are better because they have a little built in validation. But it is marginal.

[Bob Phillips]

Oh BTW, I think my timestamp is better, having the year first as it allows for sorting.

[mdmackillop]

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]

What about some line-breaks to keep it on the screen? :devil2:

Tis done :)

[Blade Hunter]

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]

Look ma, no IF!



Sub SvMe()
ActiveWorkbook.SaveAs Filename:=Replace(Range("B1").Text & "\", "\\", "\") _
& Range("A1").Text & " " & Format$(Date, "yyyymmdd") & ".xls"
End Sub


Very nice :)

[Gil]

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