Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file via makro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file via makro
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import txt file via makro
Thanks Dave,
that helped me! Regards, Markus "Dave Peterson" schrieb im Newsbeitrag ... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I import text file of cash flow to excel file then use formula | Excel Discussion (Misc queries) | |||
open *.csv file as *.xls makro | Excel Programming | |||
How do I import text file, analyze data, export results, open next file | Excel Programming | |||
Import Makro | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |