Delete File Permission Error

[ExcelMacLean]

Hello - I'm looking for help with this Permissions error.

The Scenario: We are sent hundreds of .txt files emailed to us in .zip format which must each be converted to an Excel. The .zip files can contain any number of .txt files, and each .txt file can contain any number of columns. All data must be in text format (due to leading zeros). I've created some basic automation to help with this .zip --> .txt --> Excel format to save users time. I think I've solved most of the issues, but I'm not proficient with FileSystemObject commands, and I'm not certain what I'm doing wrong. I do not take credit for most the code here, its an amalgamation of code from across the web. The only part I came up with was the logic to read the first row from the .txt file to determine how many columns we need to convert to Text format to create the ColArray for the workbook.openText FieldInfo:=ColArray.

The Error: all the .txt files are extracted to a folder, then the code loops through each file in the folder and creates a new excel workbook from each (this part works). After I've created the excel workbook I want to erase (Kill) the .txt file. This is when the Permission Error occurs. The "MyUnzippedFiles" folder can stay until its needed next time, or deleted, I dont care. I tried deleting the entire thing but got the same error. Is this because excel thinks the .txt is actually the new open workbook? Arent the .txt file and the new workbook different files? I would have thought once the Stream is closed the .txt file can safely be deleted?

---

Option Explicit 

Sub ExcelImport(ZipFileLocation As String)
 
    Dim FSOText As FileSystemObject
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String
    Dim fileNameInZip As Variant
    Dim FileName As String
    Dim FirstRowString As String
    Dim RowCount As Double, ColCount As Double
    Dim Stream As TextStream
    Dim x As Double
    Dim ColArray() As Variant
    Dim FileCount As Integer 'counter how many .txt files are extracted.
    Dim FileNameLoop As String
 
'    Fname = Application.GetOpenFilename(<wbr>filefilter:="Zip Files (*.zip), *.zip", _
'                                        <wbr>MultiSelect:=False)
    Fname = ZipFileLocation
 
    If Fname = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If
 
        'Create the folder name
        'strDate = Format(Now, " dd-mm-yy h-mm-ss")
        'FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"
        FileNameFolder = DefPath & "MyUnzipFolder" & "\"
 
        'Make the normal folder in DefPath
        If Dir(FileNameFolder, vbDirectory) = "" Then
            MkDir FileNameFolder
        End If
 
        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.<wbr>Application")
 
        'Change this "*.txt" to extract the files you want
        For Each fileNameInZip In oApp.Namespace(Fname).Items
            If LCase(fileNameInZip) Like LCase("*.txt") Then
                oApp.Namespace(FileNameFolder)<wbr>.CopyHere _
                        oApp.Namespace(Fname).Items.<wbr>Item(CStr(fileNameInZip))
                FileName = FileNameFolder & fileNameInZip
            End If
        Next
       
        'Now all the .txt files have been extracted to the temp folder
        'loop through them and open them all up as excels.
        FileNameLoop = Dir(FileNameFolder)
       
        'Debug
        Debug.Print FileNameLoop
       
        Do While FileNameLoop <> ""
          
            FileName = FileNameFolder & FileNameLoop
            Debug.Print FileName
          
            'We need to open the .txt file to see how many columns it has.
            'FreeFile is a built-in VBA function that finds the next available windows filenumber
            Set FSOText = New FileSystemObject
            Set Stream = FSOText.OpenTextFile(FileName, ForReading, False)
           
            'Read the first line only, to find out how many columns there are in the data.
            FirstRowString = Stream.ReadLine
           
            'Clost the Stream
            Stream.Close
           
            'Count the columns
            ColCount = Len(FirstRowString) - Len(Replace(FirstRowString, vbTab, "")) + 1
       
            'Create the 2-d Text Arrays for the Workbooks.openText method.
            ReDim ColArray(1 To ColCount, 1 To 2)
       
            'The 2-d array will tell the Workbooks.openText method how to format the incoming data.
            For x = 1 To ColCount
                ColArray(x, 1) = x 'All columns are to be imported.
                ColArray(x, 2) = 2 '2 is for Text Format
            Next x
       
            'Open the text file in excel as all text format.
            Workbooks.OpenText FileName:=FileName, _
            DataType:=xlDelimited, Tab:=True, FieldInfo:=ColArray
           
            'Would saving ActiveWorkbook.SaveAs "Test.xlsx" here allow me to delete the .txt?
       
            'give excel a chance to do this
            DoEvents
       
            'now that we are done with this .txt file, delete it.
            Kill FileName 'ERROR Permission Denied - Why? Stream is closed. Because the excel is open?
 
            'Because ColArray has to be redimmentioned each time
            'there is a new file, we have to erase it after use
            Erase ColArray
           
            'Go to the next File
            FileNameLoop = Dir
       
        Loop
       
        'what does this do, exaclty?
        'What is the Temporary Directory? Can I just use this instead of the MyUnzipFolder above?
        'I dont see the temp directory used above...
        On Error Resume Next
            Set FSOText = CreateObject("scripting.<wbr>filesystemobject")
            FSOText.DeleteFolder Environ("Temp") & "\Temporary Directory*", True
        On Error GoTo 0
       
    End If
   
End Sub

 

[AlphaFrog]

