Code problem
 

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

Code problem
 

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

Code problem
 

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

Code problem
 

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

Code problem
 

OK, I'll try that.

"Rick Rothstein" wrote:

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

Code problem
 

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

I'll fix that. I hope that's all it is. I'll be glad, but hacked it was
simple.

Thanks

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

Actually, that one works. The problem is when I try to retrieve the path
from a cell in the worksheet. But thanks for taking a look at it. I'm sure
preplexed

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

The problem has to be with how the data is formatted in the worksheet cells.
Check for leading or trailing spaces. Make sure you don't have quotation
marks in the cell, because they will be picked up as part of the string in
the code. If any of your cell data is numeric, such as date or time, make
sure it is formatted as text.

"bigjim" wrote:

Actually, that one works. The problem is when I try to retrieve the path
from a cell in the worksheet. But thanks for taking a look at it. I'm sure
preplexed

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

The data in the cell is just c:\field tickets. I think it is formated
general however, so I'll switch it to text and see what happens.

Thanks,

Jim

"JLGWhiz" wrote:

The problem has to be with how the data is formatted in the worksheet cells.
Check for leading or trailing spaces. Make sure you don't have quotation
marks in the cell, because they will be picked up as part of the string in
the code. If any of your cell data is numeric, such as date or time, make
sure it is formatted as text.

"bigjim" wrote:

Actually, that one works. The problem is when I try to retrieve the path
from a cell in the worksheet. But thanks for taking a look at it. I'm sure
preplexed

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

formating it as text didn't help. Instead of saving it by the correct name
in c:\field tickets, it is saveing it as falsefalsefalse.xls in my documents.

"JLGWhiz" wrote:

The problem has to be with how the data is formatted in the worksheet cells.
Check for leading or trailing spaces. Make sure you don't have quotation
marks in the cell, because they will be picked up as part of the string in
the code. If any of your cell data is numeric, such as date or time, make
sure it is formatted as text.

"bigjim" wrote:

Actually, that one works. The problem is when I try to retrieve the path
from a cell in the worksheet. But thanks for taking a look at it. I'm sure
preplexed

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim
wrote:

I'm not sure what he was looking for either, I did execute it down to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need to
initialize the varibles by stepping through the macro until those variables
should be initialized. If they then show false, there is a problem in the
code. I am not sure what Rick had was doing when he gave you that line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get <FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran the program and
sure enough it stopped there. Now where is the immediate line where I'm
supposed to type ? "<" & strpath & strappend & str3 & ".xls" & ""

"Rick Rothstein" wrote:

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

Code problem
 

The data in the cell is just c:\field tickets.

With or without a trailing back slash?

I saw your earlier post and do not understand how your are getting 3 "false"
statements as you reported. Do you have typed in text in the cells or a
formula generating your text?

--
Rick (MVP - Excel)


"bigjim" wrote in message
...
The data in the cell is just c:\field tickets. I think it is formated
general however, so I'll switch it to text and see what happens.

Thanks,

Jim

"JLGWhiz" wrote:

The problem has to be with how the data is formatted in the worksheet
cells.
Check for leading or trailing spaces. Make sure you don't have quotation
marks in the cell, because they will be picked up as part of the string
in
the code. If any of your cell data is numeric, such as date or time,
make
sure it is formatted as text.

"bigjim" wrote:

Actually, that one works. The problem is when I try to retrieve the
path
from a cell in the worksheet. But thanks for taking a look at it. I'm
sure
preplexed

"Gord Dibben" wrote:

Not sure if this has anything to do with it but I notice a space in
your
path

strpath = "c:\field tickets\ "

Not sure if that's legal.


Gord Dibben MS Excel MVP

On Thu, 5 Feb 2009 15:40:22 -0800, bigjim

wrote:

I'm not sure what he was looking for either, I did execute it down
to that
line and they all initialized to false as far as I can guess


"JLGWhiz" wrote:

Did you execute the macro down to that line? I believe you need
to
initialize the varibles by stepping through the macro until those
variables
should be initialized. If they then show false, there is a
problem in the
code. I am not sure what Rick had was doing when he gave you that
line, so
maybe he will clarify what the results mean.

"bigjim" wrote:

When I enter that in the Imediat Window, I get
<FalseFalseFalse.xls


"JLGWhiz" wrote:

Click ViewImmedieate Window on menu bar. To get rid of it
later, right
click on screen, then click hide.

"bigjim" wrote:

I've never done this before, but I got the red dot and ran
the program and
sure enough it stopped there. Now where is the immediate
line where I'm
supposed to type ? "<" & strpath & strappend & str3 &
".xls" & ""

"Rick Rothstein" wrote:

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