Code to check if Workbook Exists

[Jo15765]

I have (in Excel) code similar to this:

Public Sub One()
Dim varStar
Dim varStars

varStar = 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)
        On Error Resume Next
        Set WB = Workbooks.Open(Filename:="" & varStar)
        Run "RefreshOnOpen"
        With WB
            WB.SaveAs Filename:="" 
        End With
        WB.Close
        On Error GoTo 0
End Sub

And then from there it will call the corresponding module and pass varStar to each module and run the code. The issue I have is that sometimes (like right now) it is a new year, or a new month and varStar may not exist in the specific workbook. How can I code it to, if varStar doesn't exist just skip it and keep going. I tried On Error Resume Next, and On Error GoTo 0. But that wasn't 100% efficient. If you need to see additional coding, let me know, and I can provide more.

[Hack]

Try this...

Private Function DoesWorkSheetExist(pstrWSName As String) As Boolean
Dim i As Long
 
For i = 1 To ActiveWorkbook.Sheets.Count
    If UCase(pstrWSName) = UCase(ActiveWorkbook.Sheets(i).Name) Then
        DoesWorkSheetExist = True
        Exit For
    End If
Next 
End Function

[Jo15765]

Where in my coding would I put that function? I am still learning VBA, and thank you for the prompt response!

[westconn1]

probably in or before refeshonopen

With WB
WB.SaveAs Filename:=""
End With

you can not saveAs with no valid filename, error will occur, also the with block here is not used at all (with and end with can be totally removed)

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

For each varStar in varStars

the array should be named varstars, so you can loop through it, otherwise it should be for each somevar in varstar

another method to deal with sheet missing

on error resume next    ' start inline error handling
set sht = sheets(varstar)
 
if not err.number = 0 then  ' an error occured
' create new sheet
end if
on error goto 0   ' turn off inline error handling

[Jo15765]

I modified my code to:

Public Sub Monthly (varStar)
        On Error Resume Next
        Set WB = Workbooks.Open(Filename:="" & varStar)
        Call DoesWorkSheetExist       
        Run "RefreshOnOpen"
        With WB
            WB.SaveAs Filename:="" 
        End With
        WB.Close
        On Error GoTo 0
End Sub

I get a debug error of Argument not optional on the function posted here? Did I place it in the wrong place or leave out something?

[westconn1]

you can not saveAs with no valid filename, error will occur,

from my previous post

an empty string is not a valid filename

[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

[Jo15765]

koolsid that above code looks like it will work for my scenario. Thank you for providing it! The workbook to open and the fileName to saveAs I just left blank because I wasn't sure the exact location of where they were going to be housed. But thank you and westconn1 for pointing out that issue, sometimes it is the simplest things that cause code to fail.

Koolsid one question about your code you provided at the end of the module (I added two commented out lines with a question...

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
	'What I want to do here is if the workbook/worksheet is non existent then continue to cycle 
	'through the Array.  Meaning that if Rain doesn't exist, it's okay, just move on to Sleet
	'What would I need to put here to make it continue on to the next procedure if it's non-existent
    End If
End Sub

[Siddharth Rout]

Just delete that part Starting from "else"

    Else
        '~~> Do What ever you want to do here when the workbook is not found
	'What I want to do here is if the workbook/worksheet is non existent then continue to cycle 
	'through the Array.  Meaning that if Rain doesn't exist, it's okay, just move on to Sleet
	'What would I need to put here to make it continue on to the next procedure if it's non-existent

[Jo15765]

On this line:

        '~~> Check if Sheet exists
        If SheetExists(varStar) Then
            Run "RefreshOnOpen"

I am receiving a ByRef argument type mismatch?

[Siddharth Rout]

Yeah, I missed that...

Change this line

Function SheetExists(wst As String) As Boolean

to

Function SheetExists(wst) As Boolean

[Jo15765]

I changed that line of code and it took away the compile error. But I am still running into the same issue I was having before. The code I posted is just a smaller portion of a larger project and it will open a workbook that contains about 500 worksheets, if the worksheet doesn't exist the code begins to spool the entire workbook for conversion to pdf! Maybe my coding error is in another portion of the code. I tried to upload a sample workbook, but it said it was an invalid format...so I am going to attach a txt file with my coding included to see if one of the guru's here can show me the error in my ways.

[Siddharth Rout]

You can zip the workbook and upload that

[Jo15765]

Good grief and of course I overlook the obvious!

[Siddharth Rout]

Jo15765

You still are making the same mistake in Sub Combine()

varBook = Array("Fire", "Ice")

It should be

varBooks = Array("Fire", "Ice")

Forget the rest of the code for a moment. Could you explain in simple terms what Step_One(varBook) is supposed to do.

[Jo15765]

Here is a breakdown:
Step_One

Searches the two strPathArr locations because the file is ALWAYS in one of the two locations (depending on the report it could be more than two tho) and will search both of the locations for the file. When the file is found it will open the file, Refresh the 2 queries inside of the file then save the file in the specified locatoin and with the specified name

Step_Two

Opens the specified workbook and searches each tab inside of the workbook for the worksheet named varBook, when varBook is found it will then copy that worksheet (varBook) to a new workbook then save in the specified location and with teh specified filename

Step_Three

Opens the specified file, then refreshes all the queries in the workbook, then saves the new file in the specified location and with the specified name.

Does that clarify what I am doing in each Step?

[Siddharth Rout]

Ah ok

The there is no need for that complicated code

Let me give you the updated code shortly...

Sid

[Siddharth Rout]

Quick question. What happens when it finds the file in both the locations? Does it open both the files?

Sid

[Jo15765]

The file will only be in one or the other.

Also, let me add (i hadn't updated my code yet) that in step 2, once the worksheet is copied to a new workbook, any highlighting that is on the sheet needs to be removed.

[Siddharth Rout]

We will come to Step Two in a moment

Could you test this code for me? This is only for Step one.

Option Explicit

Dim WB As Excel.Workbook

Public Sub Combine()
    Dim varBook, varBooks
    
    varBooks = Array("Fire", "Ice")
    
    For Each varBook In varBooks
        Call Step_One(varBook)
        'Call Step_Two(varBook)
        'Call Step_Three(varBook)
    Next varBook
End Sub

Public Sub Step_One(varBook)
    Dim varWorksheets, varWorksheet
    Dim fileName1 As String, fileName2 As String, fileName3 As String
    Dim sPath As String, FileToUse As String
    Dim strPathArr(1 To 2) As String
    Dim wks As Worksheet, qt As QueryTable
    
    sPath = "C:\Reporting Folder\"
    
    strPathArr(1) = sPath & varBook & "Review\"
    strPathArr(2) = sPath & varBook & "To_Review\"
    
    fileName1 = "_Monthly.xls"
    fileName2 = "_Quarterly.xls"
    fileName3 = "_Daily.xls"
    
    varWorksheets = Array(fileName1, fileName2, fileName3)

    For Each varWorksheet In varWorksheets
        If FileExists(strPathArr(1) & "\" & varBook & varWorksheet) Then
            FileToUse = strPathArr(1) & "\" & varBook & varWorksheet
        ElseIf FileExists(strPathArr(2) & "\" & varBook & varWorksheet) Then
            FileToUse = strPathArr(2) & "\" & varBook & varWorksheet
        End If
        
        If Len(Trim(FileToUse)) <> 0 Then
            Set WB = Workbooks.Open(FileToUse)
            For Each wks In WB.Worksheets
                For Each qt In wks.QueryTables
                    qt.Refresh BackgroundQuery:=False
                Next qt
            Next wks
            Set qt = Nothing
            Set wks = Nothing

            WB.SaveAs "Z:\Ready\" & VBA.Left(ActiveWorkbook.Name, _
            VBA.InStrRev(WB.Name, ".") - 1) & "_" & VBA.Format(Date, "mmddyyyy") & ".xls", 56
                
            WB.Close SaveChanges:=False
        End If
    Next varWorksheet
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 FileExists = True
Whoa:
    On Error GoTo 0
End Function

[Jo15765]

I am getting a compile error of Variable not defined on FileFolderExists...is that a variable I just need to Dim at the top of the Function?

[Siddharth Rout]

Jo15765

Do this.

Create a new module and paste the entire code from above once again and then try.

BTW I am not using "FileFolderExists". I am using "FileExists". For that there is a function right at the bottom of that code

Sid

[Jo15765]

I pasted the code into a new workbook just to make sure there wouldn't be any conflicts between variables etc etc from things I already had. The "FileFolderExists" I was getting the conflict on, was the FileFolderExists from the function

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

But changing it to fileExists takes the compile error away so I will try that.

[Jo15765]

When I try to execute the code via the immediate window, I get a Run Time error '1004' the Macro Combine could not be found?

[Siddharth Rout]

Ah! my mistake LOL yes it has to be "fileExists" as you rightly guessed

I have Fixed the code above

[Siddharth Rout]

When I try to execute the code via the immediate window, I get a Run Time error '1004' the Macro Combine could not be found?

keep your cursor in the code Sub Combine() and then press F5

[Jo15765]

Hey, I didn't know that handy trick! The code begins to execute as expected, but now I get a debug error on the Method Save As Object 'Workbook failed.

[Siddharth Rout]

Which Excel version are you using?

[Jo15765]

2000 --- If I remove the ,56 from the End of the SaveAs statement it executes to perfection!

[Siddharth Rout]

Ok try this line instead

            WB.SaveAs "Z:\Ready\" & VBA.Left(ActiveWorkbook.Name, _
            VBA.InStrRev(WB.Name, ".") - 1) & "_" & VBA.Format(Date, "mmddyyyy") & ".xls"

[Jo15765]

We must have posted about the same time, I edited my above post...that works perfectly w/o the ,53 @ the end.

[Siddharth Rout]

So Step one is working fine now? If yes then we can move on to Step 2

Sid

[Jo15765]

Yes, we are ready for Step 2!!!

[Siddharth Rout]

Step 2

Try this

Public Sub Step_Two(varBook)
    Dim ws As Worksheet
    
    If FileExists("c:\Main_Master.xls") Then
        Set WB = Workbooks.Open(Filename:="c:\Main_Master.xls")
    
        On Error Resume Next
        Set ws = WB.Sheets(varBook)
        On Error GoTo 0
    
        If Not ws Is Nothing Then
            ws.Copy
            WB.SaveAs Filename:="Z:\Ready\" & ws.Name
        End If
    End If
End Sub

Edit: Remember to remove the Single Quote " ' " before Call Step_Two(varBook) in Sub Combine.

[Jo15765]

That code works as well. One tweak if possible, it's not closing the workbook. It is closing the workbook that we opened to copy the worksheet from, but it is not closing the workbook that we copied the single sheet into.

[Siddharth Rout]

Ah yes. One moment.

[Siddharth Rout]

Here are STEP 2 and 3 together

Public Sub Step_Two(varBook)
    Dim ws As Worksheet
    Dim wb2 As Workbook
    
    If FileExists("c:\Main_Master.xls") Then
        Set WB = Workbooks.Open(Filename:="c:\Main_Master.xls")
        
        On Error Resume Next
        Set ws = WB.Sheets(varBook)
        On Error GoTo 0
    
        If Not ws Is Nothing Then
            ws.Copy
            Set wb2 = ActiveWorkbook
            wb2.SaveAs Filename:="Z:\Ready\" & ws.Name & ".xls"
            wb2.Close SaveChanges:=False
            WB.Close SaveChanges:=False
            Set ws = Nothing
            Set wb2 = Nothing
            Set WB = Nothing
        End If
    End If
End Sub

Public Sub Step_Three(varBook)
    If FileExists("C:\Ready\Daily\" & varBook & ".xls") Then
        Set WB = Workbooks.Open(Filename:="C:\Ready\Daily\" & varBook & ".xls")
        Run "RefreshOnOpen"
        '~~> Replace NEWNAME with the name you want to save as
        WB.SaveAs Filename:="Z:\Ready\" & "NEWNAME" & ".xls"
        WB.Close SaveChanges:=False
        Set WB = Nothing
    End If
End Sub

[Jo15765]

It's still not closing the "source" workbook, but it is closing the one we copy to, in Step Two

[Siddharth Rout]

Strange. I just tested it again and it works just fine...

Just do one thing. In Combine() comment Step 1 and 3 and then test it.

[Jo15765]

It looks like I had left off some of the code. That is now working, however it looks like if the varBook does not exist in Step_One it automatically cycles to the next varBook as opposed to checking Step_Two/Step_Three for the varBOok