Code to check if Workbook Exists

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.