string capture thru VBA

Npsays

Board Regular
Joined
Dec 17, 2004
Messages
70
Hi !

I need to capture some data in excel from a specific point in a .TXT file . IS there any command in VBA which can help me capture the same.? AS of now, it opens up the entrie file for me which exceeds 70000 records and excel would not load it completely. Please help.!

Thanks !
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
hi np.

i think this code (adopted from chip pearson's code at http://www.cpearson.com/excel/imptext.htm) should do the trick. i tested on a 65537 line file, and it imported properly. please note that if you do not need delimiters (such as "," or ";" to create multiple rows from a single line of text in your .txt file), just leave the box blank and select "OK". The code will assume that each row should be seperated with a carriage return (chr(13)).

cheers. ben.

Code:
Public Sub DoTheImport()

Dim FName As Variant
Dim Sep As String
Dim BeginLine As Long

FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
        "Import Text File")

If Sep = "" Then Sep = Chr(13)
        
BeginLine = Application.InputBox _
    ("Enter the line number at which to begin importing.", "Beginning Line Number", _
    Type:=2)

If Not IsNumeric(BeginLine) Then
    MsgBox "You must enter an numeric value."
    Exit Sub
End If

Call ImportTextFile(CStr(FName), Sep, BeginLine)

End Sub

Public Sub ImportTextFile(FName As String, Sep As String, BeginLine As Long)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim LineCount As Long
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        LineCount = LineCount + 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        If LineCount >= BeginLine Then Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    If LineCount >= BeginLine Then RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub
 

Npsays

Board Regular
Joined
Dec 17, 2004
Messages
70
Thanks for responding , i'll try this and would let you know if it worked ..i guess it should !

:biggrin:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,994
Messages
5,834,775
Members
430,321
Latest member
yemisimi11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top