| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UNABLE TO IMPORT A TEXT FILE HAVING 30 COLUMNS. LOADS ONLY TILL 26column.
Hi,
I'm using below macro to load a text file in excel. The text file has 30 columns where as after running the macro, only 26 columns are loaded. ------------------------------------------------------------------- Private Sub CommandButton1_Click() On Error Resume Next Dim sFile Application.ScreenUpdating = False Application.DisplayAlerts = False sFile = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _ Title:="Import File") If sFile < False Then Workbooks.OpenText Filename:=sFile, Origin:=437, _ StartRow:=9, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ ), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _ Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array(28, 1), Array(29, 2), Array(30, 1)), _ TrailingMinusNumbers:=True ActiveSheet.UsedRange.Select Selection.Copy ActiveWorkbook.Close Worksheets("User").Activate Range("A1:Z65536").Select ActiveSheet.Paste Range("A1").Select ActiveWindow.Zoom = 85 Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub ------------------------------------------------------------- Any idea?? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UNABLE TO IMPORT A TEXT FILE HAVING 30 COLUMNS. LOADS ONLY TILL 26column.
If you stop your macro right after the data is imported, do you see 30 columns
used in that worksheet? Sinner wrote: Hi, I'm using below macro to load a text file in excel. The text file has 30 columns where as after running the macro, only 26 columns are loaded. ------------------------------------------------------------------- Private Sub CommandButton1_Click() On Error Resume Next Dim sFile Application.ScreenUpdating = False Application.DisplayAlerts = False sFile = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _ Title:="Import File") If sFile < False Then Workbooks.OpenText Filename:=sFile, Origin:=437, _ StartRow:=9, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ ), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _ Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array(28, 1), Array(29, 2), Array(30, 1)), _ TrailingMinusNumbers:=True ActiveSheet.UsedRange.Select Selection.Copy ActiveWorkbook.Close Worksheets("User").Activate Range("A1:Z65536").Select ActiveSheet.Paste Range("A1").Select ActiveWindow.Zoom = 85 Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub ------------------------------------------------------------- Any idea?? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UNABLE TO IMPORT A TEXT FILE HAVING 30 COLUMNS. LOADS ONLY TILL26 column.
On Sep 24, 12:55*am, Dave Peterson wrote:
If you stop your macro right after the data is imported, do you see 30 columns used in that worksheet? Sinner wrote: Hi, I'm using below macro to load a text file in excel. The text file has 30 columns where as after running the macro, only 26 columns are loaded. ------------------------------------------------------------------- Private Sub CommandButton1_Click() On Error Resume Next Dim sFile * * Application.ScreenUpdating = False * * Application.DisplayAlerts = False sFile = Application.GetOpenFilename( _ * * * * FileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _ * * * * Title:="Import File") * * If sFile < False Then * * * * Workbooks.OpenText Filename:=sFile, Origin:=437, _ * * * * StartRow:=9, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ * * * * ConsecutiveDelimiter:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ * * * * ), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ * * * * Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _ * * * * Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ * * * * Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array(28, 1), Array(29, 2), Array(30, 1)), _ * * * * TrailingMinusNumbers:=True * * * * ActiveSheet.UsedRange.Select * * * * Selection.Copy * * * * ActiveWorkbook.Close * * * * Worksheets("User").Activate * * * * Range("A1:Z65536").Select * * * * ActiveSheet.Paste * * Range("A1").Select * * ActiveWindow.Zoom = 85 * * Cells.EntireColumn.AutoFit * * Cells.EntireRow.AutoFit * * End If * * Application.ScreenUpdating = True * * Application.DisplayAlerts = True End Sub ------------------------------------------------------------- Any idea?? -- Dave Peterson- Hide quoted text - - Show quoted text - No Only data comes till columnZ But text file have 4 more columns data of those should come in column AA, AB, AC & AD but after columnZ there is nothing in sheet. : ( |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
UNABLE TO IMPORT A TEXT FILE HAVING 30 COLUMNS. LOADS ONLY TIL
Change
Range("A1:Z65536").Select to Range("A1:AD65536").Select Hope this helps, Hutch "Sinner" wrote: On Sep 24, 12:55 am, Dave Peterson wrote: If you stop your macro right after the data is imported, do you see 30 columns used in that worksheet? Sinner wrote: Hi, I'm using below macro to load a text file in excel. The text file has 30 columns where as after running the macro, only 26 columns are loaded. ------------------------------------------------------------------- Private Sub CommandButton1_Click() On Error Resume Next Dim sFile Application.ScreenUpdating = False Application.DisplayAlerts = False sFile = Application.GetOpenFilename( _ FileFilter:="Text Files (*.txt), *.txt", FilterIndex:=1, _ Title:="Import File") If sFile < False Then Workbooks.OpenText Filename:=sFile, Origin:=437, _ StartRow:=9, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 2 _ ), Array(2, 1), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ Array(9, 1), Array(10, 1), Array(11, 2), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _ Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), _ Array(23, 1), Array(24, 2), Array(25, 1), Array(26, 2), Array(27, 2), Array(28, 1), Array(29, 2), Array(30, 1)), _ TrailingMinusNumbers:=True ActiveSheet.UsedRange.Select Selection.Copy ActiveWorkbook.Close Worksheets("User").Activate Range("A1:Z65536").Select ActiveSheet.Paste Range("A1").Select ActiveWindow.Zoom = 85 Cells.EntireColumn.AutoFit Cells.EntireRow.AutoFit End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub ------------------------------------------------------------- Any idea?? -- Dave Peterson- Hide quoted text - - Show quoted text - No Only data comes till columnZ But text file have 4 more columns data of those should come in column AA, AB, AC & AD but after columnZ there is nothing in sheet. : ( |
| 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) | |||
| text box positioning, printing and between loads | Charts and Charting in Excel | |||
| When Excel loads it autoloads a file | Excel Discussion (Misc queries) | |||
| How do I import text file, analyze data, export results, open next file | Excel Programming | |||
| Import Text file into multiple columns | Excel Programming | |||
Hybrid Mode
