Solved: Help Importing multiple XML files - VBA

[librkvn]

I'm attempting to import multiple XML files to a mapped table. The halts execution at the beginning part of the If statement say the they're is type mismatch Run time error 13. Here is the code. Appreciate any help on this.

[vba]

Sub XmlImport()


fileToOpen = Application _

.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)
If fileToOpen = False Then
' MsgBox ("Cannot Open file - Exiting Macro")
Exit Sub
End If



ActiveWorkbook.XmlMap.("PartQuote_Map").ImportURL:=fileToOpen,Overwrite:=Fa lse


End Sub
[/vba]

[p45cal]

At the end of this line:
fileToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)
you have True for MultiSelect, this means that the method returns an array even if only one file is chosen by the user. So you should do something along these lines:[vba]Sub XmlImport()
fileToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)
If IsArray(fileToOpen) Then
For Each fil In fileToOpen
MsgBox fil
Next fil
Else
' MsgBox ("Cannot Open file - Exiting Macro")
End If
End Sub
[/vba]Otherwise, if you change that multiselect argument to False (or miss it out altogether since that's its default value:
[vba]Sub XmlImport2()
fileToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML")
If fileToOpen = False Then Exit Sub
ActiveWorkbook.XmlMap.("PartQuote_Map").ImportURL:=fileToOpen,Overwrite:=Fa lse
End Sub
[/vba]

[librkvn]

p45cal The If IsArray part with the for each loop is helpful. I just need to pass the array to "ActiveWorkbook.XmlMaps("PartQuote_Map").Import URL:=fileToOpen" so that the xml files actually import into my table.

[librkvn]

I figured it out. I replaced ActiveWorkbook.XmlMaps("PartQuote_Map").Import URL:=(fileToOpen) with ActiveWorkbook.XmlMaps("PartQuote_Map").Import URL:=fil and the files import with no problem.

[librkvn]

My Bad no disrespect intended. Thank very much. You saved me a great deal of time p45cal.

[snb]

or
[vba]
Sub M_snb()
for each fl in Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)
ActiveWorkbook.XmlMap("PartQuote_Map").Import fl
Next
End Sub
[/vba]

[p45cal]

snb, on my machine it falls over on the line beginning For Each fl.. if the user presses Cancel in the dialogue box.

[librkvn]

Gentlemen this one got solved with following code. It does everything that I need it to. I thank you all for assistance. Much appreciated p45cal & snb.

[VBA]
Sub XmlImport()
ChDir "\\scbfps1\Data\Sales\Parts Quotes"

fileToOpen = Application _
.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)

Application.DisplayAlerts = False
If IsArray(fileToOpen) Then
For Each fil In fileToOpen
ActiveWorkbook.XmlMaps("PartQuote_Map").Import URL:=fil
Next fil
Else
Exit Sub
End If
Application.DisplayAlerts = True
End Sub
[/VBA]

[p45cal]

Be careful lirkvn, you unconditionally set .DisplayAlerts to False, but the .DisplayAlerts = True to restore alerts will not get executed (because of the Exit Sub) if the user selects one or more files in the dialogue box. Unless this is intentional I would have thought[VBA]Sub XmlImport()
ChDir "\\scbfps1\Data\Sales\Parts Quotes"
fileToOpen = Application.GetOpenFilename("XML Files (*.xml), *.xml", , "Import XML", , True)
If IsArray(fileToOpen) Then
Application.DisplayAlerts = False
For Each fil In fileToOpen
ActiveWorkbook.XmlMaps("PartQuote_Map").Import URL:=fil
Next fil
Application.DisplayAlerts = True
End If
End Sub[/VBA] would be safer. (Does importing an xml file bring up a warning?)

[librkvn]

I had the Application.DisplayAlerts turned off due to issue that was coming up during import (I had a date data type field set to string and the schema verification was catching that and warning me). I've since then fixed this issue and will remove the Application.DisplayAlerts. thanks p45cal