Fix for BOM issue in first cell of imported txt file in Excel 2019?

[Event2020]

Excel 2019



Hello everyone.


I am having an issue where I am importing a text file to a specific worksheet and when the vba writes the files contents to the worksheet it is inserting the extra characters "" infront of the written text.


From my online research I understand that this is known as a BOM issue.


One web post I came across indicated that is Byte Order Mark ( or EF BB BF in hex) when trying to read UTF-8 encoded CSV.
My files are text files which also appear to have UTF-8 so maybe I may have to convert them in some way which is just adding another step I would rather avoid but if needs must?


My research suggested to change "Dim fd As fileDialog" to "FileDialog" as a possible fix but that yealded no result.


This is well outside of my knowledge and perhaps someone could throw some light on this please?

Sub Rev13_ImportFiles()
    Dim fd As fileDialog ' Changed from 'fileDialog' to 'FileDialog' as suggested as a fix
    Dim selectedFile As Variant ' Change from String to Variant as a fix
    Dim textLine As String
    Dim counter As Long
    Dim arr() As String
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCell As Range
    
    ' Set the worksheet to "Data Import" in the current workbook
    Set ws = ThisWorkbook.Sheets("Data Import")
    
    ' Create a FileDialog object as a File Picker with a filter that only allows text files
    Set fd = Application.fileDialog(msoFileDialogFilePicker) ' Corrected 'fileDialog' to 'FileDialog'
    With fd
        .Title = "Select a Text File"
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt"
        .AllowMultiSelect = True ' Allow multiple file selection
        
        ' Check If user selects a SINGLE file to import
        If .Show = -1 Then
            For Each selectedFile In .SelectedItems
                ' Write the file name in row 1
                ws.Cells(1, 1).Value = selectedFile
                
                ' Import the file into the worksheet starting on Row 2
                Open selectedFile For Input As #1
                counter = 2 ' Initialize the counter for row 2
                Do Until EOF(1)
                    Line Input #1, textLine
                    arr = Split(textLine, ",")
                    For i = LBound(arr) To UBound(arr)
                        ws.Cells(counter, i + 1).Value = arr(i)
                    Next i
                    counter = counter + 1
                Loop
                Close #1
                
                ' Find the last cell with the value "MD5 of the unencoded content" in Column A
                Set lastCell = ws.Cells(ws.Rows.Count, "A").End(xlUp)
                
                ' Check the NEXT 5 cells down in column A for values
                If Application.WorksheetFunction.CountA(lastCell.Offset(1, 0).Resize(5, 1)) = 0 Then
                    ' Move down 2 ROWS from the lastCell and select that row
                    Set lastCell = lastCell.Offset(2, 0)
                    ' Shade the cells from Columns A to C yellow
                    ws.Range("A" & lastCell.Row & ":C" & lastCell.Row).Interior.Color = RGB(255, 255, 0)
                    
                    ' Popup message showing the name of the Imported file
                    MsgBox "The file '" & selectedFile & "' has been imported."
                End If
            Next selectedFile
        Else
            Exit Sub
        End If
    End With
 End Sub

[Paul_Hossler]

This is just a guess since there is no sample file to look at but try

Dim arr as Variant

instead of

Dim arr() as String

[p45cal]

Let's have an example of such a problem text file!

[snb]

Sub M_snb()
   With Application.FileDialog(msoFileDialogFilePicker)
      If .Show Then Workbooks.OpenText .SelectedItems(1), 65001
    End With
End Sub