在Excel(2007)VBA中读取和更改多个XML文件 [英] Read and change multiple XML files in Excel (2007) VBA

问题描述

我正在尝试读取一个充满XML文件的文件夹，并将引用号更改为一种特定的格式，即今天的DATE(yymmdd)，首字母缩写，从00000001开始的8位数字引用

I'm trying to read a folder full of XML files and change the reference number into a specific format which is Today's DATE(yymmdd), Initials, 8 digit reference starting at 00000001

例如120815AB00000001，然后是120815AB00000002，等等.每个文件都有一个参考号.它包含在<中.CPAReferenceNumber>标记.

e.g 120815AB00000001 then 120815AB00000002 etc. Each file has ONE reference number. It is enclosed in the < CPAReferenceNumber> tag.

我正在使用Excel和VBA读取文件并更改相关字段.引用设置为默认值(在下面的代码中，现在将"This"更改为"That")

I'm using Excel and VBA to read the files and change the relevant field. The reference is set to a default value (in the code below it changes 'This' into 'That' for now)

此代码可用于一个单独的文件，并进行正确的更改.这些文件具有随机名称，并且没有适当的命名约定.我无法将其扩展到文件夹中的所有XML文件.任何帮助将不胜感激.

This code works on one individual file and makes the correct change. The files have random names and there is no naming convention in place. I'm unable to expand this out to all XML files in the folder. Any help would be greatly appreciated.

Sub ReplaceStringInFile()


Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String



sFileName = "c:\Search and Replace Files\blah.XML"
iFileNum = FreeFile

Open sFileName For Input As iFileNum
    Do Until EOF(iFileNum)

            Line Input #iFileNum, sBuf
            sTemp = sTemp & sBuf & vbCrLf

    Loop


Close iFileNum
sTemp = Replace(sTemp, "THIS", "THAT")

iFileNum = FreeFile

Open sFileName For Output As iFileNum
Print #iFileNum, sTemp

Close iFileNum

End Sub

在帮助"中，我已将其修改为以下代码，但这会导致错误:sFileName不在上下文中.文本更改不适用于xml文件.

After Help I have modified it to the below code, this is however causing errors: sFileName is out of context. The text change is not applied to the xml files.

Sub ReplaceStringInFile()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Const sSearchString As String = "c:\blah\*.xml"
Const directoryString As String = "c:\blah\"


iFileNum = FreeFile

sFileName = Dir(sSearchString)

Do While sFileName <> ""

      Open directoryString & sFileName For Input As iFileNum

        Do Until EOF(iFileNum)

                Line Input #iFileNum, sBuf
                sTemp = sTemp & sBuf & vbCrLf

        Loop


        Close iFileNum
        sTemp = Replace(sTemp, "IDNUMBER", "******SUCCESS!!!!!!******")

        iFileNum = FreeFile

        Open sFileName For Output As iFileNum
        Print #iFileNum, sTemp

        Close iFileNum

        Debug.Print "Do something with file named " & sFileName
        sFileName = Dir()

Loop


End Sub

推荐答案

使用 Dir 命令搜索文件.

以下示例将遍历 C:\ Temp 中的所有XML文件，并返回文件名(不包含路径):

The following example will loop through all XML-files in C:\Temp and return the file names (without the path):

Const sSearchString As String = "c:\temp\*.xml"
Dim sFileName As String

sFileName = Dir(sSearchString)
Do While sFileName <> ""
    Debug.Print "Do something with file named " & sFileName
    sFileName = Dir()
Loop

现在，如果我将您的原始代码和我的 Dir 循环结合在一起，那么我会在我的环境中得到一些有用的东西，希望它对你有用.我认为您忘记的是 sFileName 仅包含文件名而不包含完整路径-因此您写入的文件与读取的文件不同，并且可能混淆了 s While sFileName<>;" 同时循环:

Now, if I combine your original code and my Dir loop, I get something that works in my environment, hopefully it will work for you. What I think you forgot was that sFileName only contains the filename and not the full path - so you wrote to a different file than what you read from and maybe confused the Do While sFileName <> "" loop at the same time:

Sub ReplaceStringInFile()

    Const sSearchString As String = "c:\temp\*.xml"

    Dim sBuf As String
    Dim sTemp As String
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim sFilePath As String


    sFileName = Dir(sSearchString)

    Do While sFileName <> ""

        sFilePath = "c:\temp\" & sFileName  'Get full path to file
        iFileNum = FreeFile
        sTemp = ""  'Clear sTemp

        Open sFilePath For Input As iFileNum

            Do Until EOF(iFileNum)

                Line Input #iFileNum, sBuf
                sTemp = sTemp & sBuf & vbCrLf

            Loop

        Close iFileNum

        sTemp = Replace(sTemp, "THIS", "THAT")

        iFileNum = FreeFile

        Open sFilePath For Output As iFileNum
        Print #iFileNum, sTemp

        Close iFileNum

        sFileName = Dir() 'Get the next file
    Loop
End Sub

这篇关于在Excel(2007)VBA中读取和更改多个XML文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！