How to pull XML data into another excel sheet using VBA?

[paulked]

I've had a sleep now!

You could limit the file open to compatible files, eg xml, csv or xl as in this example:

Sub ImportFile()
    Dim fName As Variant, fType As String, fPath As String
    fPath = Sheets("input").Range("e2").Text
    If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
TryAgain:
    ChDir fPath
    fName = Application.GetOpenFilename("Compatible Files *.xml *.csv *.xl*,*.xml;*.xl*;*.csv", 1, "Select a File to Import...")
    If fName = False Then GoTo Xit
    If LCase(Left(fName, Len(fPath))) <> LCase(fPath) Then
        If MsgBox("You cannot load a file from this directory.  Try again?", vbYesNo, "Wrong folder...") <> vbYes Then Exit Sub
        GoTo TryAgain
    End If
    fType = Right(fName, 3)
    If fType = LCase("xml") Or fType = LCase("csv") Then
        MsgBox "XML or CSV files can be imported direct to the workbook"
    Else
        MsgBox "Excel Files can be opened to manually copy data from or certain sheets/data copied from them via VBA"
    End If
    Exit Sub
Xit:
    MsgBox "No file loaded.", , "Cancelled?"
End Sub

This restricts files to XML, CSV or XL* and also to the directory or its sub directories in E2.