Import Text file into an existing Worksheet
My code opens a new workbook. Instead I need the data to be imported into an
existing worksheet name GLFBCALO. Is there a way I can do this? Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x Sheets("GLFBCALO").Select Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200609/1 |
Import Text file into an existing Worksheet
Public Sub ImportOKdata()
Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x, sh as worksheet Sheets("GLFBCALO").Select set sh = Activesheet Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With Activesheet.usedRange.Copy Destination:=sh.Range("A1") ActiveWorkbook.close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "Fredriksson via OfficeKB.com" wrote: My code opens a new workbook. Instead I need the data to be imported into an existing worksheet name GLFBCALO. Is there a way I can do this? Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x Sheets("GLFBCALO").Select Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200609/1 |
Import Text file into an existing Worksheet
Thank you very much. I was trying to used a previous example that you replied.
I now know what I did wrong. I forgot to declare sh as a worksheet. Thanks for your help. I appreciate it Tom Ogilvy wrote: Public Sub ImportOKdata() Dim MyFile As String Dim ColumnsDesired Dim DataTypeArray Dim ColumnArray(0 To 11, 1 To 2) Dim x, sh as worksheet Sheets("GLFBCALO").Select set sh = Activesheet Cells.Select Selection.Clear 'fill the column and data type info 'Data Type 1 = general 2 = text, 9 skip ColumnsDesired = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) DataTypeArray = Array(1, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1) 'populate the array for fieldinfo For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x ' Get file With Application.FileDialog(msoFileDialogFilePicker) .AllowMultiSelect = False .Filters.Add "Text files", "*.txt" If .Show = True Then MyFile = .SelectedItems(1) 'Import data For x = LBound(ColumnsDesired) To UBound(ColumnsDesired) ColumnArray(x, 1) = ColumnsDesired(x) ColumnArray(x, 2) = DataTypeArray(x) Next x Workbooks.OpenText Filename:=MyFile, _ DataType:=xlDelimited, Tab:=True, FieldInfo:=ColumnArray End If End With Activesheet.usedRange.Copy Destination:=sh.Range("A1") ActiveWorkbook.close SaveChanges:=False End Sub My code opens a new workbook. Instead I need the data to be imported into an existing worksheet name GLFBCALO. Is there a way I can do this? [quoted text clipped - 35 lines] End With End Sub -- Message posted via http://www.officekb.com |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com