+ Reply to Thread
Results 1 to 2 of 2

Importing specific data from a Text file

Hybrid View

  1. #1
    Registered User
    Join Date
    08-16-2006
    Posts
    8

    Importing specific data from a Text file

    Hello all

    I am looking to import specific data from a text file (only certain lines). I assume using VB code is the way to go, but am not sure what the code should look like (I have done limited VB programming).

    I am looking for code that will:
    1. When run, will ask me for the path (let me browse) to the file location (but I could hard code path).
    2. Will import (read) only certain defined information and append the data to the current data column. The file is like:

    Box1_a, 10
    Box1_b, 15
    Box1_c, 20
    Box2_a, 25
    Box2_b, 30
    Box2_c, 35
    Box3_a, 40
    Box3_b, 45
    Box3_c, 50

    And I just want to import the values of lines 1, 4, 7 (10,25,40). These values would be append to the data from the previous import.

    Any ideas

    Thanks

    Charlie

  2. #2
    Valued Forum Contributor
    Join Date
    04-11-2006
    Posts
    407
    You can import it into a new worksheet and then delete the new worksheet after you copy/paste the specified rows. Something like this:
    Sub Import_Rows()
    Dim myRows, myWksht As String
    myRows = "A1,A4,A7" 'Your rows to copy (1,4,7 shown here)
    myWksht = "Sheet1" 'Your worksheet to paste values into
    
    Application.ScreenUpdating = False
        iLastRow = ActiveWorkbook.Worksheets(myWksht).Range("A" & Rows.Count).End(xlUp).Row + 1
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\YourFileFolder\YourFileName.txt" _
            , Destination:=Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1251
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        ActiveSheet.Range(myRows).EntireRow.Copy
        ActiveWorkbook.Worksheets(myWksht).Range("A" & iLastRow).PasteSpecial Paste:=xlPasteValues
        Application.DisplayAlerts = False
        ActiveSheet.Delete
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub
    Hope that helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1