Solved: Import Text File

[zoom38]

I'm trying to learn how to import a text file and the following is what I have. The problem with this is that the text file contains colons ( and is not recognizing it that is why I came up with the for/next loop below. The problem with this for/next loop is that it takes forever, over a minute. Can anyone advise why the code in the open text method does not recognize a colon. I used 58 in the ascII code set for a colon.

[vba]
Sub OpenFile()

Worksheets(1).EnableCalculation = False
Application.ScreenUpdating = False

Dim fname As String
Dim rwIndex As Long
Dim LastRow As Long


'fname = "c:\Documents and Settings\Gary\Desktop\Download_Numbers_Execute2.txt"
fname = "c:\Documents and Settings\Gary\Desktop\Download_Numbers_Execute.asp"

Workbooks.OpenText Filename:=fname, _
StartRow:=2, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
SemiColon:=True, _
Comma:=True, _
Space:=True, _
Other:=58

ActiveSheet.UsedRange.Select '\ select imported text in temporary workbook
Selection.Copy '\ copy to clipboard
ActiveWorkbook.Close '\ close temporary workbook

Worksheets("Sheet1").Activate
Range("A1") = "Open Text Method"
Range("A2").Select
ActiveSheet.Paste

'Find The Last Row On The Sheet1
LastRow = Cells(Rows.Count, 1).End(xlUp).Row

'For rwIndex = 2 To LastRow
' With Worksheets("Sheet1").Cells(rwIndex, 2)
' If .Value = ":" Then
' Cells(rwIndex, 2).Delete Shift:=xlShiftToLeft
' End If
' End With
'Next rwIndex

Rows(LastRow).EntireRow.Delete
Rows(LastRow - 1).EntireRow.Delete

'Move To Cell A1
Range("a1").Select

Application.ScreenUpdating = True
Worksheets(1).EnableCalculation = True

End Sub

[/vba] Thank you
Gary

[mdmackillop]

Hi Gary,
No time for a detailed look, but if you're having trouble, look at using the Split function to turn your text into an array.

[zoom38]

Md I will look into the split function thanks. I did however figure out my blunder. I changed
[vba]
Other:=58
[/vba]
To
[vba]
Other:=True, _
OtherChar:=Chr(58)
[/vba]
Which worked.
Thanks
Gary

[mdmackillop]

Thanks for the solution Gary.