将 .txt 保存为 .csv 将取消宏在文件中所做的所有更改。如何预防呢？

问题仍在继续this https://stackoverflow.com/q/54523166/10348607主题并与之相关我之前的帖子 https://stackoverflow.com/q/54488624/10348607。 该代码应处理更改单元格值和内部颜色的 .csv 文件。它完成了它的工作，但是在将 .txt 保存为 .csv 后，我最终得到了一些看起来像源文件的东西 - 没有可见的更改。

我想过使用字典，但据我了解，为此，我必须编辑新保存的 .csv，这正是我在下面的代码中试图避免的。有谁知道如何保存更改？

Option Explicit

Sub fixCellsValue()
Dim wrk As Workbook
Dim Sh As Worksheet
Dim SourceFolder As String, Path As String, TmpFlName As String
Dim i As Long, lastrow As Long

SourceFolder = ThisWorkbook.Path & "\source"

'creating temporary .txt file
If Dir(SourceFolder & "SomeFile.*") <> "" Then
    If InStr(1, Dir(SourceFolder & "SomeFile.*"), ".csv") Then
                    TmpFlName = SourceFolder & "\TmpCsv.txt"
                    If Dir(TmpFlName) <> "" Then Kill TmpFlName
                    FileCopy SourceFolder & "SomeFile.csv", TmpFlName
                    Workbooks.OpenText Filename:=TmpFlName, origin:= _
                    1250, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
                    , Space:=False, Other:=False, TrailingMinusNumbers:=True, Local:=False

                    Set wrk = Application.Workbooks("TmpCsv.txt")
                    Set Sh = wrk.Worksheets(1)

        lastrow = Sh.Cells(Sh.Rows.Count, "A").End(xlUp).row

        'implementing changes to the temporary .txt file
        For i = 2 To lastrow
            If Len(Sh.Cells(i, 5)) > 10 Then
                Sh.Cells(i, 5) = Left$(Sh.Cells(i, 5).Value, 10)
                Sh.Cells(i, 5).Interior.ColorIndex = 6
            End If
        Next i

    End If
End If

'saving as .csv file and deleting .txt file
If InStr(1, wrk.Name, "TmpCsv.txt") Then
    wrk.SaveAs Filename:=Dir(SourceFolder & "SomeFile.*"), FileFormat:=xlCSV, Local:=True
    wrk.Close Savechanges:=True
    Kill TmpFlName
End If
End Sub

看起来像是一个简单的以分号分隔的文本 /csv 文件较早的帖子 https://stackoverflow.com/questions/54488624/macro-separates-csv-by-comma-despite-separator-set-to-semicolon/54489108#54489108现在看起来很复杂。即使忽略其他问题，在我的试验中，我发现在从 Excel 保存 txt/csv 文件时，它可能会在保存的文件中引入一些双引号（取决于行中逗号、空格和分号的位置）。可以参考链接（将 Excel 文件保存为不带引号的 .txt 格式 https://stackoverflow.com/questions/11501531/saving-a-excel-file-into-txt-format-without-quotes) and link https://excel.tips.net/T002237_Getting_Rid_of_Extra_Quote_Marks_in_Exported_Text_Files.html and link2 https://superuser.com/questions/349882/how-to-avoid-double-quotes-when-saving-excel-file-as-unicode

据我了解，您的要求很简单，就是截断带有 csv 扩展名的分号分隔文件的第 5 列并将其保存回来，这种简单的方法可能会解决您的问题。然而，我仍然对解决方法不满意，邀请更简单直接的方法来解决问题m（由逗号、空格和分号组成的txt文件，分号被视为分隔符）

Try

Sub test2()
Dim Fname As String, Path As String, Txt As String, Txt2 As String
Dim INum As Integer, ONum As Integer, TrucTo As Integer, ColNo As Long
Dim Cols As Variant

' Modify the variables to your requirement
Path = "C:\Users\user\Desktop\"
Fname = "Somefile.csv"     ' Target file name
Fname2 = "Somefile2.csv"   ' Temp file name
TrucTo = 10                ' truncated to chars
ColNo = 4                   '  column to be truncated -1

If Dir(Path & Fname2) <> "" Then Kill Path & Fname2


INum = FreeFile
Open Path & Fname For Input As #INum
ONum = FreeFile
Open Path & Fname2 For Output As #ONum

Do Until EOF(1)
    Line Input #1, Txt
    Cols = Split(Txt, ";")
        If UBound(Cols) >= ColNo Then
            If Len(Cols(ColNo)) >= truncto Then
            Cols(ColNo) = Left(Cols(ColNo), TrucTo)
            End If
        End If
    Txt2 = Join(Cols, ";")
    Print #ONum, Txt2
Loop
Close #ONum
Close #INum


Kill Path & Fname
Name Path & Fname2 As Path & Fname


End Sub

这是结果输入和输出 https://i.stack.imgur.com/cGGkJ.jpg