时间戳

首页 > 解决方案 > 使用vba写出xml文件跳过空格

excel - 使用vba写出xml文件跳过空格

问题描述

我在excel中使用vba写出一个xml文件。我正在循环并使用引用循环中单元格值的常量进行替换。我不太明白的是如何在单元格为空时跳过写出一个部分。如果引用的单元格为空白，我想跳过循环单元格 a(i, 3) 并且不写该行，但我想继续进行下一个替换。见相关代码：

Const head = "<?xml version ""1.0"" encoding=""UTF-8""?>"
Const funct1 = "<Order function=""%%"" "
Const itemtype = "        <Item type=""%%"">"
Dim all
Dim xml As String, i As Long
Dim oPath As String: oPath = "C:\test\test.xml"
Dim intFF As Integer: intFF = FreeFile()
a = Range("A2:BU" & Cells(Rows.Count, 73).End(xlUp).Row)
xml = head
For i = 1 To UBound(a)
   xml = xml & vbNewLine & Replace(funct1, "%%", a(i, 3))
               vbNewLine & Replace(itemtype, "%%", a(i, 31))
Next
Open oPath For Output As #intFF
Print #intFF, xml
Close #intFF

Dim sBuf As String
Dim sTemp As String
Dim int2FF As Integer: int2FF = FreeFile()
Dim sFileName As String
sFileName = "C:\test\test.xml"
Open sFileName For Input As int2FF
Do Until EOF(int2FF)
    Line Input #int2FF, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close int2FF

标签： excelxmlvba

解决方案

测试每个循环的字符数。如果字符数 = 0( Len(a(i, 3) = 0)) 则转到下一个i。您将需要添加j为计数器，并且仅在您跳入XML语句时才增加。

Dim j As Long: j = 1

For i = 1 To UBound(a)
    If Len(a(i, 3)) <> 0 Then
        XML = XML & vbNewLine & Replace(funct1, "%%", a(i, 3)) _
        & vbNewLine & Replace(itemtype, "%%", a(i, 31))
        j = j + 1
    End If
Next

推荐阅读