代码之家 › 专栏 › 技术社区 › Jean

在CSV文件VBA中选择特定行

csv excel vba

Jean · 技术社区 · 7 年前

这个问题与 my previous question . 毕竟,所需要的只是删除拖尾“在特定的行上(1,2,最后一行和之前的行),所以我想知道是否有方法在CSV中选择行,以便我可以在这些特定行上使用我的函数。

对于我之前的问题,我现在得到的解决方案是:

Option Explicit
Public Function RemoveTrailing(s As String) As String
  Dim nIndex As Integer

  For nIndex = Len(s) To 1 Step -1
    If Right$(s, 1) = ";" Then
      s = Left$(s, Len(s) - 1)
    End If
  Next
  RemoveTrailing = s

End Function

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook

    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Copy

    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With

    MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    Dim sFile2 As String
    Dim sLine As String

    sFile2 = Replace(MyFileName, ".csv", "SANS_VIDE.csv")
    Open MyFileName For Input As #1
    Open sFile2 For Output As #2

    Do Until EOF(1)
        Line Input #1, sLine
        Print #2, RemoveTrailing(sLine)
    Loop

    Close #1
    Close #2
End Sub

我需要的不是循环:

Do Until EOF(1)
            Line Input #1, sLine
            Print #2, RemoveTrailing(sLine)
        Loop

仅在第一行、第二行、最后一行和前一行应用“RemoveTraily”功能。

我该怎么做?我是VBA新手。

1 回复 | 直到 7 年前

CLR 7 年前

做到这一点的一种方法是知道文件中有多少行,所以先通读一遍,然后数一数。然后,当您编写它们时,执行 RemoveTrailing 如果输出的行是第一行、第二行、倒数第二行或最后一行。。

尝试以下操作:

Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteFormats
End With

MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
Dim sFile2 As String
Dim sLine As String

sFile2 = Replace(MyFileName, ".csv", "SANS_VIDE.csv")

Dim rowcount As Long, thisrow As Long

Open MyFileName For Input As #1
rowcount = 0
Do Until EOF(1)
    Line Input #1, sLine
    rowcount = rowcount + 1
Loop
Close #1

Open MyFileName For Input As #1
Open sFile2 For Output As #2
thisrow = 0
Do Until EOF(1)
    thisrow = thisrow + 1
    Line Input #1, sLine
    If thisrow <= 2 Or thisrow >= rowcount - 1 Then
        Print #2, RemoveTrailing(sLine)
    Else
        Print #2, sLine
    End If
Loop
Close #1
Close #2

End Sub

推荐文章