Runtime error 70 (1 Viewer)

[109bow]

I have code that copies an .xls file from a USB to a folder on my PC:
Private Sub data_backup_Click()
On Error Resume Next
Kill "C:\test\LATHE_DATA.xls"
On Error GoTo 0
MsgBox "Ensure USB is inserted"
FileCopy "\\Client\A$\GeneralUSB_sda1\LATHE_DATA.xls", "C:\test\LATHE_DATA.xls"
MsgBox "Backup Completed"
End Sub

The problem is that when I run the code I get the runtime error 70 permission denied message. Is this because the .xls file I am trying to overwrite is being used as a linked table?
If so how do I overcome this problem?
Thanking you in advance

 

[RuralGuy]

Have you single stepped the code to see which line is raising the error?

 

[sneuberg]

To find out what's going on you could change the code temporarily to copy it to a different folder or just delete the link table to the excel sheet and see what the code does. If you can't perform the copy because the excel file is linked then I guess you will have to delete the linked table and reestablish it after the copy.

 

[109bow]

Thanks for all replies. I have got round it by creating a new front page and running the code from there, then opening the form that uses the linked table.

 

[The_Doc_Man]

Is this because the .xls file I am trying to overwrite is being used as a linked table?

To answer the direct question, I would say that it COULD do that, since a linked table in a workbook opens a file and locks it. You can't delete locked files, so the Kill verb would barf. But to be honest, I would have expected a "File Locked" error, not a "Permissions" error, and they ARE different error numbers.

There is another possibility. You need MODIFY permissions on the directory in which your file is being written (and from which the old file is being deleted). THAT could easily cause you to have a permissions error.

 

[wisekat]

So have you managed to solve the problem and find the real reason why it occurs? If not, you may encounter this problem in the future.

If you get this problem because of the XLS file currently opened, I would try to close it automatically. You can attach to the running instance of Excel using OLE Automation:

set xlApp = GetObject(, "Excel.Application")

Then check whether the file is opened using the methods of xlApp and close it programmatically.