Pereiti prie turinio

XML ir EXCEL VBA

ViJa

Sukūrė ViJa
Programuotojų kampas

 Dalintis Sekėjai 0

Rekomenduojami pranešimai

ViJa

ViJa 9

Sukurta
Sukurta

Sveiki.

 

Bandau susipaprastinti darbą ir noriu pasirašyti vba skriptą tam tikroms reikšmėms apdoroti. Pagrindinė programa išspjauna xml failą .edc formatu, kurį galima atidaryti excel workbooke su komanda 

Workbooks.OpenXML Filename:="\ISS01.xml", LoadOption:=xlXmlLoadImportToList

Tačiau, tai labai nepatogu, o ir iš pačio failo yra reikalinga tik siaura dalis informacijos.

 

Štai kaip atrodo dalis .xml failo

<?xml version="1.0" encoding="UTF-8"?>

-<EplanPxfRoot StreamSchema="EBitmap,BaseTypes,2,1,2;EPosition3D,BaseTypes,0,3,2;ERay3D,BaseTypes,0,4,2;EStreamableVector,BaseTypes,0,5,2;Arc3d@W3D,W3dBaseGeometry,0,16,2;Box3d@W3D,W3dBaseGeometry,0,17,2;Circle3d@W3D,W3dBaseGeometry,0,18,2;Color@W3D,W3dBaseGeometry,0,19,2;ContourPlane3d@W3D,W3dBaseGeometry,0,20,2;CTexture@W3D,W3dBaseGeometry,0,21,2;CTextureMap@W3D,W3dBaseGeometry,0,22,2;Frustum3d@W3D,W3dBaseGeometry,0,23,2;Line3d@W3D,W3dBaseGeometry,0,24,2;Linetype@W3D,W3dBaseGeometry,0,25,2;Material@W3D,W3dBaseGeometry,3,26,2;Path3d@W3D,W3dBaseGeometry,0,27,2;DMNCDataSet,TrDMProject,1,29,2;DMNCDataSetVector,TrDMProject,1,30,2;DMPlaceHolderRuleData,TrDMProject,0,31,2;Mesh3dX@W3D,W3dMeshModeller,2,33,2;MeshBox@W3D,W3dMeshModeller,5,34,2;MeshMate@W3D,W3dMeshModeller,7,35,2;MeshMateFace@W3D,W3dMeshModeller,1,36,2;MeshMateGrid@W3D,W3dMeshModeller,8,37,2;MeshMateGridLine@W3D,W3dMeshModeller,1,38,2;MeshMateLine@W3D,W3dMeshModeller,1,39,2;MeshText3dX@W3D,W3dMeshModeller,0,41,2;BaseTextLine@W3D,W3dMeshModeller,2,42,2;Mesh3d@W3D,W3dMeshModeller,8,43,2;MeshEdge3d@W3D,W3dMeshModeller,0,44,2;MeshFace3d@W3D,W3dMeshModeller,2,45,2;MeshPoint3d@W3D,W3dMeshModeller,1,46,2;MeshPolygon3d@W3D,W3dMeshModeller,1,47,2;MeshSimpleTextureTriangle3d@W3D,W3dMeshModeller,2,48,2;MeshSimpleTriangle3d@W3D,W3dMeshModeller,1,49,2;MeshTriangle3d@W3D,W3dMeshModeller,2,50,2;MeshTriangleFace3d@W3D,W3dMeshModeller,0,51,2;MeshTriangleFaceEdge3D@W3D,W3dMeshModeller,0,52,2;BaseSolidBlock@W3D,W3dBaseModeller,0,53,2;BaseSolidDiskSweep@W3D,W3dBaseModeller,0,54,2;BaseSolidExtrusion@W3D,W3dBaseModeller,0,55,2;BaseSolidSweep@W3D,W3dBaseModeller,0,56,2" NumMDSteps="0" NumProjectSteps="5121" NumMainObjects="5121" ConfigurationFlags="9153" Description="" SourceProject="" Source="" SchemaVersion="1.7.5325" PxfVersion="1.22" Version="2.1.5325" Type="DataConfiguratorMacro" Name="~data_configurator">

<?xml version="1.0" encoding="UTF-8"?>



