VBA Open Workbook from Macro fails "(path & filename) could not be found. Check the spelling..."

Question

I have the following code, which should simply open a workbook from a master workbook to allow me to paste some information which I will later retrieve from a database. I receive the error in the title every time I try to open a workbook. I have used similar code before with no problem at all. The files are kept on a server away from my desktop, but the path has been correct each time. I've checked over and over again. What could be the culprit?

Sub copyDealerDataToWorkbook(ByVal targetID As Integer)

    Dim mainWB      As Workbook    
    Dim directory   As String
    Dim fn          As String
    Dim aFile       As Excel.Application

    Set aFile = CreateObject("Excel.Application")

    Set mainWB = ActiveWorkbook
    directory = ActiveWorkbook.Path

    'append a "\"
    If InStrRev(directory, "\") < Len(directory) Then
        directory = directory & "\"
    End If

    fn = targetID & ".xls"

    aFile.Application.Visible = True
    aFile.Workbooks.Open directory & fn 'ERROR ON THIS LINE
    aFile.Parent.Windows(1).Visible = True
End Sub

I have also tried the following code...

Sub copyDealerDataToWorkbook(ByVal targetID As Integer)
    Dim foreignWB   As Workbook
    Dim mainWB      As Workbook
    Dim directory   As String
    Dim fn          As String

    Set mainWB = ActiveWorkbook
    directory = ActiveWorkbook.Path

    'append a "\"
    If InStrRev(directory, "\") < Len(directory) Then
        directory = directory & "\"
    End If

    fn = targetID & ".xls"

    Set foreignWB = Workbooks.Open(fileName:=directory & fn) 'ERROR ON THIS LINE
End Sub

Answer 1

First use hard code with name of file and test if is open.

For sample

Workbooks.open "\\Oesdfiles\Users\FName LName\Reports\test_data\monthly EDS reports\Number by dealer Per importer\test_2011\2011-01January\YourFileHere.xls"

If Yes, your directory or fn var is wrong.

If No, try use office diagnostic in office button/ excel options/Resources/Diagnostic

And try again.

If not work's , your excel need's reinstall.

[]'s

Answer 2

If Dir(directory & fn) <> "" Then
    'do the magic
Else
    MsgBox "Directory and file not found:" & vbCrLf & directory & fn, vbCritical, "Invalid Directory"
End If

Answer 3

targetID As Integer

This only works if your Excel workbook has a numeric file name without leading zeroes. Also, In this context there's no need to make targetID an Integer since you aren't iterating or performing calculations on the value, I would make it instead:

targetID As String

Edit: I just realized this was posted 2 years ago. :P