Solved: Importing Non English Text

[Carl A]

I'm trying to import text files that contain english and german. I have set the regional settings to german and added german to the office language settings. I can switch to the german keyboard and type in german just fine in excel, However when I import the text file the import wizard does not preserve the language formatting. Question is can I import this text in excel using VBA and preserve the formatting or am I just missing something simple?:dunno

Note: text is delimited by Tab and semicolon.:help

[austenr]

So any typing you did in German is switched to english when you import the text file? Is that the situation?

[Carl A]

Yes the text doesn't retain the special characters Ex.

help Hilfe Mithilfe Beihilfe Unterst?tzung (becomes)
help Hilfe Mithilfe Beihilfe Unterstützung:dunno

This is the way the text appears in the import wizard.

[Wolfgang]

Good Morning Carl,

I noticed exactly the same behaviour on my US-notebook which runs on US-XP…It just won’t recognize any German related characters…

I was told that this has something to do with the initial keyboard driver setting made by XP…on Windows NT I could simply switch to any other language driver and it worked…

Oh well…

Have a nice day,
Wolfgang

[Carl A]

Well that isn't exactly the problem. If I set up my XP machines Regional and keyboard layouts to recognize German then I have no trouble typing the special characters in Excel.
What I have learned is that Excel (2000) will not import UTF-8 encoded text and as well Access also will not import UTF-8 encoded text.
One solution is to open the text document in Word then copy and use the paste special command in Excel. Then execute a Text to Column and behold I obtain the desired results. The other is I obtained a third party utility to covert the text file to Windows unicode and then all is well.
I was just wandering if anyone here could show me how to open the file in Word in UTF-8 encoded format then select the contents of the file, place in on the clipboard then paste special using UTF-8 format into excel all using VBA code. Me thinks one step is better then two.
:anyone:

:sad2: My skills in using VBA leave much to be desired.

[Carl A]

Well here is the only solution I can come up with. Its not purty.

Sub openUnicode()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdPath As String

'Start Word
Set wrdApp = CreateObject("Word.Application")

'Open the File
wrdPath = ThisWorkbook.Path & "\"
Set wrdDoc = wrdApp.Documents.Open(wrdPath & "German.txt")

wrdApp.Selection.WholeStory
wrdApp.Selection.Copy

wrdDoc.Close
Set wrdDoc = Nothing
wrdApp.Quit True
Set wrdApp = Nothing
Call PasteContents
End Sub

Sub PasteContents()
'Application Generated Macro
Worksheets("Sheet1").Activate
Range("A1").Select

ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
DisplayAsIcon:=False
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1))

Columns.AutoFit
End Sub

The Columns.Autofit makes the columns too wide, but maybe that has to do with approx 13,000 records being pasted. This works for me.:yes Newbie