-<EplanPxfRoot StreamSchema="EBitmap,BaseTypes,2,1,2;EPosition3D,BaseTypes,0,3,2;ERay3D,BaseTypes,0,4,2;EStreamableVector,BaseTypes,0,5,2;Arc3d@W3D,W3dBaseGeometry,0,16,2;Box3d@W3D,W3dBaseGeometry,0,17,2;Circle3d@W3D,W3dBaseGeometry,0,18,2;Color@W3D,W3dBaseGeometry,0,19,2;ContourPlane3d@W3D,W3dBaseGeometry,0,20,2;CTexture@W3D,W3dBaseGeometry,0,21,2;CTextureMap@W3D,W3dBaseGeometry,0,22,2;Frustum3d@W3D,W3dBaseGeometry,0,23,2;Line3d@W3D,W3dBaseGeometry,0,24,2;Linetype@W3D,W3dBaseGeometry,0,25,2;Material@W3D,W3dBaseGeometry,3,26,2;Path3d@W3D,W3dBaseGeometry,0,27,2;DMNCDataSet,TrDMProject,1,29,2;DMNCDataSetVector,TrDMProject,1,30,2;DMPlaceHolderRuleData,TrDMProject,0,31,2;Mesh3dX@W3D,W3dMeshModeller,2,33,2;MeshBox@W3D,W3dMeshModeller,5,34,2;MeshMate@W3D,W3dMeshModeller,7,35,2;MeshMateFace@W3D,W3dMeshModeller,1,36,2;MeshMateGrid@W3D,W3dMeshModeller,8,37,2;MeshMateGridLine@W3D,W3dMeshModeller,1,38,2;MeshMateLine@W3D,W3dMeshModeller,1,39,2;MeshText3dX@W3D,W3dMeshModeller,0,41,2;BaseTextLine@W3D,W3dMeshModeller,2,42,2;Mesh3d@W3D,W3dMeshModeller,8,43,2;MeshEdge3d@W3D,W3dMeshModeller,0,44,2;MeshFace3d@W3D,W3dMeshModeller,2,45,2;MeshPoint3d@W3D,W3dMeshModeller,1,46,2;MeshPolygon3d@W3D,W3dMeshModeller,1,47,2;MeshSimpleTextureTriangle3d@W3D,W3dMeshModeller,2,48,2;MeshSimpleTriangle3d@W3D,W3dMeshModeller,1,49,2;MeshTriangle3d@W3D,W3dMeshModeller,2,50,2;MeshTriangleFace3d@W3D,W3dMeshModeller,0,51,2;MeshTriangleFaceEdge3D@W3D,W3dMeshModeller,0,52,2;BaseSolidBlock@W3D,W3dBaseModeller,0,53,2;BaseSolidDiskSweep@W3D,W3dBaseModeller,0,54,2;BaseSolidExtrusion@W3D,W3dBaseModeller,0,55,2;BaseSolidSweep@W3D,W3dBaseModeller,0,56,2" NumMDSteps="0" NumProjectSteps="5121" NumMainObjects="5121" ConfigurationFlags="9153" Description="" SourceProject="" Source="" SchemaVersion="1.7.5325" PxfVersion="1.22" Version="2.1.5325" Type="DataConfiguratorMacro" Name="~data_configurator">


-<DataConfiguration languages="en_US,lt_LT">

<XMemberDataEntry Description="" Name="A1" Readonly="0" ID="60001"/>

<XMemberDataEntry Description="Function definition" Name="A1383" Readonly="0" ID="61383"/>

<XMemberDataEntry Description="Function group" Name="A1382" Readonly="0" ID="61382"/>

<XMemberDataEntry Description="Function category" Name="A1381" Readonly="0" ID="61381"/>

<XPropertyEntry Description="DT (displayed)" Name="P20010" Readonly="0" ID="20010" Index="0"/>

</DataConfiguration>

<O59 P20010="" A1381="4" A1382="1" A1383="1" A1="59/37946" Build="5325"/>

................................
<O17 P20010="" A1381="100" A1382="5" A1383="0" A1="17/1072" Build="5325"/>
................................

<O17 P20010="" A1381="100" A1382="5" A1383="0" A1="17/1191" Build="5325"/>

<O17 P20010="-10K1-M10" A1381="200" A1382="1" A1383="1" A1="17/1016" Build="5325"/>

