closing a variable file name with macros
 

I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will be
appreciated.

closing a variable file name with macros
 

from
Workbooks("fsavename").Close False
to
activeworkbook.Close False

Your problem is due to the fact that fsavename has the path in the filename.
You only need thbe base name

or try this

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs strpath & fsavename
Workbooks("fsavename").Close False

I removed strpath from fsavename and then included it as a serate part of
the SAveas.



"bigjim" wrote:

I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will be
appreciated.

closing a variable file name with macros
 

Try using

Workbooks(fsavename).Close False


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bigjim" wrote in message
...
I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't
close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will
be
appreciated.

closing a variable file name with macros
 

Try just using Activeworkbook.close. Workbooks expects an index and you are
passing a name, one that does not exist because you have quotes on your
variable...
("fsavename").
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"bigjim" wrote:

I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will be
appreciated.

closing a variable file name with macros
 

That makes sense. Thanks a bunch. I'll try it.

"Bob Phillips" wrote:

Try using

Workbooks(fsavename).Close False


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bigjim" wrote in message
...
I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't
close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will
be
appreciated.

closing a variable file name with macros
 

Yes that worked well. I just used activeworkbook.close and it did just what
I wanted. I really appreciate the help.



"Joel" wrote:

from
Workbooks("fsavename").Close False
to
activeworkbook.Close False

Your problem is due to the fact that fsavename has the path in the filename.
You only need thbe base name

or try this

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs strpath & fsavename
Workbooks("fsavename").Close False

I removed strpath from fsavename and then included it as a serate part of
the SAveas.



"bigjim" wrote:

I'm using Excel 2003. I have created a separate workbook from a worksheet
and saved it to my hard drive. I am using the current date as part of the
file name so that it changes daily. All this works fine, but I can't close
the file after I save it. This is what I have now:

Dim strappend As String
Dim strpath As String
Dim str3 As String
Dim str4 As String
strappend = Format(Date, "mmddyyyy")
strpath = "c:\field tickets\"
str3 = ActiveSheet.Range("p1")

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

ActiveWorkbook.Sheets("devon asc f").SaveAs fsavename
Workbooks("fsavename").Close False


This last line is the problem. How do I close this file? All help will be
appreciated.