Can't Kill file?

[andy]

The below code works great, thank God for many of you who
have helped me. However, I can't seem to delete the file I
am writing to the temporary folder. The code writes to the
folder, then picks up the email attachment from the
folder, but fails at the Kill statement that refers to
that folder and gives the following error: Run-time
error '53' file not found. Yet when I go there the file is
present. Do I have to close something? I tried a wb.close,
but it didn't help.

Public iMsg As Object
Public iConf As Object
Public wb As Workbook
Public WBname As String
Public Location As String

Private Sub btnSubmitForm_Click()

Application.ScreenUpdating = False
Set wb = ThisWorkbook
Location = "http://root.any/temporary/"
WBname = "UtilityForm" & Format(Now, "dd-mm-yy")
& ".xls"

wb.SaveAs Location & WBname

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1 ' CDO Source Defaults
Set Flds = iConf.Fields
With Flds
.Item
("http://schemas.microsoft.com/cdo/configuration/sendusing"
) = 2
.Item
("http://schemas.microsoft.com/cdo/configuration/smtpserver
") = "XXX-xx"
.Item
("http://schemas.microsoft.com/cdo/configuration/smtpserver
port") = 25
.Update
End With

With iMsg
Set .Configuration = iConf
.To = "(e-mail address removed)"
.CC = ""
.BCC = ""
.From = """Insite Web Form"" <[email protected]>"
.Subject = "Faser Account Form"
.TextBody = ""
.AddAttachment Location & WBname
.Send
End With

Kill Location & WBname
Set iMsg = Nothing
Set iConf = Nothing
Set WB1 = Nothing
Set WB2 = Nothing
Application.ScreenUpdating = True
End Sub

Thank you in advance
God bless you

 

[Dick Kusleika]

Andy

Try changing your SaveAs to

wb.SaveCopyAs Location & WBname

I'd guess you're getting an error because the workbook is open. Using
wb.Close stops execution of the code (because the code is in the workbook
you are closing) so the Kill statement never executes.

 

[andy]

Hi Dic
Thanks for your suggestion. However, I originally had the code as SaveCopyAs, and the code bombs at that point and gives an error something like can't find file... I will write the exact message tomorrow when I get to work

Thanks again, perhaps I just didn't set things up correct.
God bless yo

----- Dick Kusleika wrote: ----

And

Try changing your SaveAs t

wb.SaveCopyAs Location & WBnam

I'd guess you're getting an error because the workbook is open. Usin
wb.Close stops execution of the code (because the code is in the workboo
you are closing) so the Kill statement never executes

 

[mudraker]

If I read your code correctly you still have the file open.

You can not kill a file whilst it is ope

 

[andy]

Thank you for your reply
I agree that the file that I did a wb.SaveAs on is still
probabally open. However, as Dick pointed out, if I close
the wb then the rest of the code doesn't execute. And
anyway, if I understand correctly the wb object references
the ThisWorkbook, which is not the file written by the
SaveAs.

How do I close this file?
Do I let the code finish, then close the wb object, and
somehow reopen a different wb which reference the file
which was written by the SaveAs, then kill it?

Please give code sample of whatever suggestions you can
make. I appreciate your help

Thank you
God bless you

 

[andy]

Greetings Dick,
I'm back at work, and these are the specifics. First a
question. You mentioned that the file is still open, and
by doing a wb.close the code will stop executing and the
kill will never happen. I will take out the wb.close. How
do I close the file which was the result of the SaveAs? so
that then I can kill it? Also, if it is open then how does
the email send it as an attachment (I thought it had to be
closed to send)? or is the email .AddAttachment, and
the .send keeping the file open somehow? How do I close it?

OR
I originally had the code as SaveCopyAs, however the
code always stops at the SaveCopyAs statement with the
following error: "run-time error '1004':
Method 'SaveCopyAs' of object'_Workbook' failed".
I then used the debugger to see where it stopped, which is
on the SaveCopyAs line, and then tried to start the code
running from that point again. This time it gives me the
following error: "run-time
error '1004': 'http://insite.mncppc.temporary/FaserAcctForm
28-04-04.xls' could not be found. Check the spelling of
the file name, and verify that the file location is
correct. If you are trying to open the file from your list
of most recently used files on the File menu, make sure
that the file has not been renamed, moved, or deleted."

Its as though it is trying to find the file it is
supposed to be writing before it has written it!

If I stop the code and then restart it from there I get
the following error: "run-time error '1004': Application-
defined or object-defined error."

Please give me any suggestions as how to resolve this
(please include code samples).

Thank you in advance
God bless you

 

[Dick Kusleika]

Andy

I'm back at work, and these are the specifics. First a
question. You mentioned that the file is still open, and
by doing a wb.close the code will stop executing and the
kill will never happen. I will take out the wb.close. How
do I close the file which was the result of the SaveAs? so
that then I can kill it?

The only way you can do that is to have your code in a different workbook.
The file that is the result of the SaveAs is the file with the code in it.

Also, if it is open then how does
the email send it as an attachment (I thought it had to be
closed to send)? or is the email .AddAttachment, and
the .send keeping the file open somehow? How do I close it?

It sends the file that's saved to the disk. The file that is open is
actually a temp file until it is saved. That's why there's no problem
sending and "open" file. The .AddAttachment and .Send are not opening the
file.

OR
I originally had the code as SaveCopyAs, however the
code always stops at the SaveCopyAs statement with the
following error: "run-time error '1004':
Method 'SaveCopyAs' of object'_Workbook' failed".

I don't know why SaveCopyAs would cause that error. I've never had a
problem using it. The only thing I found on Google was a problem when the
name already existed. However, when I tested it I couldn't reproduce that
problem.

Maybe it's a problem with where you're saving it. Try changing your
location to your local disk and using SaveCopyAs. If it works without
error, at least you've isolated the problem.

 

[Dave Peterson]

Actually, you can kill that open workbook:

This was posted by Jim Rech:

Sub testme()

With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close SaveChanges:=False
End With

End Sub

 

[Dick Kusleika]

Dave

Wow. There's something not right about that, but it's pretty cool.

 

[Ron de Bruin]

I use it in all the mail Examples on my site Dick

 

[Ron de Bruin]

Hi Andy

More examples are on this page
http://www.rondebruin.nl/cdo.htm

I think you have got a problem with this
Location = "http://root.any/temporary/"

Try to use a local folder to see if it is working then