tsv file open in turkish font and it should be hebrew why ?

[Shally]

when i export text file from my erp that contain hebrew fonts the file is
saved as *.tsv file.
when i open it in the excel it open every time with gibrish.
if i open it manually and using the text import wizard i can see that the
file origin font is set to Turkish(Windows), if i change it to
Hebrew(Windows) it fix the font and the file open good.
i want to skip that part and make it open the file directly with
Hebrew(Windows), how to do that ?

 

[Joel]

I believe that the .tsv is equivalent to the english file CSV (comma
seperated values). If you remane the file as TXT and open the file a pop up
window will appear that has the option to start at row 2 instead of row 1.
This may solve your problem.

 

[Shally]

it is not CSV file it's a TSV (Tab-Separated Values) file.
I'm looking for solution that will not need any manually work, since the TSV
file is beeing produce by the ERP system (Oracle Applications) in my company
and the users want it to be open with out any changes needed to be done by
them manually.

 

[Joel]

You can write your own macro to read the TSV file. The code below will work
if you change the delimite from "," to chr(9) which si the tab character.
You probably want to ingnore the first line of data.

Sub GetCSVData()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Const Delimiter = ","
Set fsread = CreateObject("Scripting.FileSystemObject")

'default folder
Folder = "C:\temp\test"
ChDir (Folder)

FName = Application.GetOpenFilename("CSV (*.csv),*.csv")


RowCount = LastRow + 1
If FName <> "" Then
'open files
Set fread = fsread.GetFile(FName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

'extract comma seperated data
ColumnCount = 1
Do While InputLine <> ""
DelimiterPosition = InStr(InputLine, Delimiter)
If DelimiterPosition > 0 Then
Data = Trim(Left(InputLine, DelimiterPosition - 1))
InputLine = Mid(InputLine, DelimiterPosition + 1)
Else
Data = Trim(InputLine)
InputLine = ""
End If

Cells(RowCount, ColumnCount) = Data
ColumnCount = ColumnCount + 1
Loop
RowCount = RowCount + 1
Loop

tsread.Close
End If
End Sub

 

[Shally]

it didn't work
i did some checking what i need is a macro that will run at start up and
will make the Origin text 1255.
i record a macro that is doing it to specific file
Sub Macro2()
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\shallyz\Desktop\fnd_gfm.tsv",
Origin:=1255, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2,
1)), _
TrailingMinusNumbers:=True
End Sub

how to make it that everytime excel is open it will check if the Origin=1255
and if not it will change to Origin:=1255 ?

 

[Joel]

You need to create a personal.xls file in the XLSTART folder under Microsoft
Office. On my PC it is in the following location

C:\Program Files\Microsoft Office\OFFICE11\XLSTART

The location of XLSTART varies on different version of Office so do a search
for XLSTART on your c: drive. Put the code in a workbook Open Event in the
personal.xls file.

 

[Shally]

simple solution:
i go to the regedit under
HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
i add a key DefaultCPG with value 1255
and that's it, it's working.

this solution is good for anyone that open excel file and it is not in the
right language just to give him the default language (in my case 1255 =
hebrew).

Thanks for your help !