<O17 P20010="-10K1-M10" A1381="201" A1382="1" A1383="2" A1="17/1018" Build="5325"/>
................................

<O59 P20010="" A1381="4" A1382="1" A1383="1" A1="59/3182" Build="5325"/>

</EplanPxfRoot>

 

 

Man reikia paimti tik P20010 reikšmes. Kur P20010 tuščias, tai jau pats su ifu nusiimčiau, bet esmė yra kaip atsidaryti skaitymui dalimis xml failą ir foreach cikle rasti būtent tas reikšmes. Jeigu ką keičia kiekvienos eilutės pradžia gali skirtis tarkim pvz O17 ir O59. Taip excel atidaręs xml failą, mano reikalingo stulpelio headerį rodo, ne P20010,o P2001013, nors ieškodamas xml failę reikšmės 2001013 nematau.

 

Skaičiau pavyzdžius internete http://stackoverflow.com/questions/15068009/how-to-open-a-xml-file-in-excel , bet nesusidūrus su xml struktūra tai atrodo sudėtingai ir neaišku kada vėl gyvenime prireiks.

 

Gal kas galėtū paaišktinti kaip stackoverflow pavyzdį adaptuoti mano reikalingos P20010 reikšmėms.

Nuoroda į pranešimą
Dalintis kituose puslapiuose
saknis

saknis 81

Atrašyta
Atrašyta

Pradzia, 

Sub MacroXML1()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant

' File filters
Filter = "XML Files (*.xml),*.xml"
' Default Filter to *.*

FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"

' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:")
With Application
   ' Set File Name to selected File
   Filename = .GetOpenFilename(Filter, FilterIndex, Title)
   ' Reset Start Drive/Path
   ChDrive (Left(.DefaultFilePath, 1))
   ChDir (.DefaultFilePath)
End With

' Exit on Cancel
If Filename = False Then
   Exit Sub
End If
Workbooks.OpenXML Filename:=Filename, LoadOption:=xlXmlLoadImportToList
End Sub

Ikelk kur nors XML, gal galima bus paziureti

Nuoroda į pranešimą
Dalintis kituose puslapiuose
Deviltry

Deviltry 107

Atrašyta
Atrašyta

Pradzia, 

Sub MacroXML1()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant

' File filters
Filter = "XML Files (*.xml),*.xml"
' Default Filter to *.*

FilterIndex = 3
' Set Dialog Caption
Title = "Select a File to Open"

' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:")
With Application
   ' Set File Name to selected File
   Filename = .GetOpenFilename(Filter, FilterIndex, Title)
   ' Reset Start Drive/Path
   ChDrive (Left(.DefaultFilePath, 1))
   ChDir (.DefaultFilePath)
End With

' Exit on Cancel
If Filename = False Then
   Exit Sub
End If
Workbooks.OpenXML Filename:=Filename, LoadOption:=xlXmlLoadImportToList
End Sub

Ikelk kur nors XML, gal galima bus paziureti

<?xml version="1.0" encoding="UTF-8"?>

 

