Saving .txt as .csv cancels all changes made by macro in the file. How to prevent it?

Question

The issue continues this topic and is associated with my earlier post. The code should deal with .csv files changing cells' values and interior color. It does its job, but after saving .txt as .csv I end up with something that looks like the source file - no changes visible.

I thought of using dictionaries, but to my understanding, to do so, I had to edit the newly saved .csv, which is exactly what I am trying to avoid in the below code. Does anyone have an idea how to have the changes saved?

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

IF you save as a CSV you're essentially saving to another kind of text file. Text files cannot contain formatting - only workbooks (xlsx, etc.) can save formatting instructions. — Cindy Meister, Feb 05 '19 at 21:24
@Cindy Meister Then no interior color, i.e. no marking, which cells have been changed. But why I am loosing also the impact of the =Left function `Sh.Cells(i, 5) = Left$(Sh.Cells(i, 5).Value, 10)` ? — Drzemlik, Feb 05 '19 at 21:36
As @CindyMeister said, there are no formatting identifiers in a txt or csv file (nothing built-in, in any case). As an option, you could add some "tag" character to a changed cell using your code, e.g. `*` where the `*` would indicate (to you and your users) that it is a changed value. But it's up to you if you can modify the cell contents in this way. — PeterT, Feb 05 '19 at 21:58
All the various file names your code is using isn't completely clear, but near the end you "kill" a file - is this the file the changes are being written to? — Cindy Meister, Feb 05 '19 at 22:00
@ Cindy Meister Yes, changes are written to the "killed" TmpFlName. Two files are used in the process. The other file is SomeFile.csv that is supposed to be saved with the changes - or was supposed to, I should say. — Drzemlik, Feb 05 '19 at 22:12
I'm kind of surprised that this works at all - when you open a `.txt` file in Excel it doesn't generate a dummy `ThisWorkbook` module for it - only a `Sheet1`. It doesn't seem to instantiate that until you add another worksheet to it. That said, you should really be using the `Scripting.FileSystemObject` for this. Do you have any event handlers anywhere that are calling `Dir`? — Comintern, Feb 05 '19 at 23:03
Are you saying you are changing excel cell values (i.e. only showing first ten characters) then saving as CSV, but when you open up the CSV the change doesn't appear? Going by the comments I would guess that you are not saving or opening what you think you are. What really is the overall objective of this and your prior questions? If the final result is a CSV file then Excel is not even required in this whole process (and things will probably become a lot simpler) — Nick.Mc, Feb 06 '19 at 06:31
Here are some guides to working with CSV in Powershell. https://trevorsullivan.net/2015/08/06/working-with-csv-files-in-powershell/ https://stackoverflow.com/questions/16682744/read-a-csv-file-with-powershell-and-capture-corresponding-data — Nick.Mc, Feb 06 '19 at 06:33
@Comintern I have no idea how to implement `Scripting.FileSystemObject` to this code, I will do research later. The question about event handlers.., I suppose I don't use them as I don't know what they are. I've had a look [here](https://support.microsoft.com/en-us/help/213566/how-to-create-application-level-event-handlers-in-excel), but this is sth I have yet to learn. — Drzemlik, Feb 06 '19 at 20:47

Ahmed AU · Accepted Answer · 2019-02-10T15:50:23.550

What looked as a simple opening semicolon delimited text /cvs file in your earlier post is now looks complicated. Even overlooking other issues, in my trial, I find while saving txt/csv files from excel it may introduce some double quote in the saved file (depending on position of comma, spaces and semicolon in a line). May refer links (Saving a Excel File into .txt format without quotes) and link and link2

As what I understand, your requirement is simple to truncate 5th column of the semicolon delimited file with csv extension and save it back, the simple approach of may solve your the problem. However, I still not satisfied with the workaround approach and invite more simple and straight approach to solve the problem (consisting txt file with comma, spaces and semicolons, while semicolon is to be treated as delimiter)

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

This is the result input & Output

as you said I'm simply trying to truncate col 5 of .csv file (and save the changes). Only after reading your answer did I realize that I complicated things and mislead potential troubleshooters. I can see in Locals window that your code works, though for now I can't adapt it so it would save .csv with the above changes. I will work on it. Thank you, accepted for working code, links with referrence and making me understand my own question better. — Drzemlik, Feb 06 '19 at 21:05
it is a pleasure to be useful. Be free with any problems in adapting the code. — Ahmed AU, Feb 07 '19 at 00:23

Saving .txt as .csv cancels all changes made by macro in the file. How to prevent it?

1 Answers1

Linked