zoom38
02-19-2007, 05:31 PM
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.
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
Thank you
Gary
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
Thank you
Gary