Import Text file into an existing Worksheet

[Fredriksson via OfficeKB.com]

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

 

[Guest]

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

 

[Fredriksson via OfficeKB.com]

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

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