How to maintain character set while exporting Excel table to ...

csv with UTF8 encoding (no BOM). I found code which almost works for me, see below. My problem is that the table contains swedish characters ( ... Home Public Questions Tags Users Companies Collectives ExploreCollectives Teams StackOverflowforTeams –Startcollaboratingandsharingorganizationalknowledge. CreateafreeTeam WhyTeams? Teams CreatefreeTeam Collectives™onStackOverflow Findcentralized,trustedcontentandcollaboratearoundthetechnologiesyouusemost. LearnmoreaboutCollectives Teams Q&Aforwork Connectandshareknowledgewithinasinglelocationthatisstructuredandeasytosearch. LearnmoreaboutTeams HowtomaintaincharactersetwhileexportingExceltableto.csvUTF8withoutBOMusingVBA? AskQuestion Asked 2years,2monthsago Modified 2years,2monthsago Viewed 317times 1 Ihavereadseveralotheranswersregardinghowtoexportatableto.csvwithUTF8encoding(noBOM).Ifoundcodewhichalmostworksforme,seebelow. Myproblemisthatthetablecontainsswedishcharacters(ÅÄÖ),andwhenthe.csv-fileisopenedthesearelosttowhatlookslikeanincorrectcharset.Ifoundaworkaroundwhichistoopenthe.csv-fileinNotepad,save,andthenopenitinExcel.TheworkaroundmakesExceldisplaythelettersproperly,butIwouldprefernottohavetheextrastep.Canthecodebelowbemodifiedsothatthecharsetisnotlost? OptionExplicit SubCSVFileAsUTF8WithoutBOM() DimSrcRangeAsRange DimCurrRowAsRange DimCurrCellAsRange DimCurrTextStrAsString DimListSepAsString DimFNameAsVariant DimUTFStreamAsObject DimBinaryStreamAsObject 'ADOConstants ConstadTypeBinary=1'Thestreamcontainsbinarydata ConstadTypeText=2'Thestreamcontainstextdata(default) ConstadWriteLine=1'writetextstringandalineseparator(asdefinedbytheLineSeparatorproperty)tothestream. ConstadModeReadWrite=3'Read/write ConstadLF=10'Linefeedonly-defaultiscarriagereturnlinefeed(adCRLF) ConstadSaveCreateOverWrite=2'OverwritesthefilewiththedatafromthecurrentlyopenStreamobject,ifthefilealreadyexists 'Openthisworkbooklocation ChDriveLeft(ThisWorkbook.Path,1) ChDirThisWorkbook.Path 'askforfilenameandpath FName=Application.GetSaveAsFilename("","CSVFile(*.csv),*.csv") 'prepareUTF-8stream SetUTFStream=CreateObject("adodb.stream") UTFStream.Type=adTypeText UTFStream.Mode=adModeReadWrite UTFStream.Charset="UTF-8" UTFStream.LineSeparator=adLF UTFStream.Open 'setfieldseparator ListSep=";" 'setsourcerangewithdataforcsvfile IfSelection.Cells.Count>1Then SetSrcRange=Selection Else SetSrcRange=ActiveSheet.UsedRange EndIf ForEachCurrRowInSrcRange.Rows CurrTextStr="" ForEachCurrCellInCurrRow.Cells CurrTextStr=CurrTextStr&Replace(CurrCell.Value,"""","""""")&ListSep Next 'removeListSepafterthelastvalueinline WhileRight(CurrTextStr,1)=ListSep CurrTextStr=Left(CurrTextStr,Len(CurrTextStr)-1) Wend 'addlinetoUTFStream UTFStream.WriteTextCurrTextStr,adWriteLine'WritescharacterdatatoatextStreamobject Next 'skipBOM UTFStream.Position=3'setsorreturnsalongvaluethatindicatesthecurrentposition(inbytes)fromthebeginningofaStreamobject 'copyUTFStreamtoBinaryStream SetBinaryStream=CreateObject("adodb.stream") BinaryStream.Type=adTypeBinary BinaryStream.Mode=adModeReadWrite BinaryStream.Open'OpensaStreamobject 'StripsBOM(first3bytes) UTFStream.CopyToBinaryStream'Copiesaspecifiednumberofcharacters/bytesfromoneStreamobjectintoanotherStreamobject UTFStream.Flush'SendsthecontentsoftheStreambuffertotheassociatedunderlyingobject UTFStream.Close'ClosesaStreamobject 'savetofile BinaryStream.SaveToFileFName,adSaveCreateOverWrite BinaryStream.Flush'SendsthecontentsoftheStreambuffertotheassociatedunderlyingobject BinaryStream.Close'ClosesaStreamobject EndSub excelvbacsvcharacter-encoding Share Improvethisquestion Follow askedAug3,2020at15:51 DavidHasselbergDavidHasselberg 17111goldbadge22silverbadges1313bronzebadges 3 DidyoumeanNotepad++?BecausesavingthefilewithNotepaddidnothingforme. – DecimalTurn Aug3,2020at18:16 No,onthecontraryopeninginNotepad++doesnothingformewhiledoingitinWindowsNotepadfixesthefilesomehow. – DavidHasselberg Aug3,2020at18:31 Basedonyourresponsetomyanswer,IfeellikeknowingthenameoftheothersoftwareyouaretryingtousetheCSVwithwouldbeanimportantinformationtoaddtoyourquestion. – DecimalTurn Aug3,2020at18:55 Addacomment  |  1Answer 1 Sortedby: Resettodefault Highestscore(default) Trending(recentvotescountmore) Datemodified(newestfirst) Datecreated(oldestfirst) 1 EDIT: Basedonyourcomment,IrealizethatwhatyouinitiallywantedwastokeeptheinformationaboutthecharacterencodinginsidethefilewithouthavingaBOM. Theproblemwiththisquestion(asyourealizedit)isthattheBOMisactuallywhatnormallycontainstheinformationaboutthecharacterencodingandputtingthisinformationanywhereelseinthefiledoesn'treallymakesense. So,yourcodeisactuallyperfectforthetaskathand.WhatneedstobechangedishowtheCSVfileisimported/openedbythesoftwareyouwanttouse. WhenthefilehasnoBOM,asoftwarereadingthefilehastoguessthe characterencoding. Ingeneral,ifthesoftwareyouusedoesn'tsupportBOManddoesn'tguesscorrectly,thereshouldatleastbeawaytocustomizethebehavioroftheimport/opencommandsothatyoucanspecifythecharacterencoding(seemslikeyouactuallyfoundit). Originalanswer: Forsomereason,ExcelhasahardtimetoguessthecharacterencodingwhenopeningaUTF-8encodedCSVfilewhenyoujustdouble-clickingthefile.Youhavetohelpitalittle... Insteadofopeningitdirectly,youcouldloadtheCSVcontenttoanewworkbookbyusingthe(legacy)TextImportWizardandselectingtheUTF-8characterset(65001)duringimportifExcelisnotabletofigureitoutbyitself. Ifyouweretorecordamacrowhiledoingitandmakeitintoasubprocedure,youcouldhavesomethinglikethis: SubOpenCSV(FullFileNameAsString) DimwbAsWorkbook Setwb=Workbooks.Add DimwsAsWorksheet Setws=wb.Sheets(1) Withws.QueryTables.Add(Connection:=_ "TEXT;"&FullFileName,Destination:=Range(_ "$A$1")) .Name="CSV_Open" .FieldNames=True .RowNumbers=False .FillAdjacentFormulas=False .PreserveFormatting=True .RefreshOnFileOpen=False .RefreshStyle=xlInsertDeleteCells .SavePassword=False .SaveData=True .AdjustColumnWidth=True .RefreshPeriod=0 .TextFilePromptOnRefresh=False .TextFilePlatform=65001 .TextFileStartRow=1 .TextFileParseType=xlDelimited .TextFileTextQualifier=xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter=False .TextFileTabDelimiter=True .TextFileSemicolonDelimiter=False .TextFileCommaDelimiter=False .TextFileSpaceDelimiter=False .TextFileColumnDataTypes=Array(1) .TextFileTrailingMinusNumbers=True .RefreshBackgroundQuery:=False EndWith EndSub Othersuggestion Ifyoureallywanttobeabletodouble-clickthefileinsteadofusingtheTextImportWizardorrunningamacro,youcouldalwayscreateaVBAeventprocedureinanadd-inorPERSONAL.XSLBrunningeverytimeaworkbookisopened. IfitdetectsthatthefilethatwasjustopenedisaCSVfile,itcouldcloseitand"reopen"itusingthecodeabove. Extra: Ofinterest:thereisaquestionhereabouthowtochangethedefaultcharacterencodingthatExceluses. Share Improvethisanswer Follow editedAug3,2020at20:37 answeredAug3,2020at18:23 DecimalTurnDecimalTurn 2,60622goldbadges1414silverbadges2929bronzebadges 6 Thefileistobeusedbyothersoftware,andthatsoftwarehasthesameproblemasExcelwithÅÄÖ.Unfortunatelythissolutionstillrequiresamanualextrastep,justlikesavingthe.csvfromNotepad.Woulditbepossibletomergeyourscriptwithmine,sothatthe.csvisinstantlyopened,receivesthecorrectcharset,andthensavedagain? – DavidHasselberg Aug3,2020at18:36 @DavidHasselbergAndthatothersoftwaredoesnotsupportBOM? – DecimalTurn Aug3,2020at18:38 Correct,itdoesnot. – DavidHasselberg Aug3,2020at18:47 @DavidHasselberg,thenthat'syourrealproblem.TheBOMiswhatcontainsthecharactersetinformation.Ifthebehaviorofthatsoftwarecan'tbecustomizedtoreadandusetheBOM,Idon'tseeanotherwaytoencodethefilethatwouldsolveyourproblem. – DecimalTurn Aug3,2020at18:53 Isee,thatexplainswhythisissuchaproblem.YourexplanationmademethinkitwouldbeunreasonablefortheothersoftwaretobothbeunabletoreadhandleBOMandtonotcarrysupportforutf-8encoding.Hence,Itriedtoaddalineofcodeintotheimportscriptofthesoftwarethatsettheencodingtoutf-8,andmiraculouslyitworkedstraightaway.Thanksforhelpingalongtheway! – DavidHasselberg Aug3,2020at20:03  |  Show1morecomment YourAnswer ThanksforcontributingananswertoStackOverflow!Pleasebesuretoanswerthequestion.Providedetailsandshareyourresearch!Butavoid…Askingforhelp,clarification,orrespondingtootheranswers.Makingstatementsbasedonopinion;backthemupwithreferencesorpersonalexperience.Tolearnmore,seeourtipsonwritinggreatanswers. Draftsaved Draftdiscarded Signuporlogin SignupusingGoogle SignupusingFacebook SignupusingEmailandPassword Submit Postasaguest Name Email Required,butnevershown PostYourAnswer Discard Byclicking“PostYourAnswer”,youagreetoourtermsofservice,privacypolicyandcookiepolicy Nottheansweryou'relookingfor?Browseotherquestionstaggedexcelvbacsvcharacter-encodingoraskyourownquestion. TheOverflowBlog HowtoearnamillionreputationonStackOverflow:beofservicetoothers Therightwaytojobhop(Ep.495) FeaturedonMeta BookmarkshaveevolvedintoSaves Inboximprovements:markingnotificationsasread/unread,andafiltered... Revieweroverboard!Orarequesttoimprovetheonboardingguidancefornew... CollectivesUpdate:RecognizedMembers,Articles,andGitLab Shouldweburninatethe[script]tag? Related 761 HowdoIseewhatcharactersetaMySQLdatabase/table/columnis? 618 HowtoavoidusingSelectinExcelVBA 40 sep=";"statementbreaksutf8BOMinCSVfilewhichisgeneratedbyXSL 223 TypeError:abytes-likeobjectisrequired,not'str'inpythonandCSV 1 SpecialcharacterissueinExcelforCSVfile,SheetjsprependBOMforutf8CSVfiles 0 HowcanIreturnafileusingspringwithoutchangingtheencoding,orjustsetupUTF-8-BOMencoding HotNetworkQuestions MakeaCourtTranscriber My(large)employerhasn'tregisteredanobviousmisspellingoftheirprimarydomainURL Levinson'salgorithmandQRdecompositionforcomplexleast-squaresFIRdesign WillIgetdeniedentryafterIremovedavisasticker?Ismypassportdamaged? Could"nocloning"beusedasadefenceforquantumencryption? HowtogetridofUbuntuProadvertisementwhenupdatingapt? WhatdothecolorsindicateonthisKC135tankerboom? Changelinkcolorbasedinbackgroundcolor? Theunusualphrasing"verb+the+comparativeadjective"intheLordoftheRingsnovels Whydostringhashcodeschangeforeachexecutionin.NET? WhatisthedifferencebetweenGlidepathversusGlideslope? StandardCoverflow-safearithmeticfunctions Sciencefictionbook/novelaboutaliensinhumans'bodies Whatprotocolisthiswaveform? Howdoyoucalculatethetimeuntilthesteady-stateofadrug? Flatkeyboardwithoutanyphysicalkeys Doyoupayforthebreakfastinadvance? WhytheneedforaScienceOfficeronacargovessel? Superimposedglyphsandsuper/subscripts ConvertanintegertoIEEE754float Sortbycolumngroupandignoreothercolumnsfailingforthisexample,why? Whyistherealotofcurrentvariationattheoutputofabuckwhenabatteryisconnectedattheoutput? Writingthenumber'80668227'asasumsof4&5cubes Whydoesthesameelectrontransitionreleasephotonsofdifferentfrequenciesforsomeelements? morehotquestions Questionfeed SubscribetoRSS Questionfeed TosubscribetothisRSSfeed,copyandpastethisURLintoyourRSSreader. lang-vb Yourprivacy Byclicking“Acceptallcookies”,youagreeStackExchangecanstorecookiesonyourdeviceanddiscloseinformationinaccordancewithourCookiePolicy. Acceptallcookies Customizesettings