Results 1 to 4 of 4

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

  1. #1
    VBAX Regular
    Apr 2024

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

    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.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
                    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
                Exit Sub
            End If
        End With
     End Sub

  2. #2
    VBAX Sage
    Apr 2007
    United States
    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


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Oct 2005
    Surrey UK
    Let's have an example of such a problem text file!
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts