Code to check if Workbook Exists

I have (in Excel) code similar to this:

Code:

---


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.

Try this...

vb Code:

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

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

probably in or before refeshonopen

Quote:

---

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)

Quote:

---

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

vb Code:

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

I modified my code to:

Code:

---

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?

Quote:

---

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

---

from my previous post

an empty string is not a valid filename

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.

Code:

---

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

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...

Code:

---

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

---

Just delete that part Starting from "else" :)

Code:

---

    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

---

On this line:

Code:

---

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

---

I am receiving a ByRef argument type mismatch?

Yeah, I missed that...

Change this line

Code:

---

Function SheetExists(wst As String) As Boolean

---

to

Code:

---

Function SheetExists(wst) As Boolean

---

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.

You can zip the workbook and upload that :)

Good grief and of course I overlook the obvious!

Jo15765

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

Code:

---

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

---

It should be

Code:

---

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.

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?

Ah ok :)

The there is no need for that complicated code :)

Let me give you the updated code shortly...

Sid

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

Sid

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.

We will come to Step Two in a moment ;)

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

Code:

---

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

---

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?

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

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

Code:

---

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.

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?

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

I have Fixed the code above :)

Quote:

---

Originally Posted by 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?

---

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

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.

Which Excel version are you using?

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

Ok try this line instead

Code:

---

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

---

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

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

Sid

Yes, we are ready for Step 2!!! :)

Step 2

Try this

Code:

---

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.

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.

Ah yes. One moment.

Here are STEP 2 and 3 together :)

Code:

---

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

---

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

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.

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

Now since I have given you all the 3 codes, Show me the complete code that you are using now.

Sid

This is my complete code:

Code:

---

Option Explicit
Public Sub Testing()
    Dim WB As Excel.Workbook
    Dim varBook, varBooks
   
    varBooks = Array("Rain", "Sleet", "Snow")
   
    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 WB As Excel.Workbook
    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 = ""

    strPathArr(1) = sPath & varBook & "Templates\"
    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"
            WB.Close SaveChanges:=False
        End If
    Next varWorksheet
End Sub
Public Sub Step_Two(varBook)
    Dim WB As Excel.Workbook
    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)
Dim WB As Excel.Workbook
    If FileExists("C:\Ready\Daily\" & varBook & ".xls") Then
        Set WB = Workbooks.Open(Filename:="C:\Ready\Daily\" & varBook & ".xls")
        '~~> Replace NEWNAME with the name you want to save as
        WB.SaveAs Filename:="Z:\Ready\" & ActiveWorkbook.Name & ".xls"
        WB.Close SaveChanges:=False
        Set WB = Nothing
    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 FileExists = True
Whoa:
    On Error GoTo 0
End Function

---

Do you know how to step through the code?

I know you just press F8, but I don't know what to look for when doing that.

Ok in Code Combine, go to the line Call Step_One(varBook) and press F9. That entire line will be highlighted in BROWN. Now run Combine() by pressing F5. The code will stop on that line and then you can keep pressing F8 to see how is the code progressing. This would take some time but then you will know which line is executing and which doesn't :)

Give it a try.

And this will show me what sequence the commands are running in, correct?

Absolutely :)

I get this error:

Run-Time error '-2147221080 (800401a8)':

Automation Eror

In Step_Two this line:

Code:

---

wb2.Close SaveChanges:=False

---

Hmm that is strange. You don't get that error when you don't step through right?

Correct. Only when i am stepping through the code does that error display.

Ok this time instead of highlighting Call Step_One(varBook), now highlight the line End If which is right after

wb2.Close SaveChanges:=False

by pressing F9 and try again. Also un-highlight the line Call Step_One(varBook)

Stepping through it does not produce the error anymore, and appears to execute as required, but all expected output is not produced, when the code is run.? I'm really confused as to how it would step through okay, but when actually run it doesn't...

Try and run all the steps individually and then check it.

If each module is run individually they will run with no issues. It's combining them, that causes the issues.