Code to check if Workbook Exists

[Siddharth Rout]

Jo15765

I am kind of confused. Your Post Title says that you want to check if a workbook exists or not and in your post you are checking for the worksheets? I believe that you might be checking for worksheets...

There are lot of things in your code that I want to refer to. So let's go through them one by one

In my below code, I am checking for both the Workbook and WorkSheet if they exists or not.

1) You don't need to open the workbook to check if the file exists or not.

2) You are trying to open a file without specifying the path and the extension?

3) When Saving File you have to specify the Path, Newname, File Extension and then FileFormat. (Especially if you are using Excel 2007 onwards)

4) I have not covered error handling. What I would recommend is having a look at this link.

Topic: To ‘Err’ is Human
Link: http://siddharthrout.wordpress.com/2...-err-is-human/

I have commented the code thoroughly so you should not have a problem in understanding it.

Option Explicit

'~~> Change File Path of the workbook Here
Const FilePath As String = "C:\Temp\"
'~~> Change File Extenstion of the workbook here
Const FileExt As String = ".xls"
'~~> File Which you have to open
Const FileName As String = "Temp"

'~~> THE ABOVE WILL BE USED IF YOU ARE ACTUALLY DOING A SAVEAS

Dim Wb As Workbook
Dim ws As Worksheet

Public Sub One()
    Dim varStar, varStars

    varStars = Array("Rain", "Sleet", "Snow")

    For Each varStar In varStars
        Call Precipitation(varStar)
        Call Monthly(varStar)
        Call Weekly(varStar)
        Call Yearly(varStar)
    Next varStar
End Sub

Public Sub Monthly(varStar)
    Dim NewName As String
    Dim Filefrmt As Long
    
    '~~> New Name for the File (Will be required if you are doing a SAVE AS
    NewName = "NewRain"
    
    '~~> Check if the workbook exists
    If FileExists(FilePath & FileName & FileExt) Then
        '~~> If exists, open it
        Set Wb = Workbooks.Open(FilePath & FileName & FileExt)
        '~~> Check if Sheet exists
        If SheetExists(varStar) Then
            Run "RefreshOnOpen"
            
            '~~> Comment the below 6 lines if you are not doing a SAVE AS
            Select Case FileExt
            Case ".xls": Filefrmt = 56
            Case ".xlsx": Filefrmt = 51
            Case ".xlsm": Filefrmt = 52
            Case ".xlsb": Filefrmt = 50
            End Select
            
            With Wb
                .SaveAs FilePath & NewName & FileExt, Filefrmt
                .Close SaveChanges:=False
                
                '---------------            NOTE            ---------------
                '~~> If you are just trying to save the existing file then you do not need a SaveAs
                '~~> Comment the above two lines and uncomment the below
                
                '.Close SaveChanges:=True
            End With
        Else '<~~ Sheet Not Found
            '~~> Do What ever you want to do here when the sheet is not found
        End If
    Else
        '~~> Do What ever you want to do here when the workbook is not found
    End If
End Sub

'~~> Function to check if File exists
Public Function FileExists(strFullPath As String) As Boolean
    On Error GoTo Whoa
    If Not Dir(strFullPath, vbDirectory) = vbNullString _
    Then FileFolderExists = True
Whoa:
    On Error GoTo 0
End Function

'~~> Function to check if sheet exists
Function SheetExists(wst As String) As Boolean
    Dim oSheet As Worksheet
    On Error Resume Next
    Set oSheet = Sheets(wst)
    On Error GoTo 0

    If Not oSheet Is Nothing Then SheetExists = True
End Function

HTH

Sid