在Excel 2019上使用宏编辑XML文件

azpvetkf 于 2023-11-20 发布在其他

关注(0)|答案(2)|浏览(135)

希望你们都做得很好！
我试图编辑的XML文件是由我的数控机床产生的束。
所有我想做的是，如果在（是变量INT）之前，它必须将值替换为我在表中的数字。表由Item标记和QTY组成（QTY值将替换，Item标记将是XML文件的名称）
我尝试创建宏，只是为了将替换为1以开始，但这根本不起作用。尝试使用CHATGPT找到解决方案。下面是生成的宏。附件是文件的示例

Sub EditXMLFile()
    Dim SourceFile As String
    Dim DestFolder As String
    Dim FileDialog As FileDialog
    Dim XMLDoc As Object
    Dim TextToReplace As String
    Dim NewText As String
    
    ' Prompt user to select the XML file
    Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)
    FileDialog.Title = "Select the XML file to edit"
    FileDialog.Filters.Clear
    FileDialog.Filters.Add "XML Files", "*.xml"
    
    If FileDialog.Show = -1 Then
        SourceFile = FileDialog.SelectedItems(1)
    Else
        Exit Sub
    End If
    
    ' Prompt user for destination folder
    Set FileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If FileDialog.Show = -1 Then
        DestFolder = FileDialog.SelectedItems(1)
    Else
        Exit Sub
    End If
    
    ' Load the selected XML file
    Set XMLDoc = CreateObject("MSXML2.DOMDocument.6.0")
    XMLDoc.async = False
    XMLDoc.Load SourceFile
    
    ' Define the text to replace and the new text
    TextToReplace = "<NUM>*</NUM>"
    NewText = "<NUM>1</NUM>"
    
    ' Start the recursive search and replace
    ReplaceTextRecursively XMLDoc, TextToReplace, NewText
    
    ' Save the modified XML file to the destination folder
    XMLDoc.Save DestFolder & "\" & "ModifiedXML.xml"
    
    ' Clean up
    Set XMLDoc = Nothing
    Set FileDialog = Nothing
    
    MsgBox "XML file edited and saved successfully!", vbInformation
End Sub

Sub ReplaceTextRecursively(ByRef Node As Object, ByVal TextToReplace As String, ByVal NewText As String)
    Dim ChildNode As Object
    
    If Node Is Nothing Then Exit Sub
    
    If Node.HasChildNodes Then
        For Each ChildNode In Node.ChildNodes
            ReplaceTextRecursively ChildNode, TextToReplace, NewText
        Next ChildNode
    End If
    
    If Node.NodeType = 3 Then ' Text node
        If InStr(Node.Text, "<CUT>") > 0 Then
            Node.Text = Replace(Node.Text, TextToReplace, NewText)
        End If
    End If
End Sub

字符串
需要编辑的XML文件内的代码（我希望附加文件）

<JOB>
    <JINF>
        <NUM>0</NUM>
    </JINF>
    <BODY>
        <BAR>
            <BRAN>TESTENVIRONMENT</BRAN>
            <SYST>7000 SERIES</SYST>
            <CODE>70-110_938</CODE>
            <DESC>
            </DESC>
            <DICL>
            </DICL>
            <DOCL>
            </DOCL>
            <LEN>0</LEN>
            <LENR>0</LENR>
            <H>146.5</H>
            <MLT>-1</MLT>
            <POS>0</POS>
            <ICL>
            </ICL>
            <OCL>
            </OCL>
            <SCRI>0</SCRI>
            <SCRF>0</SCRF>
            <LREUSAB>0</LREUSAB>
            <SCRP>0</SCRP>
            <CTIM>0</CTIM>
            <CTSP>0</CTSP>
            <SVL>0</SVL>
            <IVL>0</IVL>
            <VROT>0</VROT>
            <VU1S>0</VU1S>
            <VU1D>0</VU1D>
            <VU2S>0</VU2S>
            <VU2D>0</VU2D>
            <SCP>0</SCP>
            <BRS>0</BRS>
            <IFS>0</IFS>
            <BS1L>0</BS1L>
            <BS1R>0</BS1R>
            <BS2L>0</BS2L>
            <BS2R>0</BS2R>
            <PRPZ>0</PRPZ>
            <PRMA>0</PRMA>
            <PRMB>0</PRMB>
            <PRMO>0</PRMO>
            <PRMV>0</PRMV>
            <STMV>0</STMV>
            <MXBN>0</MXBN>
            <W>85</W>
            <ENTH>0</ENTH>
            <COLOR a="255" r="128" g="128" b="128"/>
            <DXF>
            </DXF>
            <REV>0</REV>
            <INNESTABILE>0</INNESTABILE>
            <NUM>1</NUM>
            <PRIORITA>0</PRIORITA>
            <STATOBARRA>0</STATOBARRA>
            <STATOCONGELATO>0</STATOCONGELATO>
            <MODELLO_CON_ERRORI>0</MODELLO_CON_ERRORI>
            <ID_ESTERNO>
            </ID_ESTERNO>
            <OFFESET_X>0</OFFESET_X>
            <OFFESET_Y>0</OFFESET_Y>
            <OFFESET_Z>0</OFFESET_Z>
            <OFFESET_YCLOSE>0</OFFESET_YCLOSE>
            <VIRTPLATE>
            </VIRTPLATE>
            <CUT>**<NUM>123</NUM>
