closing a variable file name with macros

B

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.
 
J

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.
 
B

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)
 
J

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

bigjim

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

Bob Phillips said:
Try using

Workbooks(fsavename).Close False


--
HTH

Bob

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

bigjim

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top