-<EplanPxfRoot StreamSchema="EBitmap,BaseTypes,2,1,2;EPosition3D,BaseTypes,0,3,2;ERay3D,BaseTypes,0,4,2;EStreamableVector,BaseTypes,0,5,2;Arc3d@W3D,W3dBaseGeometry,0,16,2;Box3d@W3D,W3dBaseGeometry,0,17,2;Circle3d@W3D,W3dBaseGeometry,0,18,2;Color@W3D,W3dBaseGeometry,0,19,2;ContourPlane3d@W3D,W3dBaseGeometry,0,20,2;CTexture@W3D,W3dBaseGeometry,0,21,2;CTextureMap@W3D,W3dBaseGeometry,0,22,2;Frustum3d@W3D,W3dBaseGeometry,0,23,2;Line3d@W3D,W3dBaseGeometry,0,24,2;Linetype@W3D,W3dBaseGeometry,0,25,2;Material@W3D,W3dBaseGeometry,3,26,2;Path3d@W3D,W3dBaseGeometry,0,27,2;DMNCDataSet,TrDMProject,1,29,2;DMNCDataSetVector,TrDMProject,1,30,2;DMPlaceHolderRuleData,TrDMProject,0,31,2;Mesh3dX@W3D,W3dMeshModeller,2,33,2;MeshBox@W3D,W3dMeshModeller,5,34,2;MeshMate@W3D,W3dMeshModeller,7,35,2;MeshMateFace@W3D,W3dMeshModeller,1,36,2;MeshMateGrid@W3D,W3dMeshModeller,8,37,2;MeshMateGridLine@W3D,W3dMeshModeller,1,38,2;MeshMateLine@W3D,W3dMeshModeller,1,39,2;MeshText3dX@W3D,W3dMeshModeller,0,41,2;BaseTextLine@W3D,W3dMeshModeller,2,42,2;Mesh3d@W3D,W3dMeshModeller,8,43,2;MeshEdge3d@W3D,W3dMeshModeller,0,44,2;MeshFace3d@W3D,W3dMeshModeller,2,45,2;MeshPoint3d@W3D,W3dMeshModeller,1,46,2;MeshPolygon3d@W3D,W3dMeshModeller,1,47,2;MeshSimpleTextureTriangle3d@W3D,W3dMeshModeller,2,48,2;MeshSimpleTriangle3d@W3D,W3dMeshModeller,1,49,2;MeshTriangle3d@W3D,W3dMeshModeller,2,50,2;MeshTriangleFace3d@W3D,W3dMeshModeller,0,51,2;MeshTriangleFaceEdge3D@W3D,W3dMeshModeller,0,52,2;BaseSolidBlock@W3D,W3dBaseModeller,0,53,2;BaseSolidDiskSweep@W3D,W3dBaseModeller,0,54,2;BaseSolidExtrusion@W3D,W3dBaseModeller,0,55,2;BaseSolidSweep@W3D,W3dBaseModeller,0,56,2" NumMDSteps="0" NumProjectSteps="5121" NumMainObjects="5121" ConfigurationFlags="9153" Description="" SourceProject="" Source="" SchemaVersion="1.7.5325" PxfVersion="1.22" Version="2.1.5325" Type="DataConfiguratorMacro" Name="~data_configurator">

 

<?xml version="1.0" encoding="UTF-8"?>

 

 

 

-<EplanPxfRoot StreamSchema="EBitmap,BaseTypes,2,1,2;EPosition3D,BaseTypes,0,3,2;ERay3D,BaseTypes,0,4,2;EStreamableVector,BaseTypes,0,5,2;Arc3d@W3D,W3dBaseGeometry,0,16,2;Box3d@W3D,W3dBaseGeometry,0,17,2;Circle3d@W3D,W3dBaseGeometry,0,18,2;Color@W3D,W3dBaseGeometry,0,19,2;ContourPlane3d@W3D,W3dBaseGeometry,0,20,2;CTexture@W3D,W3dBaseGeometry,0,21,2;CTextureMap@W3D,W3dBaseGeometry,0,22,2;Frustum3d@W3D,W3dBaseGeometry,0,23,2;Line3d@W3D,W3dBaseGeometry,0,24,2;Linetype@W3D,W3dBaseGeometry,0,25,2;Material@W3D,W3dBaseGeometry,3,26,2;Path3d@W3D,W3dBaseGeometry,0,27,2;DMNCDataSet,TrDMProject,1,29,2;DMNCDataSetVector,TrDMProject,1,30,2;DMPlaceHolderRuleData,TrDMProject,0,31,2;Mesh3dX@W3D,W3dMeshModeller,2,33,2;MeshBox@W3D,W3dMeshModeller,5,34,2;MeshMate@W3D,W3dMeshModeller,7,35,2;MeshMateFace@W3D,W3dMeshModeller,1,36,2;MeshMateGrid@W3D,W3dMeshModeller,8,37,2;MeshMateGridLine@W3D,W3dMeshModeller,1,38,2;MeshMateLine@W3D,W3dMeshModeller,1,39,2;MeshText3dX@W3D,W3dMeshModeller,0,41,2;BaseTextLine@W3D,W3dMeshModeller,2,42,2;Mesh3d@W3D,W3dMeshModeller,8,43,2;MeshEdge3d@W3D,W3dMeshModeller,0,44,2;MeshFace3d@W3D,W3dMeshModeller,2,45,2;MeshPoint3d@W3D,W3dMeshModeller,1,46,2;MeshPolygon3d@W3D,W3dMeshModeller,1,47,2;MeshSimpleTextureTriangle3d@W3D,W3dMeshModeller,2,48,2;MeshSimpleTriangle3d@W3D,W3dMeshModeller,1,49,2;MeshTriangle3d@W3D,W3dMeshModeller,2,50,2;MeshTriangleFace3d@W3D,W3dMeshModeller,0,51,2;MeshTriangleFaceEdge3D@W3D,W3dMeshModeller,0,52,2;BaseSolidBlock@W3D,W3dBaseModeller,0,53,2;BaseSolidDiskSweep@W3D,W3dBaseModeller,0,54,2;BaseSolidExtrusion@W3D,W3dBaseModeller,0,55,2;BaseSolidSweep@W3D,W3dBaseModeller,0,56,2" NumMDSteps="0" NumProjectSteps="5121" NumMainObjects="5121" ConfigurationFlags="9153" Description="" SourceProject="" Source="" SchemaVersion="1.7.5325" PxfVersion="1.22" Version="2.1.5325" Type="DataConfiguratorMacro" Name="~data_configurator">

 

 