**<NUMEXE>0</NUMEXE>
                <TINA>
                </TINA>
                <ANGL>90</ANGL>
                <ANGR>90</ANGR>
                <AB1>90</AB1>
                <AB2>90</AB2>
                <IL>1396.16998</IL>
                <OL>1396.16998</OL>
                <TRML>0</TRML>
                <TL1>0</TL1>
                <TAL>90</TAL>
                <TRMR>0</TRMR>
                <TL2>0</TL2>
                <TAR>90</TAR>
                <TLON>0</TLON>
                <TRON>0</TRON>
                <TLMTON>0</TLMTON>
                <TRMTON>0</TRMTON>
                <MARGIN1>0</MARGIN1>
                <MARGIN2>0</MARGIN2>
                <BCOD>938%70110.IN1E.0008-1396</BCOD>
                <EXTERNAL_ID>0</EXTERNAL_ID>
                <CSNA>
                </CSNA>
                <CSNU>
                </CSNU>
                <ORCD>70110.IN1E.0008-1396</ORCD>
                <ORDCDQTY>1</ORDCDQTY>
                <DESC>
                </DESC>
                <CID>
                </CID>
                <IDQUADRO>
                </IDQUADRO>
                <FRNU>
                </FRNU>
                <STAT>0</STAT>
                <SUBAREA_BARRA>0</SUBAREA_BARRA>
                <PEZZO_RIFATTO>0</PEZZO_RIFATTO>
                <RT>
                    <Vx x="1" y="0" z="0"/>
                    <Vy x="0" y="1" z="0"/>
                    <T x="0" y="0" z="0"/>
                </RT>
                <AREA>0</AREA>
                <STOP>0</STOP>
                <PRINT_LABEL>1</PRINT_LABEL>
                <LBL>
                </LBL>
                <LBL>938%70110.IN1E.0008-1396</LBL>
                <LBL>
                </LBL>
                <LBL>LAVORARE</LBL>
                <MACHININGS>
                    <MACHINING WCODE="#0" OFFSET="1296.17" OFFSETY="127.5"
                               OFFSETZ="-3.15" FORMULAX="" FORMULAY=""
                               FORMULAZ="" FACE="2" TIPOLAV="" VAR1="5.5"
                               VAR2="" VAR3="" VAR4="" VAR5="" ANGLE="0"
                               VERPERC="1" P1X="0" P1Y="0" P1Z="0" P2X="0"
                               P2Y="0" P2Z="0" P3X="0" P3Y="0" P3Z="0"
                               INVERSEPATHONMULTIPLANES="0" EMPTYGROVE="0"
                               BREAKSHAVING="0" CODUTENSILE="35" CILINLAV="2"
                               OFFSETFIN="0" CODUTEFIN="0" CILLAVFIN="0"
                               DESCRIPTION="Drill D11.000" SGRAVANZ="0"
                               SGRUSCITA="0" SRGLAV="0" SGRROT="0"
                               VARPROFSGR="0" FINAVANZ="0" FINUSCITA="0"
                               FINLAV="0" FINROT="0" VARPROFFIN="0"
                               EXECUTIONLEVEL="1" IDCATEGORY="1" POSDEF=""
                               ENABLELOAD="" IDMACHINING="0" CLAMPNEAR="0"
                               STARTCONNECTION="0" ENDCONNECTION="0"
                               TIPOSTAZIONE="Lavorazione" STATION_ID=""
                               NUMEROSOTTOLAVORAZIONI="1"
                               NUMEROFORIANUBATRICE="0" NUMEROANUBATURE="0"
                               SGRTIPO="2" FINTIPO="0" STATION_EXTRA_DATA="10">
                        <FORBIDDENSPACES/>
                        <PIANI>
                            <PIANO START="0" THICK="10" AVANZAMENTO="0"
                                   ROTAZIONE="0"/>
                            <PIANO START="10" THICK="1.40822017993"
                                   AVANZAMENTO="0" ROTAZIONE="0"/>
                            <PIANO START="11.40822017993" THICK="0.013086202792"
                                   AVANZAMENTO="0" ROTAZIONE="0"/>
                        </PIANI>
                    </MACHINING>
                    <MACHINING WCODE="#0" OFFSET="100" OFFSETY="127.5"
                               OFFSETZ="-3.15" FORMULAX="" FORMULAY=""
                               FORMULAZ="" FACE="2" TIPOLAV="" VAR1="5.5"
                               VAR2="" VAR3="" VAR4="" VAR5="" ANGLE="0"
                               VERPERC="1" P1X="0" P1Y="0" P1Z="0" P2X="0"
                               P2Y="0" P2Z="0" P3X="0" P3Y="0" P3Z="0"
                               INVERSEPATHONMULTIPLANES="0" EMPTYGROVE="0"
                               BREAKSHAVING="0" CODUTENSILE="35" CILINLAV="2"
                               OFFSETFIN="0" CODUTEFIN="0" CILLAVFIN="0"
                               DESCRIPTION="Drill D11.000" SGRAVANZ="0"
                               SGRUSCITA="0" SRGLAV="0" SGRROT="0"
                               VARPROFSGR="0" FINAVANZ="0" FINUSCITA="0"
                               FINLAV="0" FINROT="0" VARPROFFIN="0"
                               EXECUTIONLEVEL="1" IDCATEGORY="2" POSDEF=""
                               ENABLELOAD="" IDMACHINING="0" CLAMPNEAR="0"
                               STARTCONNECTION="0" ENDCONNECTION="0"
                               TIPOSTAZIONE="Lavorazione" STATION_ID=""
                               NUMEROSOTTOLAVORAZIONI="1"
                               NUMEROFORIANUBATRICE="0" NUMEROANUBATURE="0"
                               SGRTIPO="2" FINTIPO="0" STATION_EXTRA_DATA="10">
                        <FORBIDDENSPACES/>
                        <PIANI>
                            <PIANO START="0" THICK="10" AVANZAMENTO="0"
                                   ROTAZIONE="0"/>
                            <PIANO START="10" THICK="1.408220179937"
                                   AVANZAMENTO="0" ROTAZIONE="0"/>
                            <PIANO START="11.408220179937"
                                   THICK="0.013086202769" AVANZAMENTO="0"
                                   ROTAZIONE="0"/>
                        </PIANI>
                    </MACHINING>
                </MACHININGS>
            </CUT>
            <AREE_ROVINATE/>
            <TIMES/>
            <TEMPI_CN_START>
            </TEMPI_CN_START>
            <TEMPI_CN_END>
            </TEMPI_CN_END>
        </BAR>
    </BODY>
    <JOBIMAGES NUMBER="0"/>
