Import Text file into an existing Worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fredriksson - 26 Sep 2006 17:14 GMT

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

Reply to this Message

Tom Ogilvy - 26 Sep 2006 17:46 GMT

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

Signature

Regards,
Tom Ogilvy

> 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

Reply to this Message

Fredriksson - 26 Sep 2006 18:22 GMT

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
[quoted text clipped - 41 lines]
>> End With
>> End Sub

Reply to this Message