-<DataConfiguration languages="en_US,lt_LT">

 

<XMemberDataEntry Description="" Name="A1" Readonly="0" ID="60001"/>

 

<XMemberDataEntry Description="Function definition" Name="A1383" Readonly="0" ID="61383"/>

 

<XMemberDataEntry Description="Function group" Name="A1382" Readonly="0" ID="61382"/>

 

<XMemberDataEntry Description="Function category" Name="A1381" Readonly="0" ID="61381"/>

 

<XPropertyEntry Description="DT (displayed)" Name="P20010" Readonly="0" ID="20010" Index="0"/>

 

</DataConfiguration>

 

<O59 P20010="" A1381="4" A1382="1" A1383="1" A1="59/37946" Build="5325"/>

 

................................

<O17 P20010="" A1381="100" A1382="5" A1383="0" A1="17/1072" Build="5325"/>

................................

 

<O17 P20010="" A1381="100" A1382="5" A1383="0" A1="17/1191" Build="5325"/>

 

<O17 P20010="-10K1-M10" A1381="200" A1382="1" A1383="1" A1="17/1016" Build="5325"/>

 

<O17 P20010="-10K1-M10" A1381="201" A1382="1" A1383="2" A1="17/1018" Build="5325"/>

................................

 

<O59 P20010="" A1381="4" A1382="1" A1383="1" A1="59/3182" Build="5325"/>

 

</EplanPxfRoot>

:D

Nuoroda į pranešimą
Dalintis kituose puslapiuose

Prisijunkite prie diskusijos

Jūs galite rašyti dabar, o registruotis vėliau. Jeigu turite paskyrą, prisijunkite dabar, kad rašytumėte iš savo paskyros.

Svečias
Parašykite atsakymą...

×   Įdėta kaip raiškusis tekstas.   Atkurti formatavimą

  Only 75 emoji are allowed.

×   Nuorodos turinys įdėtas automatiškai.   Rodyti kaip įprastą nuorodą

×   Jūsų anksčiau įrašytas turinys buvo atkurtas.   Išvalyti redaktorių

×   You cannot paste images directly. Upload or insert images from URL.

Įkraunama...
×
 Dalintis
Sekėjai 0
Eiti į temų sąrašą

  • Dabar naršo   0 narių

    Nei vienas registruotas narys šiuo metu nežiūri šio puslapio.

  • Prisijunk prie bendruomenės dabar!

    Uždarbis.lt nariai domisi verslo, IT ir asmeninio tobulėjimo temomis, kartu sprendžia problemas, dalinasi žiniomis ir idėjomis, sutinka būsimus verslo partnerius ir dalyvauja gyvuose susitikimuose.

    Užsiregistruok dabar ir galėsi:

    ✔️ Dalyvauti diskusijose;

    ✔️ Kurti naujas temas;

    ✔️ Rašyti atsakymus;

    ✔️ Vertinti kitų žmonių pranešimus;

    ✔️ Susisiekti su bet kuriuo nariu asmeniškai;

    ✔️ Naudotis tamsia dizaino versija;

    ir dar daugiau.

    Registracija trunka ~30 sek. ir yra visiškai nemokama.

  • Naujausios temos

  • Karštos temos

×
×
  • Pasirinkite naujai kuriamo turinio tipą...