closing a variable file name with macros

[bigjim]

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.

 

[Joel]

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.

 

[Bob Phillips]

Try using

Workbooks(fsavename).Close False


--
HTH

Bob

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

 

[John Bundy]

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").

 

[bigjim]

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

Try using

Workbooks(fsavename).Close False


--
HTH

Bob

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

 

[bigjim]

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