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