|
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?? |
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 |
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. : ( |
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. : ( |
| All times are GMT +1. The time now is 12:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com