VBA - Import data and encode as UTF8

[Makroz]

Hello Genie-i

Im looking for help with a macro that you can either select a file and import as 65001: Unicode UTF8 or convert a sheet dropped into a tab.

The issue i have is a source data export comes out with characters such as ‐ when they should not be

if i use the "from text/CSV" import function and select as 65001: Unicode UTF8 then it corrects all the special characters so i am essentially looking for something to replicate this so i can just press a button and the characters in the sheet should show as intended.

any help is appreciated

Thank you!

[CheeseSandwich]

Are you importing from a txt file or do the values already exist on the sheet?

If it is a txt/ csv file are you able to provide a sample file?

[Makroz]

Hey, thank you!

I have put together a sample so hopefully its attached ok. the source file is a CSV


Column D should read as below but its not specific to these 4 columns so im looking to correct either a full import or whatever is dropped into a worksheet. i can adjust any code ranges if required though

Lamborghini – Magnifique

Should be

Lamborghini � Magnifique

[CheeseSandwich]

Perhaps:

Sub test()
    Dim MyFile As String
    
    MyFile = "C:\Users\jbloggs\Desktop\test\test\Test.csv"
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & MyFile, Destination:=Range("A1"))
        .TextFilePlatform = 65001
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=True
    End With
End Sub

Edit: Just noticed your XL version... Let me know if it doen not work for you.

[Makroz]

Thank you my friend this is exactly what i was looking for!

don't worry i need to update my excel version i only just noticed

Have a great day!