CODEWITHSUNDEEP
excelvba
DonH
DonH

Reputation: 3

How can I delete multiple blank .csv files?

I have some VBA code in excel 2010 that imports multiple .csv files in to one excel workbook however, sometimes there is a rogue file that contains nothing and has a file size of zero that throws up an error, I then have to manually go to the folder and delete this and run my macro again. Therefore I'm after some help that will allow me to check the file size's of all .csv file contained in a folder and delete any that are zero before I import them. Is there a way I can do this? Or possibly another suggested method that would help? I'm very new to VBA so please be patient if I don't fully understand straight away.

I have looked into FileLen(C:\Test\test.csv) = 0 Then Kill said file. But this only deletes specifically the file mentioned. I would rather check all file sizes and if any are zero, kill them.

Upvotes: 0

Views: 179

Answers (3)

DonH
DonH

Reputation: 3

Thanks both for your speedy responses, I managed to eventually get my loop going jiggling Andy's sample. I'd already started dabbling with his idea before Ron responded so continued with it. I do however like the idea of the user being asked first whether or not they would like to delete the file, so I think I'll have a stab at Ron's suggestion too and maybe learn something along the way. Thanks again.

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

You can use the FileSystemObject to check files in a folder, and delete them if they are of the correct type and size = 0.

Be very careful running this as the deletions will be permanent (not recoverable).

Option Explicit
'Set reference to Microsoft Scripting Runtime
Sub terfuge()
    Dim FSO As FileSystemObject, FI As File, FIs As Files, FO As Folder
    Const strBasePath As String = "full_path_of_folder_to_search"
    Dim bMsg As Integer

Set FSO = New FileSystemObject
Set FO = FSO.GetFolder(strBasePath)
Set FIs = FO.Files

For Each FI In FIs
    If FI.Name Like "*.csv" Then
        If FI.Size = 0 Then
            bMsg = MsgBox(Prompt:="Are you sure you want to delete " & FI.Name & "?", Buttons:=vbYesNoCancel)
            Select Case bMsg
                Case vbYes
                    FI.Delete
                Case vbCancel
                    Exit Sub
            End Select
        End If
    End If
Next FI

End Sub

Upvotes: 1

AndyW
AndyW

Reputation: 430

You can use the DIR command to search through all files in a folder then kill any with a file size of 0. The code below searches through a specified folder and lists the *.xls filenames in a sheet called Main. It should be easy to adapt this and combine with your own Kill command.

Sub Directory()
    Dim strPath As String
    Dim strFolderPath As String
    Dim strFileName As String
    Dim intRow As Integer
    Dim intColumn As Integer
    Dim intNumber As Integer

    intRow = 1
    intColumn = 1
    intNumber = 0

    strFolderPath = "h:\excel\*.xls" 'search through H drive excel folder
    strFileName = Dir(strFolderPath) 'Get name of first file
    Do
        Sheets("Main").Cells(intRow, intColumn) = strFileName 'write filename
        strFileName = Dir  'get next filename
        intRow = intRow + 1
    Loop Until strFileName = ""

End Sub

Upvotes: 0

Related Questions