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) xdoc.SetProperty "SelectionNamespaces", "xmlns:ns='urn:customs.ru:Information:ExchangeDocuments:ED_Container:5.13.1'" Set xdocE = xdoc.DocumentElement For Each xdocE In xdoc.SelectNodes("ns:ED_Container/ns:ContainerDoc/ns:DocBody/ns:ESADout_CU") Aplication.Range("DTRange").Cells(row_number, 1).Value = xdocE.SelectSingleNode("/comment()").Text Aplication.Range("DTRange").Cells(row_number, 2).Value = xdocE.SelectSingleNode("ns:CustomsProcedure").Text Aplication.Range("DTRange").Cells(row_number, 3).Value = xdocE.SelectSingleNode("ns:CustomsModeCode").Text Aplication.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("ns:catESAD_cu:GoodsNumeric").Text Aplication.Range("DTRange").Cells(row_number, 5).Value = xdocE.SelectSingleNode("ns:catESAD_cu:GoodsTNVEDCode").Text row_number = row_number + 1 Next xdocE Next i End If End WithEnd Sub
Aplication.Range("DTRange").Cells(row_number, 1).Value = xdocE.SelectSingleNode("comment()").Text Aplication.Range("DTRange").Cells(row_number, 2).Value = xdocE.SelectSingleNode("dout:CustomsProcedure").Text Aplication.Range("DTRange").Cells(row_number, 3).Value = xdocE.SelectSingleNode("dout:CustomsModeCode").Text Aplication.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("atc:catESAD_cu:GoodsNumeric").Text Aplication.Range("DTRange").Cells(row_number, 5).Value = xdocE.SelectSingleNode("atc:catESAD_cu:GoodsTNVEDCode").Text row_number = row_number + 1 Next xdocE
For Each xdocE In xdoc.SelectNodes("ED_Container/ContainerDoc/DocBody/ESADout_CU")
xdoc.Load (xmlFileName) xdoc.SetProperty "SelectionNamespaces", "xmlns:cont='urn:customs.ru:Information:ExchangeDocuments:ED_Container:5.13.1'" xdoc.SetProperty "SelectionNamespaces", "xmlns:dout='urn:customs.ru:Information:CustomsDocuments:ESADout_CU:5.17.0'" xdoc.SetProperty "SelectionNamespaces", "xmlns:atc='urn:customs.ru:CUESADCommonAggregateTypesCust:5.17.0'" Set xdocE = xdoc.DocumentElement For Each xdocE In xdoc.SelectNodes("cont:ED_Container/cont:ContainerDoc/cont:DocBody/dout:ESADout_CU") Application.Range("DTRange").Cells(row_number, 1).Value = xdocE.SelectSingleNode("comment()").Text Application.Range("DTRange").Cells(row_number, 2).Value = xdocE.SelectSingleNode("dout:CustomsProcedure").Text Application.Range("DTRange").Cells(row_number, 3).Value = xdocE.SelectSingleNode("dout:CustomsModeCode").Text Application.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("atc:catESAD_cu:GoodsNumeric").Text Application.Range("DTRange").Cells(row_number, 5).Value = xdocE.SelectSingleNode("atc:catESAD_cu:GoodsTNVEDCode").Text row_number = row_number + 1 Next xdocE Next i
xdoc.SetProperty "SelectionNamespaces", "xmlns:cont='urn:customs.ru:Information:ExchangeDocuments:ED_Container:5.13.1'" xdoc.SetProperty "SelectionNamespaces", "xmlns:dout='urn:customs.ru:Information:CustomsDocuments:ESADout_CU:5.17.0'" xdoc.SetProperty "SelectionNamespaces", "xmlns:atc='urn:customs.ru:CUESADCommonAggregateTypesCust:5.17.0'" Set xdocE = xdoc.DocumentElement For Each xdocE In xdoc.SelectNodes("ED_Container/ContainerDoc/DocBody/ESADout_CU") Application.Range("DTRange").Cells(row_number, 1).Value = xdocE.SelectSingleNode("/comment()").Text Application.Range("DTRange").Cells(row_number, 2).Value = xdocE.SelectSingleNode("CustomsProcedure").Text Application.Range("DTRange").Cells(row_number, 3).Value = xdocE.SelectSingleNode("CustomsModeCode").Text Application.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("GoodsNumeric").Text Application.Range("DTRange").Cells(row_number, 5).Value = xdocE.SelectSingleNode("GoodsTNVEDCode").Text row_number = row_number + 1
Application.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("GoodsNumeric").Text Application.Range("DTRange").Cells(row_number, 5).Value = xdocE.SelectSingleNode("GoodsTNVEDCode").Text
For Each xdocE In xdoc.SelectNodes("ED_Container/ContainerDoc/DocBody/ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods")
Application.Range("DTRange").Cells(row_number, 4).Value = xdocE.SelectSingleNode("ESADout_CUGoodsShipment/ESADout_CUGoods/catESAD_cu:GoodsNumeric").Text
Set nESADout_CUGoods = xmlDoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods")For Each nCUGoods In nESADout_CUGoodssGoodsNumeric = nCUGoods.SelectSingleNode("catESAD_cu:GoodsNumeric").nodetypedvaluesGoodsTNVEDCode = nCUGoods.SelectSingleNode("catESAD_cu:GoodsTNVEDCode").nodetypedvalueNext
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 WithEnd Sub
On Error Resume NextsGoodsMarking="" 'обнуляем переменную, чтобы при возникновении ошибки она оставалась пустой'пробуем запомнить в переменную значение. Если ошибка - переменная останется пустойsGoodsMarking = nCUGoods.SelectSingleNode("catESAD_cu:GoodsGroupDescription/catESAD_cu:GoodsGroupInformation/catESAD_cu:GoodsMarking").nodeTypedValue
For each oEl in xmlDoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument[catESAD_cu:PresentedDocumentModeCode=0422]/cat_ru:PrDocumentNumber")' обработкаNext
Set nESADout_CUPresentedDocument = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument") For Each nPresentedDocument In nESADout_CUPresentedDocument sGoodsPresentedDocumentModeCode = nPresentedDocument.SelectSingleNode("catESAD_cu:PresentedDocumentModeCode").Text Next
Set nESADout_CUPresentedDocument = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument") For Each nPresentedDocument In nESADout_CUPresentedDocument.SelectSingleNode("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument[catESAD_cu:PresentedDocumentModeCode=0422]/cat_ru:PrDocumentNumber").Text
Set nESADout_CUPresentedDocument = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument") For Each nPresentedDocument In nESADout_CUPresentedDocument sGoodsPresentedDocumentModeCode = nPresentedDocument.SelectSingleNode("catESAD_cu:PresentedDocumentModeCode").Text sPrDocumentNumber = nPresentedDocument.SelectSingleNode("cat_ru:PrDocumentNumber").Text If sGoodsPresentedDocumentModeCode Like "0422" Then Application.Range("DTRange").Cells(row_number, 8).Value = sGoodsPresentedDocumentModeCode: Application.Range("DTRange").Cells(row_number, 9).Value = sPrDocumentNumber Next
Select Case sGoodsPresentedDocumentModeCode Case "0422" Application.Range("DTRange").Cells(row_number, 8).Value = sGoodsPresentedDocumentModeCode Application.Range("DTRange").Cells(row_number, 9).Value = sPrDocumentNumber Case Else GoTo a3 End Select
Set nESADout_CUPresentedDocument = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/*[catESAD_cu:PresentedDocumentModeCode=04022]") For Each nPresentedDocument In nESADout_CUPresentedDocument sGoodsPresentedDocumentModeCode = nPresentedDocument.SelectSingleNode("catESAD_cu:PresentedDocumentModeCode").Text sPrDocumentNumber = nPresentedDocument.SelectSingleNode("cat_ru:PrDocumentNumber").Text Next
Set nESADout_CUPresentedDocument = xdoc.DocumentElement.SelectNodes("//ESADout_CU/ESADout_CUGoodsShipment/ESADout_CUGoods/ESADout_CUPresentedDocument[catESAD_cu:PresentedDocumentModeCode=04022]") sGoodsPresentedDocumentModeCode = "" sPrDocumentNumber = "" For Each nPresentedDocument In nESADout_CUPresentedDocument sGoodsPresentedDocumentModeCode = nPresentedDocument.SelectSingleNode("catESAD_cu:PresentedDocumentModeCode").Text sPrDocumentNumber = nPresentedDocument.SelectSingleNode("cat_ru:PrDocumentNumber").Text Next
Set nodeXML = nCUGoods.getElementsByTagName("catESAD_cu:GoodsDescription") For k = 0 To nodeXML.Length - 1 If k = 0 Then sGoodsDescription1 = nodeXML(k).Text If k = 1 Then sGoodsDescription2 = nodeXML(k).Text Next