How can i check if file is empty?

user_9483957 0
Viewed 196

im using following code as excel macro - basically it goes through all folders and subfolders and saves every xls or xlsx file it finds as a pdf. This works fine, however if there is an empty file, it crashes. How can i check if file im going to convert to pdf is empty? or is there a way i can convert empty files too? im ok with either solution (i didn't create this code i found it but made a few changes to it, i don't code in VB)

Thanks for any kind of help :)

Sub BatchOpenMultiplePSTFiles()
    Dim objShell As Object
    Dim objWindowsFolder As Object
    Dim strWindowsFolder As String

    'Select the specific Windows folder
    Set objShell = CreateObject("Shell.Application")
    Set objWindowsFolder = objShell.BrowseForFolder(0, "Select a Windows folder:", 0, "")

    If Not objWindowsFolder Is Nothing Then
       strWindowsFolder = objWindowsFolder.self.Path & "\"

       Call ProcessFolders(strWindowsFolder)

       'Open the windows folder
       Shell "Explorer.exe" & " " & strWindowsFolder, vbNormalFocus
   End If
End Sub

Sub ProcessFolders(strPath As String)
    Dim objFileSystem As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim objExcelFile As Object
    Dim objWorkbook As Excel.Workbook
    Dim strWorkbookName As String
    Dim myPath As String
    

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFileSystem.GetFolder(strPath)

    For Each objFile In objFolder.Files
        strFileExtension = objFileSystem.GetExtensionName(objFile)
        If (LCase(strFileExtension) = "xls" Or LCase(strFileExtension) = "xlsx") And 1 = 1 Then
           Set objExcelFile = objFile
           Set objWorkbook = Application.Workbooks.Open(objExcelFile.Path)
           
           myPath = Replace(Replace(objFile.Path, ".xlsx", ""), ".xls", "")
           
          
           objWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & ".pdf"

           objWorkbook.Close False
        End If
    Next

    'Process all folders and subfolders
    If objFolder.SubFolders.Count > 0 Then
       For Each objSubFolder In objFolder.SubFolders
           If ((objSubFolder.Attributes And 2) = 0) And ((objSubFolder.Attributes And 4) = 0) Then
              ProcessFolders (objSubFolder.Path)
           End If
       Next
    End If
End Sub
vba excel
2 Answers

Try checking if there are values in the active sheet:

'your existing code
    Set objWorkbook = Application.Workbooks.Open(objExcelFile.Path) 'existing
        'new code lines______________
        if objWorkbook.Sheets(1).cells.count > 0 then 'supposing that in not probable to have more sheets, but the first to be empty...   
           myPath = Replace(Replace(objFile.Path, ".xlsx", ""), ".xls", "")          
           objWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & ".pdf"
        end if
        'End new code lines__________
     objWorkbook.Close False
'your existing code
user_22333080

Empty Workbook!?

Not entirely sure about the rules (assumptions) that make a workbook 'empty', but you could modify your code like this ...

Set objWorkbook = Application.Workbooks.Open(objExcelFile.Path)
If Not IsWorkbookEmpty(objWorkbook) Then
    myPath = Replace(Replace(objFile.Path, ".xlsx", ""), ".xls", "")
    objWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myPath & ".pdf"
End If
objWorkbook.Close False

... which utilizes the following function:

Function IsWorkbookEmpty(ByVal wb As Workbook) As Boolean
    
    If wb Is Nothing Then IsWorkbookEmpty = True
    
    ' Assumptions:
    ' 1. There are only workheets and/or charts in the workbook.
    ' 2. A workbook is empty if all the cells of its visible worksheets
    '    are empty and there are no visible charts.
    
    Dim sh As Object
    Dim fCell As Range
    
    For Each sh In wb.Sheets
        If sh.Visible = xlSheetVisible Then
            If sh.Type = xlWorksheet Then
                Set fCell = Nothing
                Set fCell = sh.Cells.Find("*", sh.Cells(sh.Rows.Count, _
                    sh.Columns.Count), xlFormulas, , xlByRows)
                If Not fCell Is Nothing Then
                    Exit Function
                End If
            Else
                Exit Function
            End If
        End If
    Next sh
       
    IsWorkbookEmpty = True
    
End Function
user_98140690
Related