It's not the stream. It is because you opened the file as a workbook and it' is still open.

'Would saving ActiveWorkbook.SaveAs "Test.xlsx" here allow me to delete the .txt?

Yes. This would allow the original text file to be deleted.

Alternatively; you could copy the text file, open the copy, and delete the original.

 

[ExcelMacLean]

Thank you! Looks like I answered my own question - I should have tested it. I added a bit of logic to skip non-text files in that folder as well. Here is the final code:

Option Explicit
 
 
Sub ExcelImport(ZipFileLocation As String)
 
    Dim FSOText As FileSystemObject
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim strDate As String
    Dim fileNameInZip As Variant
    Dim FileName As String
    Dim FirstRowString As String
    Dim RowCount As Double, ColCount As Double
    Dim Stream As TextStream
    Dim x As Double
    Dim ColArray() As Variant
    Dim FileCount As Integer 'counter how many .txt files are extracted.
    Dim FileNameLoop As String
    Dim NewExcelFileName As String
 
'    Fname = Application.GetOpenFilename(<wbr>filefilter:="Zip Files (*.zip), *.zip", _
'                             <wbr>           MultiSelect:=False)
    Fname = ZipFileLocation
 
    If Fname = False Then
        'Do nothing
    Else
        'Root folder for the new folder.
        'You can also use DefPath = "C:\Users\Ron\test\"
        DefPath = Application.DefaultFilePath
        If Right(DefPath, 1) <> "\" Then
            DefPath = DefPath & "\"
        End If
 
        'Create the folder name
        'strDate = Format(Now, " dd-mm-yy h-mm-ss")
        'FileNameFolder = DefPath & "MyUnzipFolder " & strDate & "\"
        FileNameFolder = DefPath & "MyUnzipFolder" & "\"
 
        'Make the normal folder in DefPath
        If Dir(FileNameFolder, vbDirectory) = "" Then
            MkDir FileNameFolder
        End If
 
        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.<wbr>Application")
 
        'Change this "*.txt" to extract the files you want
        For Each fileNameInZip In oApp.Namespace(Fname).Items
            If LCase(fileNameInZip) Like LCase("*.txt") Then
                oApp.Namespace(FileNameFolder)<wbr>.CopyHere _
                        oApp.Namespace(Fname).Items.<wbr>Item(CStr(fileNameInZip))
                FileName = FileNameFolder & fileNameInZip
            End If
        Next
       
        'Now all the .txt files have been extracted to the temp folder
        'loop through them and open them all up as excels.
        FileNameLoop = Dir(FileNameFolder)
       
        'Debug
        Debug.Print FileNameLoop
       
        'Turn Off Display alerts for file saves
        Application.DisplayAlerts = False
       
        Do While FileNameLoop <> ""
       
            If Right(FileNameLoop, 4) <> ".txt" Then
                GoTo NextFile
            End If
          
            FileName = FileNameFolder & FileNameLoop
            Debug.Print FileName
          
            'We need to open the .txt file to see how many columns it has.
            'FreeFile is a built-in VBA function that finds the next available windows filenumber
            Set FSOText = New FileSystemObject
            Set Stream = FSOText.OpenTextFile(FileName, ForReading, False)
           
            'Read the first line only, to find out how many columns there are in the data.
            FirstRowString = Stream.ReadLine
           
            'Clost the Stream
            Stream.Close
           
            'Count the columns
            ColCount = Len(FirstRowString) - Len(Replace(FirstRowString, vbTab, "")) + 1
       
            'Create the 2-d Text Arrays for the Workbooks.openText method.
            ReDim ColArray(1 To ColCount, 1 To 2)
       
            'The 2-d array will tell the Workbooks.openText method how to format the incoming data.
            For x = 1 To ColCount
                ColArray(x, 1) = x 'All columns are to be imported.
                ColArray(x, 2) = 2 '2 is for Text Format
            Next x
       
            'Open the text file in excel as all text format.
            Workbooks.OpenText FileName:=FileName, _
            DataType:=xlDelimited, Tab:=True, FieldInfo:=ColArray
           
            'Save the file with a differnet name
            NewExcelFileName = Replace(FileName, ".txt", ".xlsx")
            ActiveWorkbook.SaveAs NewExcelFileName
 
            'Would saving ActiveWorkbook.SaveAs "Test.xlsx" here allow me to delete the .txt?
       
            'give excel a chance to do this
            DoEvents
       
            'now that we are done with this .txt file, delete it.
            Kill FileName 'ERROR Permission Denied - Why? Stream is closed. Because the excel is open?
 
            'Because ColArray has to be redimmentioned each time
            'there is a new file, we have to erase it after use
            Erase ColArray
           
NextFile:
 
            'Go to the next File
            FileNameLoop = Dir
       
        Loop
       
        'what does this do, exaclty?
        'What is the Temporary Directory? Can I just use this instead of the MyUnzipFolder above?
        'I dont see the temp directory used above...
        On Error Resume Next
            Set FSOText = CreateObject("scripting.<wbr>filesystemobject")
            FSOText.DeleteFolder Environ("Temp") & "\Temporary Directory*", True
        On Error GoTo 0
       
    End If
   
    'Display Alerts Reset
    Application.DisplayAlerts = True
               
End Sub