Saving a file with a variable name

[bigjim]

I am using Excel 2003 and I want to save a file to a different folders based
on the contents of a cell.

If I use this code it saves the file in the folder c:\field tickets
just as it is supposed to:


Dim strappend As String
Dim strpath As String
Dim str3 As String
strappend = ActiveSheet.Range("j8")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("c8")

fsavename = strpath & strappend & str3 & ".xls"
If Dir(fsavename) <> "" Then
fsavename = strpath & strappend & str3 & "a.xls"

End If

ActiveWorkbook.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False


Now, if I use this code, it saves it to My Documents and not to the folder
c:\field tickets. What am I doing wrong?

Cell j627 contains C:\Field Tickets\

Dim strappend As String
Dim strpath As String
Dim str3 As String
strappend = ActiveSheet.Range("j8")
strpath = ActiveSheet.Range("j627")
str3 = ActiveSheet.Range("c8")

fsavename = strpath & strappend & str3 & ".xls"
If Dir(fsavename) <> "" Then
fsavename = strpath & strappend & str3 & "a.xls"

End If

ActiveWorkbook.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False

 

[Bob Phillips]

Dim strappend As String
Dim strpath As String
Dim str3 As String
strappend = ActiveSheet.Range("j8").Value
strpath = ActiveSheet.Range("j627").Value
str3 = ActiveSheet.Range("c8").Value

fsavename = strpath & strappend & str3 & ".xls"
If Dir(fsavename) <> "" Then

fsavename = strpath & strappend & str3 & "a.xls"
End If

ActiveWorkbook.SaveAs fsavename
ActiveWorkbook.Close False

 

[bigjim]

It still doesn't save it to c:\field tickets. It goes to my documents. Can
you think of anything else I might try? I checked and made sure that
c:\Field Tickets\ was in cell J627, but it still won't save it to that folder.

 

[Chip Pearson]

Does the folder C:\Field Tickets exist? If not, you need to use MkDir
to create the folder.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[bigjim]

Yes it exists. If I replace strpath = ActiveSheet.Range("j627").Value
with strpath = "c:\field tickets\" It saves it to field tickets. It just
won't pick this up for j627.