- #MS ACCESS RUNTIME ERROR 70 PERMISSION DENIED DELETEFILE ARCHIVE#
- #MS ACCESS RUNTIME ERROR 70 PERMISSION DENIED DELETEFILE CODE#
'This step is first to minimize time between checking if this file is open and attempting to delete itĬall FileCopy(listFilePath, listFileName, listArchiveName, listArchivePath) Set fso = CreateObject("Scripting.FileSystemObject") , vbQuestion + vbYesNo + vbDefaultButton2 _
If FileExists(exportFile) = False Then 'If export file already exists, query export will fail.Īnswer = MsgBox("The exported file already exists. ListArchive = listArchivePath & listArchiveName ListArchiveName = Left(listFileName, Len(listFileName) - 5) & " " & FileDate & ".xlsm"
#MS ACCESS RUNTIME ERROR 70 PERMISSION DENIED DELETEFILE ARCHIVE#
ListArchivePath = "**server archive location**"įileDate = Format(Now, "yyyy-mm-dd hh-mm-ss") ListFileLocal = templateFilePath & listFileName ListFileName = "UNIT AVAILABILITY LIST.xlsm" Templatefile = templateFilePath & templateFileName ProcCheck = 0 'Each positive check incrementsĮxportFile = exportFilePath & exportFileName Columns(i).ColumnWidth + 2Īnd Here is the main button click event code:ĭim exportFileName As String, exportFilePath As String, exportFile As Stringĭim templateFileName As String, templateFilePath As String, templatefile As Stringĭim listFileName As String, listFilePath As String, listFile As String, listFileLocal As Stringĭim listArchivePath As String, listArchiveName As String, listArchive As String 'Format alternating row shading, column borders and column widths for new range of data = 'Set template data equal to destination dataĮ = FalseĮ = True Set rngTemplate = wsTemplate.Range(wsTemplate.Cells(2, 1), wsTemplate.Cells(lastrow, lastcol)) 'Set template data range Set rngDestination = wsDestination.Range(wsDestination.Cells(2, 1), wsDestination.Cells(lastrow, lastcol)) 'Set destination data range Set wsDestination = wbTemplate.Worksheets() 'Set New List Destination Sheet WsSource.Copy after:=wbTemplate.Worksheets() 'Copy source worksheet to end of template copy Set wsSource = wbSource.Worksheets(1) 'Set source workbook sheet Set wsTemplate = wbTemplate.Worksheets(1) 'Set Template sheet Set wbTemplate = (Template) 'Open Template copy Set wbSource = (Source) 'Open Source workbook Set excelApp = CreateObject("Excel.Application") 'Transfer data from export source to copy of template file
'Coded using late binding so instances are defined as objects and excel specific types are enumeratedĭim wbSource As Object, wbTemplate As Objectĭim wsDestination As Object, wsSource As Object, wsTemplate As Objectĭim rngDestination As Object, rngTemplate As Object Public Sub FormatList(Source As String, Template As String, SheetName As String)
#MS ACCESS RUNTIME ERROR 70 PERMISSION DENIED DELETEFILE CODE#
Did my putting some of this fso object in a PUBLIC module sub do something slightly different or necessitate a more specific closing of an instance (that's not in my code) that is handled when the code is in a PRIVATE button click sub? That was a long way of asking, what gives? Any ideas on what I did or what happened that caused there to be a file that was unable to be deleted by Access?Įdit: To throw a possible start to a direction of a conversation. I've rerun the code again a handful of times and the delete now functions just fine. Rerun the code and voila! No permission error. I finally decide it has to be a file issue that I can't figure out. Check folder permissions, file permissions, etc, no issues stand out. Can't see why it would be giving me a permission error. I throw in the towel, move my code back to the way it was. One of the errors that occurred was an Error 70 - Permission Denied after creating an archive of the existing list (copying to a separate folder) and trying to delete the current list in preparation for generating a new list. This may be irrelevant but I'm providing background just in case it is. So, I wasn't terribly smart and didn't save off a copy of my working code/db before trying to move it from a button call to a module. Long story short, I got some errors and decided the cost/benefit analysis didn't work. For half a second I got it into my head to turn the main code itself into a standalone sub so it can be called and used to export a variety of list queries we would be using. I ran it half a dozen times without issue. I got some query export to spreadsheet code working (see my other thread.lol).