0

I'm having a problem with my Access VBA macro which for some reason is breaking on those lines with error "Permission Denied [Run-time error 70]:

sOutputFile = CurrentProject.Path & "\Output Files\Recon\" & sDate & " " & sClient & " Cash Recon.xlsx"
sTemplateFile = CurrentProject.Path & "\Temp Files\Template_Listed.xlsx"
If Dir(sOutputFile) <> "" Then Kill sOutputFile
FileCopy sTemplateFile, sOutputFile

What's getting "pointed out" directly is the "Kill sOutputFile" phrase.

It is worth to mention, that I do not have any of the files open, I have full access to the directories, and not long ago (before declaring sOutputFile and sTemplateFile) they've been both cleared.

Any help is much appreciated and I'm willing to share more of my code if needed.

Edit: Also, from time to time, the macro goes to the next line and breaks at the FileCopy instead.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
heaton124
  • 69
  • 1
  • 11
  • 1
    Maybe your antivirus is analyzing the file while you are trying to delete it. Do you still get the same error if retry to delete it after a while? – Florent B. Jan 24 '18 at 10:35
  • 1
    I just noticed, that when I'm trying to open the file manually, a popup window shows up telling me that the file is currently open by me. No clue why. I'll try setting macro to close sOutputFile first. – heaton124 Jan 24 '18 at 10:40
  • 1
    Can you delete the file through windows explorer? – SE1986 Jan 24 '18 at 10:45
  • Take a look for some ideas here - https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open – Vityata Jan 24 '18 at 11:09
  • I can't currently delete the file at all for some reason. I'm quite busy doing some other work right now but I also can't come up with any idea of force closing it through VBA or even manually. After closing excel processes I finally were able to delete it, but that's not the thing I'd like to do on everyday basis. – heaton124 Jan 24 '18 at 12:54

2 Answers2

1

The logic is not quite right in my opinion, please try the following (FileCopy should be inside an If...End If):

sOutputFile = CurrentProject.Path & "\Output Files\Recon\" & sDate & " " & 
sClient & " Cash Recon.xlsx"
sTemplateFile = CurrentProject.Path & "\Temp Files\Template_Listed.xlsx"
If FileExists(sOutputFile) Then 
  Kill sOutputFile
  FileCopy sTemplateFile, sOutputFile
End If

And this is the FileExists function:

Public Function FileExists_1(sFileName As String) As Boolean
  Dim obj_fso As Object

  Set obj_fso = CreateObject("Scripting.FileSystemObject")
  FileExists_1 = obj_fso.fileExists(sFileName)

  Set obj_fso = Nothing
End Function
0

What was the issue in my case, was that I previously used the macro, but the full ran failed. After trying to run it again after few adjustments (not related to the code I posted here), the above issue was happening. All just because I had the file still open somewhere in my excel's memory, hence the file couldn't be deleted.

Thanks for all the contributions guys. You're amazing as always!

heaton124
  • 69
  • 1
  • 11