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, Commented Jul 28, 2015 at 13:51
@simple man: based on your above comment (credit where due!), I fixed the typo in my post:) — Siddharth Rout
– Siddharth Rout, Commented Jul 28, 2015 at 14:02
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
– Cairl Dacar Borstnik, Commented Jul 31, 2015 at 16:29

Siddharth Rout · Accepted Answer · 2015-07-28 14:00:40Z

2

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.

edited Jul 28, 2015 at 14:00

answered Jul 28, 2015 at 13:50

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cairl Dacar Borstnik Over a year ago

The code is actually opening the correct file and type, the 424 error happens after the target file has opened (you're correct, on the InStr line). I think the problem is occurring with the 'updatelinks' after the file has opened

Collectives™ on Stack Overflow

excel vba open file runtime error 424

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related