в общем доработал код и получилось следующее
Private Sub CommandButtonImport_Click()
Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.clear
.Title = "Select Multiple XML Files"
.Filters.Add "XML File", "*.xml", 1
.AllowMultiSelect = True
If .Show = True Then
Dim xdoc As Object
Set xdoc = CreateObject("MSXML2.DOMdocument")
xdoc.async = False: xdoc.validateOnParse = False
row_number = 1
For i = 1 To .SelectedItems.Count
xmlFileName = fd.SelectedItems(i)
xdoc.Load (xmlFileName)
Set xdocE = xdoc.DocumentElement
For Each xdocE In xdoc.SelectNodes("ED_Container/ContainerDoc/DocBody/ESADout_CU")
Application.Range("DTRange").Cells(row_number, 1).Value = Right(xdocE.SelectSingleNode("/comment()").Text, 23)
Application.Range("DTRange").Cells(row_number, 2).Value = xdocE.SelectSingleNode("CustomsProcedure").Text
Application.Range("DTRange").Cells(row_number, 3).Value = xdocE.SelectSingleNode("CustomsModeCode").Text
Set nESADout_CUGoodsShipment = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment")
For Each nCUGoodsShipment In nESADout_CUGoodsShipment
sTotalGoodsNumber = nCUGoodsShipment.SelectSingleNode("catESAD_cu:TotalGoodsNumber").nodeTypedValue
Next
Set nESADout_CUGoods = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods")
For Each nCUGoods In nESADout_CUGoods
sGoodsNumeric = nCUGoods.SelectSingleNode("catESAD_cu:GoodsNumeric").nodeTypedValue
sGoodsTNVEDCode = nCUGoods.SelectSingleNode("catESAD_cu:GoodsTNVEDCode").nodeTypedValue
sGoodsMarking = nCUGoods.SelectSingleNode("catESAD_cu:GoodsGroupDescription/catESAD_cu:GoodsGroupInformation/catESAD_cu:GoodsMarking").nodeTypedValue
Application.Range("DTRange").Cells(row_number, 4).Value = sGoodsNumeric
Application.Range("DTRange").Cells(row_number, 5).Value = sGoodsTNVEDCode
Application.Range("DTRange").Cells(row_number, 6).Value = sGoodsMarking
row_number = row_number + 1
If sTotalGoodsNumber > 1 Then GoTo a1 Else GoTo a2
a1: If Application.Range("DTRange").Cells(row_number, 1).Value = "" Then Application.Range("DTRange").Cells(row_number, 1).Value = Application.Range("DTRange").Cells(row_number - 1, 1).Value
If Application.Range("DTRange").Cells(row_number, 2).Value = "" Then Application.Range("DTRange").Cells(row_number, 2).Value = Application.Range("DTRange").Cells(row_number - 1, 2).Value
If Application.Range("DTRange").Cells(row_number, 3).Value = "" Then Application.Range("DTRange").Cells(row_number, 3).Value = Application.Range("DTRange").Cells(row_number - 1, 3).Value
Next nCUGoods
a2: Next xdocE
Next i
If Application.Range("DTRange").Cells(row_number, 5).Value = "" Then Application.Range("DTRange").Cells(row_number, 1).Value = ""
If Application.Range("DTRange").Cells(row_number, 5).Value = "" Then Application.Range("DTRange").Cells(row_number, 2).Value = ""
If Application.Range("DTRange").Cells(row_number, 5).Value = "" Then Application.Range("DTRange").Cells(row_number, 3).Value = ""
End If
End With
End Sub
как вы и сказали вопросы остались
Но они появились после того как захотелось еще одно поле увидеть
sGoodsMarking = nCUGoods.SelectSingleNode("catESAD_cu:GoodsGroupDescription/catESAD_cu:GoodsGroupInformation/catESAD_cu:GoodsMarking").nodeTypedValue
И как оказалось оно есть не во всех ДТ. Когда код доходит до этих ДТ спотыкается и выдает ошибку. Подскажите как можно обойти эту ошибку. Пробовал On Error GoTo, но или не там прописывал или не правильно понял как им пользоваться.
Excel это не сложно
