Execution Error 424 - Import multiple XML files in VBA

Question

I’m completely new in vba and I’m having a problem with my code.

I need to extract data from some tags from various files XML. When I execute the code or perform Debub, the error of 424 "The object is required" on the line XML.Filename = fd.SelectedItems(i).

Here is the code:

Private Sub CommandButtonImport_Click()
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Clear
        .Title = "Select Multiple XML Files"
        .Filters.Add "XML File", "*.xml", 1
        .AllowMultiSelect = True
        
    If .Show = True Then
        Dim xdoc As Object
        Set xdoc = CreateObject("MSXML2.DOMDocument")
        xdoc.async = False: xdoc.validateOnParse = False
        row_number = 1
        For i = 1 To .SelectedItems.Count
            XML.Filename = fd.SelectedItems(i)
            xdoc.Load (XML.Filename)
            Set Products = xdoc.DocumentElement
            For Each Product In Products.ChildNodes
                Application.Range("ProductsRange").Cells(row_number, 1).Value = Product.ChildNodes(0).Text
                Application.Range("ProductsRange").Cells(row_number, 2).Value = Product.ChildNodes(1).Text
                Application.Range("ProductsRange").Cells(row_number, 3).Value = Product.ChildNodes(2).Text
                Application.Range("ProductsRange").Cells(row_number, 4).Value = Product.ChildNodes(3).Text
                Application.Range("ProductsRange").Cells(row_number, 5).Value = Product.ChildNodes(4).Text
                Application.Range("ProductsRange").Cells(row_number, 6).Value = Product.ChildNodes(5).Text
                Application.Range("ProductsRange").Cells(row_number, 7).Value = Product.ChildNodes(6).Text
                row_number = 1 = row_number + 1
            
            Next Product
                        
        Next i
    
    End If
    End With
    
End Sub

Answer 1

Hail, my dear!

I believe the mistake happened because the variable XML not defined. As XML is Nothing, has no properties (in your case, the FileName which you tried to assign). "The object is required" to see if there is this property and assign it.

You can use two methods to load XML: Load and LoadXML. Both take as string parameter. In the case of Load, string must contain the file path; in the case of LoadXML, the string must contain the XML text itself.

Your choice was to use the Load with the file path, then simply remove the reference to the XML variable, which does not exist, and pass the string with the file path directly to the xdoc.Load:

    For i = 1 To .SelectedItems.Count
        ' XML.Filename = fd.SelectedItems(i)
        xdoc.Load fd.SelectedItems(i)
        ' Se eu entendi corretamente, isso aqui é o caminho do arquivo. Se for
        ' só o nome do arquivo, dê um jeito de adicionar o caminho, algo como
        ' xdoc.Load strCaminho & "\" & fd.SelectedItems(i)

Finally, just to explain another possible mistake: xdoc.Load (XML.Filename) probably result in error. In VB, when passing parameters, parentheses are only used when there is some value assignment involved. If on the line you are just calling a function or method without assigning value, you should not use parentheses. This also happens with Functions and Subs. For example, let’s say you have a function that makes certain changes on the screen, without returning values, and will make its call. Since there is no value assignment, you should not use parentheses to pass the parameters (MeuSubAjustaTela strCorDoTema). But if the function returns a value and you want to assign this value to a variable (or even pass this value as parameter in another function/method), then you should use parentheses (strCaminhoValidado = MinhaFunctionValidadora (strArquivo)).