</JOB>

型

excel

来源：https://stackoverflow.com/questions/77454524/editing-xml-file-using-macro-on-excel-2019

2条答案

按热度按时间

sdnqo3pr1#

使用XPATH选择节点。

Option Explicit

Sub EditXML()

    Dim XMLDOC As Object, node As Object
    Dim sourcefile As String, newfile As String
    
    sourcefile = "job.xml"
    newfile = "Updated_" & sourcefile
    
    Set XMLDOC = CreateObject("Msxml2.DOMDocument.6.0")
    XMLDOC.Load sourcefile
    
    On Error Resume Next
    Set node = XMLDOC.SelectNodes("//CUT/NUM")(0)
    On Error GoTo 0
    
    If node Is Nothing Then
        MsgBox "Node CUT/NUM not found", vbCritical
        Exit Sub
    Else
        node.Text = 123456 ' new value
    End If
    
    XMLDOC.Save newfile
    MsgBox newfile & " saved", vbInformation

End Sub

字符串

赞(0）回复(0）举报 2023-11-20

sq1bmfud2#

选项1：您的代码即将完成。

Option Explicit
Sub EditXMLFile()
    Dim SourceFile As String
    Dim XMLDoc As Object
    Dim TextToReplace As String
    Dim NewText As String, Tag1 As String, Tag2 As String
    SourceFile = "D:\Temp\data.xml" ' modify as needed
    ' Load the selected XML file
    Set XMLDoc = CreateObject("MSXML2.DOMDocument.6.0")
    XMLDoc.async = False
    XMLDoc.Load SourceFile
    ' Define the text to replace and the new text
    Tag1 = "CUT"
    Tag2 = "NUM"
    NewText = "111"
    ' Start the recursive search and replace
    ReplaceTextRecursively XMLDoc, "", Tag1, Tag2, NewText
    ' Save the modified XML file to the destination folder
    XMLDoc.Save "d:\temp\" & "ModifiedXML.xml" ' modify as needed
    ' Clean up
    Set XMLDoc = Nothing
    MsgBox "XML file edited and saved successfully!", vbInformation
End Sub
Sub ReplaceTextRecursively(ByRef Node As Object, ByVal ParentTag As String, _
        ByVal Tag1 As String, ByVal Tag2 As String, ByVal NewText As String)
    Dim ChildNode As Object
    If Node Is Nothing Then Exit Sub
    If Node.HasChildNodes Then
        For Each ChildNode In Node.ChildNodes
            ReplaceTextRecursively ChildNode, Node.BaseName, Tag1, Tag2, NewText
        Next ChildNode
    End If
    If Node.NodeType = 1 Then
        If ParentTag = Tag1 And Node.BaseName = Tag2 Then
            Node.Text = NewText
        End If
    End If
End Sub

字符串
ReplaceTextRecursively可以用SelectNodes优化（我刚刚意识到@CDP1802之前发布了一个类似的解决方案）。

Sub UpdateNodeValues(ByRef xmlDoc As Object, ByVal Tag1 As String, _
     ByVal Tag2 As String, ByVal NewText As String)
    Dim nodes As Object
    Set nodes = xmlDoc.SelectNodes("//" & Tag1 & "/" & Tag2)
    If Not nodes Is Nothing Then
        For Each node In nodes
            node.Text = NewText
        Next node
    End If
End Sub

型
备选案文2：使用RegExp替换目标标记是一种简单的方法，但是，在处理复杂的XML数据时，它可能不如解析XML那么可靠。

将<CUT>*<NUM>*</NUM>替换为<CUT>*<NUM>88</NUM>
注意：* RegExp不是处理XML/HTML的最佳解决方案，请参考@Yitzhak Khabinsky的评论。

Option Explicit
Sub ReplaceNumInXML()
    Dim objFSO As Object
    Dim objFile As Object
    Dim sText As String
    Dim EndTag As String
    ' modify as needed
    Const PRETAG = "<CUT>"
    Const Tag = "<NUM>"
    Const NEWNUM = 88
    Const FILE_PATH = "D:\Temp\data.xml"
    ' **********
    ' Create ending tage
    EndTag = "</" & Mid(Tag, 2)
    ' Read xml file
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(FILE_PATH, 1)
    sText = objFile.ReadAll
    objFile.Close
    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .Pattern = "(" & PRETAG & ".*?" & Tag & ")\d+(" & EndTag & ")"
        ' Replace num
        sText = .Replace(sText, "$1" & NEWNUM & EndTag)
    End With
    ' Write result to new xml file
    Set objFile = objFSO.CreateTextFile(Replace(FILE_PATH, ".xml", "_new.xml"), True)
    objFile.Write sText
    objFile.Close
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub

型

赞(0）回复(0）举报 2023-11-20

我来回答

在Excel 2019上使用宏编辑XML文件

2条答案

相关问题

热门标签

最新问答