CODEWITHSUNDEEP
excelvba
The Numbers Man
The Numbers Man

Reputation: 139

VBA Query to pull XML data into an Excel File

I am getting an error in the below VBA. It is throwing an error on the Set Book = Workbooks.OpenXM(FilePath. Unfortunately, this is not my Code and the guy who wrote it is uncontactable. Can anyone please advise me as to what this line is looking for? The purpose of the Code is to import an XML file into an Excel File.

Sub importITE()

    Dim FilePath As String
    Dim Book As Workbook
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFile As Object
    Dim i As Integer

'   Load XML Data to New Workbook
    FilePath = "C:\Users\Joebloggs\Documents\Sample Reconciliations\ITE\*.xml"
    Set Book = Workbooks.OpenXML(FilePath)

   'Copy to active Worksheet
    Book.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("ITE").Range("A1")
    Set oFSO = CreateObject("Scripting.FileSystemObject")


    'Close New Workbook
    Book.Close False
    
    
End Sub

Upvotes: 0

Views: 166

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Here's an outline of how you'd use Dir() to get the exact file name:

Const FPATH As String = "C:\Users\Joebloggs\Documents\Sample Reconciliations\ITE\" 
Dim f, Book

f = Dir(FPATH & "*.xml")
If Len(f) > 0 Then
    Set Book = Workbooks.OpenXML(FPATH & f)

    'other actions
Else
    Msgbox "No xml file found..."
End If

Upvotes: 1

Related Questions