I am using excel 2003. The following code names the file I want saved and
puts it in the correct folder. I want to be able to change the folder I put
it in based on the value of cell j627.




Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").value
strpath = "c:\field tickets\ "
str3 = ActiveSheet.Range("c8").value
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


When I put c:\field tickets\ in cell J627 and change the code as
follows it doesn't work. I would appreciate anyone that can help me figure
out whats wrong. The directory c:\field tickets\ does exist and as I said I
can save the file to that directory if I just don't get the directory from
cell j627:

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.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False

At first glance, it looks like that line should work. Try putting a
breakpoint on this line...

fsavename = strpath & strappend & str3 & ".xls"

and then execute this command in the Immediate window...

? "<" & strpath & strappend & str3 & ".xls" & ""

This is the text being assigned to the fsavename variable... you are looking
specifically for missing or doubled up backslashes and spaces where there
shouldn't be any (the "<" and "" symbols should have not spaces between
them and the rest of the text).

--
Rick (MVP - Excel)


"bigjim" wrote in message
...
I am using excel 2003. The following code names the file I want saved and
puts it in the correct folder. I want to be able to change the folder I
put
it in based on the value of cell j627.




Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").value
strpath = "c:\field tickets\ "
str3 = ActiveSheet.Range("c8").value
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


When I put c:\field tickets\ in cell J627 and change the code as
follows it doesn't work. I would appreciate anyone that can help me
figure
out whats wrong. The directory c:\field tickets\ does exist and as I said
I
can save the file to that directory if I just don't get the directory from
cell j627:

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.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False

Well, it acted like it was trying to save it to that folder, but I gat an
error: The file could not be accesed. Make sure the file name does not
contain any of the following characters: < ? [ ] : or *

Have you got any other ideas I might try. I'm at my wits end trying to
figure this out. I really do appreciate your help.

Jim Ford

"Rick Rothstein" wrote:

At first glance, it looks like that line should work. Try putting a
breakpoint on this line...

fsavename = strpath & strappend & str3 & ".xls"

and then execute this command in the Immediate window...

? "<" & strpath & strappend & str3 & ".xls" & ""

This is the text being assigned to the fsavename variable... you are looking
specifically for missing or doubled up backslashes and spaces where there
shouldn't be any (the "<" and "" symbols should have not spaces between
them and the rest of the text).

--
Rick (MVP - Excel)


"bigjim" wrote in message
...
I am using excel 2003. The following code names the file I want saved and
puts it in the correct folder. I want to be able to change the folder I
put
it in based on the value of cell j627.




Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").value
strpath = "c:\field tickets\ "
str3 = ActiveSheet.Range("c8").value
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


When I put c:\field tickets\ in cell J627 and change the code as
follows it doesn't work. I would appreciate anyone that can help me
figure
out whats wrong. The directory c:\field tickets\ does exist and as I said
I
can save the file to that directory if I just don't get the directory from
cell j627:

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.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False

If you put the breakpoint on the line I said to, there would be no attempt
to save the file unless you pressed Run to continue it (which I forgot to
say, don't do that as we are simply trying to debug the problem for now). If
you are not familiar with how to place a breakpoint on a line, simply do
this... click the vertical gray bar (located at the left side of the code
window) next to the line "fsavename" line. This will put a red dot in the
gray bar and highlight the line in red. Now when you run the program, your
code will stop at that line (but not execute it) and then you can print the
line of code I gave to you into the Immediate window.

--
Rick (MVP - Excel)


"bigjim" wrote in message
...
Well, it acted like it was trying to save it to that folder, but I gat an
error: The file could not be accesed. Make sure the file name does not
contain any of the following characters: < ? [ ] : or *

Have you got any other ideas I might try. I'm at my wits end trying to
figure this out. I really do appreciate your help.

Jim Ford

"Rick Rothstein" wrote:

At first glance, it looks like that line should work. Try putting a
breakpoint on this line...

fsavename = strpath & strappend & str3 & ".xls"

and then execute this command in the Immediate window...

? "<" & strpath & strappend & str3 & ".xls" & ""

This is the text being assigned to the fsavename variable... you are
looking
specifically for missing or doubled up backslashes and spaces where there
shouldn't be any (the "<" and "" symbols should have not spaces between
them and the rest of the text).

--
Rick (MVP - Excel)


"bigjim" wrote in message
...
I am using excel 2003. The following code names the file I want saved
and
puts it in the correct folder. I want to be able to change the folder
I
put
it in based on the value of cell j627.




Dim strappend As String
Dim strpath As String
Dim str3 As String

strappend = ActiveSheet.Range("j8").value
strpath = "c:\field tickets\ "
str3 = ActiveSheet.Range("c8").value
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


When I put c:\field tickets\ in cell J627 and change the code as
follows it doesn't work. I would appreciate anyone that can help me
figure
out whats wrong. The directory c:\field tickets\ does exist and as I
said
I
can save the file to that directory if I just don't get the directory
from
cell j627:

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.Sheets("Devon ASC f").SaveAs fsavename
ActiveWorkbook.Close False