Delete CSV files if empty in folder

davidp13

New Member
Joined
Jun 7, 2011
Messages
25
Hi. I have been trying for a while now to delete csv files in a directory if these are empty/blank. Basically I have a routine that updates multiple csv files in a directory and within certain csv files i will delete all the values as i dont need to use them, but some files will be left with content. The ones with empty value should be deleted.

The best I get right at the moment is to delete all the files.

Any help/guidance pls?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I wonder how the updates are done.

If via code, can you tweak the code that updates the files to instead read from the files? And the ones with no records are then deleted.
Or otherwise a SELECT query against each file, and those that return zero records get deleted.
 
Upvote 0
I have tried this, but it deletes all the files.

Code:
Public Sub Setup3()'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\*.csv")
Do While MyFile <> ""
    
    ActiveSheet.Range("A1").Select
    'Sheets(1).Select.Range("A:A").Select
    'If ActiveSheet.Range("A1").SpecialCells(xlCellTypeConstants, xlTextValues).Cells.Count = 0 Then
    
        If (ActiveSheet.Range("A1").Value < 1) Then
           Kill "C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\" & MyFile


        End If
       
    'need to specify full path again because a file was deleted 1
    MyFile = Dir$("C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\*.csv")
Loop
End Sub
 
Upvote 0
Please back up files before running the code. And maybe run on some test files first.

Decision on whether to delete a file is based solely on cell A1 being empty or not. If this isn't good for you, please modify or advise what would be a better check.
Code:
Sub trythis()

    Const sFILE_PATH_TO_CHECK As String = "C:\Users\admin\Google Drive\Everest\MSA\DailyMSA - New"


    Dim sFileName As String
    Dim sFileFullName As String
    
    Application.ScreenUpdating = False
    sFileName = Dir(sFILE_PATH_TO_CHECK & "\*.csv")
    Do While Len(sFileName)
        sFileFullName = sFILE_PATH_TO_CHECK & "\" & sFileName
        If bFileIsEmptyInFirstCell(FileName:=sFileFullName) Then Kill sFileFullName
        sFileName = Dir()
    Loop
    
End Sub


Private Function bFileIsEmptyInFirstCell(ByVal FileName As String) As Boolean


    'just opens file and checks if first cell is empty
    
    Dim wbkFileToCheck As Excel.Workbook


    Set wbkFileToCheck = Workbooks.Open(FileName:=FileName)
    bFileIsEmptyInFirstCell = Len(Range("A1").Value) = 0
    wbkFileToCheck.Close
    Set wbkFileToCheck = Nothing
    DoEvents


End Function
 
Upvote 0
Thanks, it worked 99%. I needed to change one line in the function section and add Sheets(1). Works 100% now. See below:

Code:
bFileIsEmptyInFirstCell = Len(Sheets(1).Range("A1").Value) = 0
 
Last edited:
Upvote 0
Well done.

I 'm unsure what difference that change makes. Do some csv files have multiple worksheets?
 
Upvote 0
I'm speculating here, but it might be because the CSV file has a sheet that is named and not just Sheet1. I have noticed that I get better feedback when I specify it.
 
Upvote 0

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top