CODEWITHSUNDEEP
vbaexcel
Cairl Dacar Borstnik
Cairl Dacar Borstnik

Reputation: 45

excel vba open file runtime error 424

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

Upvotes: 1

Views: 1044

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

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.

Upvotes: 2

Related Questions