CODEWITHSUNDEEP
vbaexcel
Nimy Alex
Nimy Alex

Reputation: 175

VBA function to check if a workbook exists

I have two workbooks 1: Source.xlsx and 2. Destination.xlsx. If the #2 exists in the same location as that of #1, then return the workbook to my main function, else create a new workbook and return it. I have the following code:

Function CheckForExistingWorkbooks() As Workbook
Dim wb1 As Workbook
Dim FilePath As String
Dim TestStr As String

FilePath = ThisWorkbook.Path & "\Student Information.xlsx"

TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
    Set wb1 = Workbooks.Add
Else
    Set wb1 = Workbooks.Open(FilePath)
End If

CheckForExistingWorkbooks = wb1
End Function

On debugging, the function returns "Nothing". How do I get this to work?

Upvotes: 2

Views: 3064

Answers (1)

Chris Melville
Chris Melville

Reputation: 1518

Try this - without needing error detection:

Function CheckForExistingWorkbooks() As Workbook
    Dim wb1 as Workbook
    Dim TestStr As String, FilePath As String
    FilePath = ThisWorkbook.Path & "\Student Information.xlsx"
    If Len(Dir(FilePath)) = 0 Then
        Set wb1 = Workbooks.Add
    Else
        Set wb1 = Workbooks.Open(FilePath)
    End If
    Set CheckForExistingWorkbooks = wb1
End Function

Upvotes: 3

Related Questions