path/file access error

[raymond3090]

HI, I hope somebody can help with this, I'm stumped!

I'm importing a bunch of data from an Excel spreadsheet. Right after I do this I need to delete that spreadsheet. I have the following code:

DoCmd.TransferSpreadsheet acImport, 8, "tblFieldData", "H:\temp", True, "Field"

...a few more imports like this take place (the data gets imported as intended)...

then...

Kill "H:\temp.xls"

This is where I get a path/file access error. Any suggestions?

regards,
-ray.

 

[Travis]

From the looks of your code you are importing "H:\temp" and trying to Delete "H:\temp.xls".

Check to see if the file is located in "H:\" with the name of "temp.xls"

When a file does not exist and you use Kill you will get an Error. You can use DIR to first check to see if a file does exist.

 

[raymond3090]

thanks for the reply travis! Before my 'kill' statement I added:

MsgBox Dir("H:\temp.xls") which presented a message box with text: "temp.xls".

I changed my import statements to include H:\temp.xls rather than just H:\temp. The import still works as expected, but I still get the same path/file access error.

If I run the same kill statement (just that statement alone) after all of this code ends, it deletes the file just fine. Does the transferSpreadsheet method somehow open the temp.xls file, and preventing me from deleting it? That's the only thing I can see happening here.

If so, how do I close it?

thanks again,

ray

 

[Travis]

It must, since it wont let you delete it. Try doing this:

'This will suppress the Error and continue to loop while the H:\Temp.xls file still exists.
On Error resume next
Do While DIR("H:\Temp.xls")<>""
  Kill "H:\Temp.xls"
  Err.Clear
Loop

 

[raymond3090]

travis:

i used the code. It doesn't delete the file and as a result it's an infinite loop. After a couple minutes I aborted the task.

-ray

 

[Travis]

'This will suppress the Error and continue to loop while the H:\Temp.xls file still exists.
On Error resume next
[b]DoEvents[/b]
Do While DIR("H:\Temp.xls")<>""
  Kill "H:\Temp.xls"
  Err.Clear
  [b]DoEvents[/b]
Loop

 

[raymond3090]

travis: thanks again for your help. I tried to do what you suggested in the previous message. The code stopped this time (at least I think it did) but the temp file was still there.

I'm not really certain as to what exactly it was supposed to do ie. how it was different from the last piece of code.

Shortly after I thought the code had finished, I went in to edit it and found that I could make no changes until it was reset. Thinking that maybe the code really wasn't finished (was it?), I waited a short time with no change.

I'm incredibly stumped!!