Import txt file via makro

[Markus Mannheim]

Hi,

the followin cde imports a txt file to excel:

Private Sub Workbook_Open()

Application.DisplayAlerts = False

'Meldung wird angezeigt

MsgBox "Bitte selektieren Sie die zu ladende Adressdaten-Datei!"
ChDir "\"
ChDrive "G:\"
ChDir "operating\handy\adressdaten"
ZuOeffnendeDatei = Application.GetOpenFilename("Textdateien
(*.txt),*.txt")
If ZuOeffnendeDatei = False Then
Exit Sub
End If
Application.ScreenUpdating = False


Workbooks.OpenText Filename:=ZuOeffnendeDatei, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, Semicolon:=True _
, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

'Herkunftsdatei wird Variable zugewiesen

ZuOeffnendeDatei = ActiveWorkbook.Name

Workbooks(ZuOeffnendeDatei).Activate
Selection.CurrentRegion.Select
Selection.Copy
Workbooks(ZuOeffnendeDatei).Close
Windows("export_outlook.xls").Activate
Sheets("Tabelle1").Select
Range("A1").Select
Selection.PasteSpecial
Selection.Columns.AutoFit
Range("A1").Select

Unfortunately the data in column 4 and 5 are telephone numbers, which will
be incorrectly imported:
a number = 005541995799999 will be shown as 5.542E+11
I tried to set format the columns with number format = "@" in advance, but
it didn't help.

For help thanks in advance,

Regards,
Markus

 

[Dave Peterson]

Record a macro when you open the file manually.

And when you're in the text to columns wizard, make sure you change the fields
that hold phone numbers to Text (don't leave it general).

Then take that recorded code and insert it into your code.

You'll be changing this section of code:

Workbooks.OpenText Filename:=ZuOeffnendeDatei, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, Semicolon:=True _
, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1))

 

[Markus Mannheim]

Thanks Dave,

that helped me!

Regards,
Markus

Record a macro when you open the file manually.

And when you're in the text to columns wizard, make sure you change the
fields
that hold phone numbers to Text (don't leave it general).

Then take that recorded code and insert it into your code.

You'll be changing this section of code: