XML files into Excel

[linesy]

Hi all,

I have to download 43 files from an outside vendor and then consolidate those files into one workbook. Currently they download into c:\Users\Downloads and I open each one individually and it converts into an excel table.

Does anyone have any clues as to how can I easily get all of this data into one file and one sheet? I also only need rows that contain "IA-1" i column F.

If they were already excel workbooks I have a macro that would consolidate all of them but not sure how to do this with an XML file. Thanks for your help in advance.

 

[Worf]

Hi

This will generate Excel workbooks:

Sub XML_to_Excel()
' converts XML files into Excel tables


Dim wb As Workbook, xmlf$, fd$
fd = "C:\pub\"
ChDir fd
xmlf = Dir("*.xml")
Do While xmlf <> ""
    Set wb = Workbooks.OpenXML(FileName:=fd & "\" & xmlf, _
    LoadOption:=xlXmlLoadImportToList)
    MsgBox wb.FullName, 64
    xmlf = Dir
Loop
End Sub

 

[linesy]

Thank you Worf...this works great! Is there a way of not having to confirm "ok" for every file that opens? Again, thanks for your expertise.

Obrigado .... visitei Rio em Março. Bela. O meu marido é de Minas

 

[SpillerBD]

Skip the message Bog line in his macro. (comment it out.)

'MsgBox wb.FullName, 64

 

[Worf]

Like Spiller said, just remove that line of code.

Fico feliz que esteja funcionando!