-
Solved: Import Text File
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
-
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
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
-
Thanks for the solution Gary.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules