Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: Retrieving some columns from closed workbook to Active

    Hi i'm new to ecxel,
    and want to create a button that once pressed will extract data from columns a,c,d,f from a worksheet which is closed and populate the active worksheet. If anyone know how to do this in VB i'd most grateful if you can share the code,
    with thanks

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Attached are two workbooks and a button to run code designed to copy from workbook #1 columns A,C,D & F into workbook #2. The code may be helpful to you in creating your own solution. The solution works if you have no blank cells in your source data, within the data rows in column A. If there are blanks, then the code would need to be revised to get the last data row.
    Copy both workbooks to the same folder and open BookDest.xls and run the macro.

    Code:
    Sub Macro1()
    'Macro will open closed workbook,
    'copy data from cols A, C, D & F to destination worksheet cols A-D,
    'close source file.
    Dim ThatWkBkPath As String, ThatWkBkName As String
    Dim ThisWkBkName As String
    Dim numTrans As Long, i As Integer, intCol As Integer
    Dim aryCols() As String
    
        aryCols = Split("1|3|4|6", "|")   'array represents columns A,C,D,F
        
        'identify the name and path of the source data
        ThatWkBkName = "BookSrce.xls"
        ThatWkBkPath = ActiveWorkbook.Path & "\"
        
        ThisWkBkName = ActiveWorkbook.Name
        
        'hide screen activity
        Application.ScreenUpdating = False
        
        'clear out the cells before copying new data in
        Workbooks(ThisWkBkName).Sheets("Sheet1").Columns("A:D").ClearContents
        
        'open the workbook with the source data
        Workbooks.Open (ThatWkBkPath & ThatWkBkName)
        
        'move to the source data worksheet
        'in the loop, select each column's data rows, then copy
        'and paste into destination worksheet
        Sheets("Sheet1").Select
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Select
        numTrans = Selection.Rows.Count
        
        For i = 0 To UBound(aryCols)
            intCol = CInt(aryCols(i))
            Workbooks(ThatWkBkName).Activate
            Range(Cells(1, intCol), Cells(numTrans, intCol)).Select
            Selection.Copy
        
            'move back to destination workbook and copy data starting in column A,
            'then columns B, C & D with each pass through the loop
            Workbooks(ThisWkBkName).Activate
            Cells(1, (i + 1)).Select
            ActiveSheet.Paste
        Next
        
        Range("A1").Select
        Application.CutCopyMode = False
        
        Workbooks(ThatWkBkName).Close SaveChanges:=False    'close source workbook
    
        Application.ScreenUpdating = True
        
        MsgBox "All done copying data from " & ThatWkBkName
        
    End Sub
    Attached Files Attached Files
    Last edited by JerryDal; 10-25-11 at 01:28. Reason: clarify comment in code

  3. #3
    Join Date
    Oct 2011
    Posts
    8

    Excel Macro to capture specific data from a txt file

    Hi,

    I need help. I have a txt file that I would like to open via a macro. Once open I need to search for data from one line and move to excel then find additional data on the next line (s) in the txt file and move to the same output row in excel.

    This is an EDI error file.

    I need to capture the data that has TRN*2*value~
    Just need to move the 10 first 10 bytes of the value to one cell then the next 9 bytes to the next cell in the same row.
    The I need to capture the STC segment from the next row in the txt file to the next columns of the same row.
    For example: Output should have:
    1111111111 I12345678 A2 20
    Actually the STC data has three parts usually before the *
    Even better I would like to just write out only the TRN numbers and STC data for just the STCs that have A3 or A7.



    See txt file:

    ISA*00* *00* *ZZ*EMEDNYBAT *ZZ*491 *111029*2040*^*00501*111117220*0*P*;~
    GS*HN*EMEDNYBAT*4XX*20111029*104022*15127220*X*005 010X214~
    ST*277*041027220*005010X214~
    BHT*0085*08*5057510*20111029*104022*TH~
    HL*1**20*1~
    NM1*PR*2*NYSDOH*****FI*111111111~
    TRN*1*100027220~
    DTP*050*D8*20111029~
    DTP*009*D8*20111029~
    HL*2*1*21*1~
    NM1*41*2*THIS HOSPITAL*****46*491~
    TRN*2*5057510~
    STC*A1;20*20111029*WQ*45479.05~
    QTY*90*2~
    AMT*YU*45479.05~
    HL*3*2*19*1~
    NM1*85*2*THIS HOSPITAL*****XX*1111111111~
    TRN*1*1130200000001293VF~
    HL*4*3*PT~
    NM1*QC*1*SMITH*JANE****MI*ZP75117M~
    TRN*2*1110029032I60238045~
    STC*A2;20*20111029*WQ*25693.4~
    REF*1K*1130200041027220~
    REF*BLT*111~
    DTP*472*RD8*20111014-20111019~
    HL*5*3*PT~
    NM1*QC*1*JOHNSON*JOE****MI*WB64271Z~
    TRN*2*1110029255I60238731~
    STC*A3;400*20111029*WQ*19785.65~
    REF*1K*1130200041027320~
    REF*BLT*111~
    DTP*472*RD8*20111016-20111019~
    SE*31*111117220~
    GE*10*11117220~
    IEA*1*111117220~

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I am responding to this post to encourage you to dig in and start coding these tasks yourself. After I have attempted to code a solution, then I use the forum when there seems to be a roadblock that I can't get past.

    There is always more than one solution, but my plan would be to read the text file (with code similar to the code below), and identify the line numbers that start with one of the targets STC*A3 or STC*A7, and the line number above each target found.

    Let's say you find either of these values on line #2 and line #37. This tells you that the information you are looking for is in lines #1 and #2, and lines #36 and #37. These line numbers can be stored in a "working" sheet and used in a second reading of the text file.
    During the second reading I would store just the target text lines in the worksheet. You use a counter in your loop, so that a function such as the one below named isTargetLine, used in conjunction with the results produced by the code at the bottom, will allow you to retrieve only those lines that you need.

    What you will need to do, and this will develop your VBA skills, is write another function to read through the text file a second time, pull out just the target lines, parse out the fields you want to keep, and place the results in a destination worksheet.

    In the code, I used Sheet2 to store line numbers in column A. I would then use column B to store each target line of text, and parse the 6 fields from 2 rows into columns C-H on just 1 row, so you might end up with something like this in columns C-H:
    TRN*2 STC*A7 1110029255 I60238731 A3 400
    then in code put all data in cells C-H, skipping over every other row which would not have data in columns C-H, into the destination worksheet.

    All this assumes that data in the text file is consistent and as clean as the sample in your post.

    Good luck in your coding.

    Code:
    Function isTargetLine(LineNo As Long) As Boolean
    Dim k As Long, tmpBool As Boolean
        
        k = 1
        Do While Sheets("Sheet2").Cells(k, 1).Value <> ""
            If Sheets("Sheet2").Cells(k, 1).Value = LineNo Then
                tmpBool = True
                Exit Do
            End If
            k = k + 1
        Loop
        
        isTargetLine = tmpBool
    
    End Functio
    Code:
    Function Identify_LineNumbers()
    'read text file and put target line numbers in Sheet2
    'for subsequent reading of the line above and the current line
    Dim textFile As String, targetText As String, lineText As String
    Dim fNum
    Dim LineNumber As Long, i As Long
    
        textFile = "C:\Temp\EDIError.txt"
        targetText = "STC*A3 STC*A7"
        
        Worksheets("Sheet2").Columns("A:H").ClearContents   'clear a place to work
        
        fNum = FreeFile     'fNum will be the next available number, usually 1
        
        Open textFile For Binary Access Read As #fNum     'open the file
        
        LineNumber = 1
        i = 1
        Do While Not EOF(fNum)                  'loop until end of file
            On Error GoTo errorHandler
            Line Input #fNum, lineText          'read data into variable
            If InStr(targetText, Mid(lineText, 1, 6)) > 0 Then
                'one of the 2 targets found; identify the line number above this line
                Sheets("Sheet2").Cells(i, 1).Value = (LineNumber - 1)
                i = i + 1
                Sheets("Sheet2").Cells(i, 1).Value = LineNumber
                i = i + 1
            End If
            LineNumber = LineNumber + 1
        Loop
    
    Exit_Identify_LineNumbers:
     
        Close #fNum
        Exit Function
        
    errorHandler:
    
        If Err = 62 Then
            'input past end of file--text file had no end-of-file marker
            'all done, go to the exit
        Else
            MsgBox "An unexpected error occurred reading the file" _
                & vbCrLf & textFile & vbCrLf & vbCrLf & _
                "Error #" & Err & vbCrLf & "Error description: " & Error
        End If
        
        Resume Exit_Identify_LineNumbers
        
    End Function
    Last edited by JerryDal; 10-31-11 at 13:30. Reason: remove redundant line of code in errorHandler

  5. #5
    Join Date
    Oct 2011
    Posts
    8

    Excel Macro to capture specific data from a txt file

    JerryDal,

    Thank you for your post. I am making attempts to run this. I am new to coding VB. Did you test this code? Just want to know if this will work as is. I need help setting this up. I inserted these as Module1 and Module2 within the VBAProject and changed the filename path. Is there anything else I need to do to get this working? then I can make modifications going forward.
    Step by step instructions would be great.


    Thank you Jerry as I really need help.

    Jeepman

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    I've given you the start of your coding project. The Identify_LineNumbers function is the first pass through the data to identify all the lines you want to retrieve. My "What you will need to do..." paragraph tells you what needs to be developed.

    The isTargetLine function will be used with the new function that you will write to extract all the text from every target line. Both functions have been tested and they work for me.

    Your step-by-step instructions are to (1) write a new function to read through the text file, and you can use the function I've given you as a model. In that function, you will use a counter to track the lines being read, and, with the 2nd function I've given you, set up a test to see if the line is a keeper, with code like this:
    Code:
    If isTargetLine Then
      <you add code here to assign the value in the 
        variable to a cell in Sheet2>
      <use a second counter here to track rows
        being populated in Sheet2>
    End If
    If the line is a keeper, you store the text in Sheet2.

    and step 2, write code to parse your data fields. You will write a function to examine the text in each two rows on Sheet2 and extract the 6 fields into 6 cells, putting the data on one row in Sheet2. A loop with a counter will be used to deal with each set of 2 rows in Sheet2.
    You will use string manipulation to get the fields. Your first 2 fields are easy: you know that on the first of your 2 rows you have "TRN*2", so you would use the MID() function to parse out 5 characters. And the next row starts with "STC", so you will also use MID() to parse out either "STC*A3" or "STC*A7", using MID(<text>, 1, 3) to get the "STC" and MID(<text>, 5, 2) to get the "A7" or "A3".
    Read up on string manipulation to learn how to pull out the other fields. The function INSTR() is one of the functions you'll use. You will be looking for patterns and field separators in your data to figure out the starting point and ending point of each field. If a field has a fixed length, it makes the process a little easier.

    I don't think you will be breaking any rules by putting these small functions all in one module.

    Jerry

Posting Permissions

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