Method importxml of object application failed

[steve21nj]

I am attempting to pull data from an XML file via VBA without doing the import on external data. I created a blank table called "tblHolding". In theory, my code works the way i want which is: User clicks button, file explorer opens for them to select the file, imports data to "tblHolding", and msg box saying complete.

It runs through the code but doesn't import. It errors at the below code. Also, where do i reference the table so I can append the data.

Any suggestions?

Fails saying method importxml of object application failed

Application.ImportXML strPath & strFileList(intFile), 2

Private Sub Command234_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder
Dim f As Object

Dim strFolder As String
Dim varItem As Variant
 
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = True
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
         Next
        End If
        Set f = Nothing
        
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
    
 
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
        [U]Application.ImportXML strPath & strFileList(intFile), 2[/U]
    Next intFile
    MsgBox "Import Completed"
End Sub

 

[JHB]

What are the values of strPath & strFileList(intFile)?

 

[MarkK]

More importantly, what is the error?

 

[steve21nj]

The error is 'run time error -2146697203 (800c000d)': Method 'ImportXML' of object'_Application' failed.

 

[steve21nj]

What are the values of strPath & strFileList(intFile)?

In theory it would be this, but I want the user to select the file and folder, not hard code it.

strPath = "Z:\Data\Import\"
    strFile = Dir(strPath & "savfile*.XML")

 

[steve21nj]

I got it to work by fixing the strPath below. Not sure if this is the best solution but it is working.

Private Sub Command234_Click()
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder
Dim f As Object
Dim strFolder As String
Dim varItem As Variant
 
    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            [B][U]strPath[/U][/B] = Left(varItem, Len(varItem) - Len(strFile))
         Next
        End If
        
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
    
 
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
        Application.ImportXML strPath & strFileList(intFile), 2
    Next intFile
    MsgBox "Import Completed"
    Set f = Nothing
End Sub