Why is this VBA script corrupting files?

Question

I've created a VBA script to save an xlsx file as a semi-colon-del CSV format, but unfortunately, it's corrupting the output/CSV file when it saves (in notebook I see only PK ). Can anyone give me insight as to why this is happening?

Specific use case: I want to be able to download a file (features_AFX.csv) from FTP that is semi-colon delimited, import into Excel using Power Query Editor (to retain special characters in the transformation), make edits, and then save/output as semi-colon delimited. Changing my regional settings to use semi-colon as the default delimiter is not practical as I usually work with commas as the delimiter and only need semi-colons for a few files. Additionally, I wanted to make sure the special characters within cells (which could also include semi-colons) were retained when exporting to csv.

• Note: all files are saved in filepath C:\Users\Julie

VBA script:

Private Sub commaReplace()

    Dim objFSO
    Dim filePath
    Dim migratorFileName
    Dim strFullPath1
    Dim strFullPath2
    Const ForReading = 1
    'define a TextStream object
    Dim objTS
    Dim strContents As String

    'note, my code actually uses the below commented out filepath
    'as the location of the workbook can be arbitrary, e.g.
    'Worksheets("FilePath").[A2:A2].Value is determined when workbook
    'is opened
    'filePath = Worksheets("FilePath").[A2:A2].Value
    filePath = "C:\Users\julie\"

    'our original file that we've exported as csv file in another section of code
    migratorFileName = "features_AFXMASTER.xlsx"
    strFullPath1 = filePath + migratorFileName

    'the path and file name we want to save to, semicolon separated vs. comma
    migratorFileName = "features_AFX.csv"
    strFullPath2 = filePath + migratorFileName

    'read everything from the csv file, replacing comma with semicolon
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTS = objFSO.OpenTextFile(strFullPath1, ForReading)
    strContents = objTS.ReadAll
    strContents = Replace(strContents, ",", ";")
    objTS.Close

    'write everything out to another file, note, this could just overwrite
    'the original file if you pass the optional overwrite flag
    Set objTS = objFSO.CreateTextFile(strFullPath2)
    objTS.Write strContents
    objTS.Close

End Sub

Thank you!! Julie

Tried:

Attempt 1: Downloaded features_AFX.csv (already semi-colon delimited) to C:\Users\Julie. Imported to Excel using Power Query Editor, and assigned file name features_AFXMASTER.xlsm. Appended VBA script to features_AFXMASTER.xlsm and ran the script. No error appeared and features_AFX file was updated, but on opening the file, it had been corrupted.

Attempt 2: Downloaded features_AFX.csv to C:\Users\Julie. Imported to Excel using Power Query Editor, and assigned file name features_AFXMASTER.xlsm. (file remained from Attempt 1 with VBA appended) Deleted features_AFX.csv. Ran VBA script in features_AFXMASTER. No error appeared and features_AFX.csv file was created. On opening the .csv, it was corrupted.

Attempt 3: Created a separate file named features_VBA.xlsm with VBA script appended. Downloaded features_AFX.csv to C:\Users\Julie. Imported to Excel using Power Query Editor, and assigned file name features_AFXMASTER.xlsm. (this was a newly-created file, not leftover from previous attempts) Deleted features_AFX.csv. Ran VBA script in features_VBA. No error appeared and features_AFX.csv file was created. On opening the .csv, it was corrupted.

Expected:

features_AFX.csv should be saved with semi-colons as the delimiter

Answer 1

I had a similar need and made a .vbs to call that would mass change in a flat file one character to another. It has three parameters

Const ForReading = 1
Const ForWriting = 2
strFileName = Wscript.Arguments(0)
strOldText = Wscript.Arguments(1)
strNewText = Wscript.Arguments(2)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, ForReading)
strText = objFile.ReadAll
objFile.Close
newText = Replace(strText, strOldText, strNewText)
Set objFile = objFSO.OpenTextFile(strFileName, ForWriting)
objFile.WriteLine newText
objFile.Close

Created a small test file 'TestDoc.txt': Col1,col2,col3,col4 Opened CMD prompt (in the folder containing the script: 'RawMod.vbs' and test file and ran rawmod.vbs "TestDoc.txt" "," "|" TestDoc.txt was updated to: Col1|col2|col3|col4

Please let me know if this is useful

Answer 2

It's not too tricky to export directly from Excel to a text file:

Sub Tester()
    PutContent "C:\Temp\test.csv", RangeToText(Selection, ";")
End Sub

'Create a string from the content of range `rng`, using `FieldSep`
'  to separate field values.  Quote any values containing `FieldSep`
Function RangeToText(rng As Range, FieldSep As String) As String
    Dim r As Long, c As Long, sep As String, content As String, v
    For r = 1 To rng.Rows.Count
        sep = ""
        For c = 1 To rng.Columns.Count
            v = rng.Cells(r, c).Value
            'quote any field values containing `FieldSep`
            If InStr(1, v, FieldSep, vbTextCompare) > 0 Then
                v = """" & v & """"
            End If
            content = content & sep & v
            sep = FieldSep 'populate after 1st value on the line
        Next c
        content = content & vbCrLf 'default Windows line separator
    Next r
    RangeToText = content
End Function

'Write text in `content` to a text file at path `f`
Sub PutContent(f As String, content As String)
    CreateObject("scripting.filesystemobject"). _
                  OpenTextFile(f, 2, True).Write content
End Sub

EDIT: if you need to write to a UTF-8 encoded file then you can use:

Sub WriteUTF8(filePath As String, content As String)
    With CreateObject("ADODB.Stream")
        .Type = 2 'adTypeText: text/string data
        .Charset = "utf-8" 'default is "Unicode"
        .Open
        .WriteText content
        .SaveToFile filePath, 2 'Save to disk
        .Close
    End With
End Sub