excel vba open file runtime error 424

Question

Excel 2010 VBA: I'm trying to loop through files in a folder and only open the files with names that contain a certain string. I've done this before and I know the logic works, but I keep getting the 424 error when I'm opening the target files. I'm pretty sure it has something to do with the links and have tried EVERYTHING to turn off those alerts problematically, but I'm still getting the error

Private Sub CommandButton1_Click()
    Dim lSecurity As Long
    Dim myPath As Variant

    lSecurity = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityLow
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False

    myPath = "F:\Pathname"
    Call Recurse(myPath)

    Application.AutomationSecurity = lSecurity
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True
End Sub


Function Recurse(sPath As Variant) As String
    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim myFile As Variant
    Dim file As String
    Dim A As Workbook
    Dim B As Workbook
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim count As Integer

    Set myFolder = FSO.GetFolder(sPath)
    Set A = ThisWorkbook
    i = 2

    For Each myFile In myFolder.Files
        If InStr(myFile.Name, "_2015_DOMESTIC_TB") <> 0 Then
            Set B = Workbooks.Open(Filename:=myFile)
            Call Datadump
            B.Close SaveChanges:=False
        End If
        i = i + 1
    Next

End Function

Function Datadump()

    A.Cells(i, 1).Value = B.Cells(1, 4).Value

    For count = 1 To 59
        k = 2
        A.Cells(i, k).Value = B.Cells(11 + count, 4).Value
        count = count + 1
        k = k + 1
    Next count

End Function

The error happens at which line? Probably at If InStr(myFile.Name, "_2015_DOMESTIC_TB") <> 0 Then. Try with Dim myFile As Object instead of Dim myFile As Variant. And then, Set B = Workbooks.Open(Filename:=myFile.Name) rather than Set B = Workbooks.Open(Filename:=myFile) — simpLE MAn
@simple man: based on your above comment (credit where due!), I fixed the typo in my post:) — Siddharth Rout
I tried changing myFile to a variant, but that yields error 1004 and the target file never opens. With the original code the file would open and THEN I would get the 424 error at the InStr line of code. I think the error is stemming from the target file having originated as a web document, but I'm not able to programmatically get past those dialog boxes — Cairl Dacar Borstnik

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-07-28T13:50:41

Seems like your function is trying to open a non Excel file. Change your function to (Untested as posting from phone)

Function Recurse(sPath As Variant) As String
    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim myFile As Variant
    Dim file As String
    Dim A As Workbook, B As Workbook
    Dim i As Integer, j As Integer, k As Integer, count As Integer
    Dim MyAr As Variant

    Set myFolder = FSO.GetFolder(sPath)
    Set A = ThisWorkbook
    i = 2

    For Each myFile In myFolder.Files
        If InStr(myFile.Name, "_2015_DOMESTIC_TB") <> 0 Then
            MyAr = Split(myFile.Name, ".")
            If MyAr(UBound(MyAr)) Like "xls*" Then '<~~ Check if it is an Excel file
                Set B = Workbooks.Open(Filename:=myFile.Name)
                Call Datadump
                B.Close SaveChanges:=False
            End If
        End If
        i = i + 1
    Next
End Function

This function will check that you are trying to open a valid excel file.

If you still get the error then please tell us which line is giving you the error and what is the value of myFile.Name at the time of error.

excel vba open file runtime error 424

1 Answers