vba - Character changes when importing to Access and exporting to Excel -

i'm working on access database in import csv files converted xls works, 1 file has fields characters change within field after being imported access

for example: dash changes û
beginning double quote changes ô
end double quote changes ö

from have read has 7 or 8 bit character codes.. not understand.

my questions are, there way prevent character change or there better i've tried already? or there potential problems haven't come across seems work in example below?

here's i've tried far seems work

from original excel file save unicode text file (something new me)

    activeworkbook.saveas filename:= _  "d:\newfiles\reportlist.txt", fileformat:=xlunicodetext _      , createbackup:=false  

then import database following code

    docmd.transfertext acimportdelim, "reportlist  import specification", "tbl_reportlist", "d:\newfiles\reportlist.txt", true  

this seems import text database correctly.

other people work data , export new report access excel. changes font ms sans serif , changes characters again not same changes when imported. after excel report exported, , change font arial characters correct again.... @ least far.

i haven't run character change in past , solution seems work, i'm not sure if there other potential problems or if there's missed. haven't found answer specific question yet.

thanks taking time this.

here method have used in past circumvent character encoding issues.

i suspect method should work between excel , access -- although access not familiar with.

this sub specifies file's full name & path, , destination new filename & path. these same if want overwrite existing.

note on few simple tests, can't read file saved "unicode" excel, works on files saved "tab delimited txt" files , csv/comma-separated files, too.

sub openandsavetxtutf8() dim txtfilename string dim newtxtfilename string  txtfilename = "d:\newfiles\reportlist.txt" newtxtfilename = "d:\newfiles\utf8_reportlist.txt"  writeutf8(readtextfile(txtfilename), newtxtfilename)  end sub 

this sub calls on 2 functions borrowed sources credited in code comments. writeutf8 creates proper utf8 file contents of readtextfile returns string of full file contents.

function readtextfile(sfilename string) string 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=699     dim ifile integer      on local error resume next      ' \\ use freefile supply file number not in use     ifile = freefile       ' \\ ' open file input.     open sfilename input #ifile       ' \\ return (read) whole content of file function     readtextfile = input$(lof(ifile), ifile)      close #ifile     on error goto 0 end function 

this function requires reference adodb library, or, can dim objstream object , code should still work you.

function writeutf8(textstring$, myfileout$) 'modified http://www.vbaexpress.com/forum/showthread.php?t=42375 'david zemens - february 12, 2013  'requires reference adodb?       ' utf8()  version 1.00      ' open "plain" text file , save again in utf-8 encoding      ' (overwriting existing file without asking confirmation).      '      ' based on sample script jtmar:      ' http://bytes.com/groups/asp/52959-save-file-utf-8-format-asp-vbscript      '      ' written rob van der woude      ' http://www.robvanderwoude.com      dim objstream adodb.stream       ' valid charset values adodb.stream     const cdobig5 = "big5"     const cdoeuc_jp = "euc-jp"     const cdoeuc_kr = "euc-kr"     const cdogb2312 = "gb2312"     const cdoiso_2022_jp = "iso-2022-jp"     const cdoiso_2022_kr = "iso-2022-kr"     const cdoiso_8859_1 = "iso-8859-1"     const cdoiso_8859_2 = "iso-8859-2"     const cdoiso_8859_3 = "iso-8859-3"     const cdoiso_8859_4 = "iso-8859-4"     const cdoiso_8859_5 = "iso-8859-5"     const cdoiso_8859_6 = "iso-8859-6"     const cdoiso_8859_7 = "iso-8859-7"     const cdoiso_8859_8 = "iso-8859-8"     const cdoiso_8859_9 = "iso-8859-9"     const cdokoi8_r = "koi8-r"     const cdoshift_jis = "shift-jis"     const cdous_ascii = "us-ascii"     const cdoutf_7 = "utf-7"     const cdoutf_8 = "utf-8"       ' adodb.stream file i/o constants     const adtypebinary = 1     const adtypetext = 2     const adsavecreatenotexist = 1     const adsavecreateoverwrite = 2      on error resume next      set objstream = createobject("adodb.stream")     objstream.open     objstream.type = adtypetext     objstream.position = 0     objstream.charset = cdoutf_8   'we passing string write file, omit following line '    objstream.loadfromfile myfilein  'and instead of using loadfromfile writing directly copied ' text unsaved/temp instance of notepad.exe objstream.writetext textstring, 1      objstream.savetofile myfileout, adsavecreateoverwrite     objstream.close     set objstream = nothing      if err         writeutf8 = false     else         writeutf8 = true     end if      on error goto 0 end function