Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract cells from a workbook (excel)

    Hi All,

    I am new to this macro stuff and trying to understand how to develop a
    little Macro which will extract data for me.

    I have a folder with many text files, which I would open in excel, delimited.
    The data in these text files remains in a consistant position. I have a bout 100 files
    to work through and was wondering if its at all possible to automate my process.
    I would click the files and open them but as soon as I open the delimited txt file in
    excel I would used ctrl "e" to extract the data I need.

    My condition is this.

    OPen the text file,
    open in excel and delimit it.

    type ctrl "e" and the macro goes to work,

    copy and paste cells B4,B5, B13, B21

    then

    Goto cell CK, find the works "Flush Started"
    From this row move over to column AZ and move down till the value of CK=0.
    Now move over to Column AS of this row and copy the value and
    paste it in the working macro worksheet.

    close the text file

    and I will click the next one.

    Im not sure if this is possible to write in a short period of time, but though
    some experts might be able to help.

    Thank you for having a read.

    Keith

  2. #2
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    I have attached a worksheet, unfortunetly not all the data could be shown its too big
    but what is important is on.

    Keith

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    Welcome to Woody's Lounge!

    It is quite possible to write a macro that loops through all text files in a folder, opens each in turn and extract information from them. The basic structure is like this:

    Sub ProcessFiles()
    ' Folder to process - must include trailing backslash
    Const strFolder = "C:Test"
    Dim strFile As String
    Dim wbkCur As Workbook
    Dim wbkTxt As Workbook

    On Error GoTo ErrHandler

    Set wbkCur = ActiveWorkbook

    strFile = Dir(strFolder & "*.txt")
    Do While Not strFile = ""
    Set wbkTxt = Workbooks.OpenText Filename:=strFolder & stFile, DataType:=xlDelimited, Tab:=True
    ' Code to process file goes here
    ' ...
    wbkTxt.Close SaveChanges:=False
    strFile = Dir
    Loop

    ExitHandler:
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    The above assumes that the text files are tab-delimited, it's easily modified for other delimiters.

    It's not clear to me how you want to process the text files.
    - Where exactly should B4,B5, B13, B21 be pasted?
    - Where exactly should the cell found in column AS be pasted?

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    Thanks Hans, Nice to meet you!

    I would like to paste cells B4 in A1, B5 in B1, B13 in C1, B21 in D1 and AS in E1, skip a row to seperate and so on.

    Unfortuntly, the text files are not really text files. They are from a monitoring program with a variety of extensions ie. .001, .002, .005
    This is why I want to just click on the files I know are the ones with the data, and run a macro through them to quickly copy the cells needed and move on.

    Thanks for you reply.

  5. #5
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    For the data in cell A1 to E1, how do I give each column a title once. which is build into the macro?

    I guess the data would be pasted in columns A2:E2 instead if a Title was in place.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    What exactly do you mean by

    Goto cell CK, find the works "Flush Started"
    From this row move over to column AZ and move down till the value of CK=0.
    Now move over to Column AS of this row and copy the value and
    paste it in the working macro worksheet.

    I don't understand this description. What is the role of column AZ in this?

  7. #7
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    sorry it should be words in column AZ.


    Refer to my attached workbook;

    CK will have the words Flush Started in on of the cells (row 34 column CK) this is different for each workbook. This tells me when a certain operation is taking place. At this point I now know where The Flush operation is taking place so now move over on row 34 to the column AZ "fall out" which has values starting at row 34 column AZ. When the Value in AZ is 0 then I i want the value in column AS "BHT" on the same row. In this case is row 142 and the value there is 14.6. Copy this and paste it beside D2 in the macro work book.


    I hope this makes more sense.

    Thank You Hans

  8. #8
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    What is the role of AZ;

    The role of AZ is basically a point when the my Flush started operation has ended. At this point The data in AZ should now be 0. When my data is 0 in AZ there will be a certain Bottom Temperature which I want to copy asnd paste.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    The following macro should be stored in the workbook into which the cell values will be copied. When you run it, however, the text file you opened should be the active workbook.

    Sub CopyTest()
    Dim wbkCur As Workbook
    Dim wshCur As Worksheet
    Dim wbkTxt As Workbook
    Dim wshTxt As Worksheet
    Dim rng As Range
    Dim lngFoundRow As Long
    Dim lngRow As Long

    Set wbkCur = ThisWorkbook
    Set wshCur = wbkCur.Worksheets(1)
    Set wbkTxt = ActiveWorkbook
    Set wshTxt = wbkTxt.Worksheets(1)

    lngRow = wshCur.Range("A65536").End(xlUp).Row + 1
    wshTxt.Range("B4").Copy Destination:=wshCur.Range("A" & lngRow)
    wshTxt.Range("B5").Copy Destination:=wshCur.Range("B" & lngRow)
    wshTxt.Range("B13").Copy Destination:=wshCur.Range("C" & lngRow)
    wshTxt.Range("B21").Copy Destination:=wshCur.Range("D" & lngRow)

    Set rng = wshTxt.Range("CK:CK") _
    .Find(What:="Flush Started", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    lngFoundRow = rng.Row
    Set rng = wshTxt.Range("AZ" & lngFoundRow & ":AZ65536") _
    .Find(What:=0, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Offset(0, -7).Copy Destination:=wshCur.Range("E" & lngRow)
    End If
    End If

    Set rng = Nothing
    Set wshTxt = Nothing
    Set wbkTxt = Nothing
    Set wshCur = Nothing
    Set wbkCur = Nothing
    End Sub

  10. #10
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    Wow Im impressed you make it look like a peice of cake.
    Now I must learn what you did.



    Thanks Hans

  11. #11
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    Hello all and Hans,

    I have been using this macro you developed. But Now doing calculations and relize I left some data behind. You macro is working perfectly Hans.

    I am trying to add some more extractions;

    1.
    In this piece of code, how would I extract cells AV, BB from the same row the AS
    Value is being extracted from and paste them is columns F2, and G2?

    Set rng = wshTxt.Range("CK:CK") _
    .Find(What:="Flush Started", LookAt:=xlWhole)
    If Not rng Is Nothing Then
    lngFoundRow = rng.Row
    Set rng = wshTxt.Range("AZ" & lngFoundRow & ":AZ65536") _
    .Find(What:=0, LookAt:=xlWhole)
    If Not rng Is Nothing Then
    rng.Offset(0, -7).Copy Destination:=wshCur.Range("E" & lngRow)
    End If
    End If


    2.
    In column G, copy the max value and paste is in column H2

    3.
    I have tried to learn from your code and have build one which might be missing a few commands . Its similar to the find the value Flush Started in Ck > move to AZ and then AS procedure.

    The difference is columns.

    I am trying to extract the cells when the value in column G is not equal to 0.
    The cells needed to be extracted are cells E, F, AX, AD.
    The big challenge here is that each column has values that maybe inconsistent so maybe the average might be more of a consistent number.

    So once a value in column G is greater then 0, from that row move up 2 rows. Select 10 rows in E. From those 10 rows take the average of all 10 values. And paste the average in I2.

    This would be the done for columns F, AX, AD

    I am not sure where to even start in attempting this.

    Maybe if anyone with know how has time they could give some clues

    Thanks for reading

  12. #12
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract cells from a workbook (excel)

    Thanks Hans works good.

    I noticed your formula needs another bracket.

    thanks you again is the 3rd question possible?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    Edited by HansV to correct formula

    1. When

    rng.Offset(0, -7).Copy Destination:=wshCur.Range("E" & lngRow)

    is executed, rng is the cell in column AZ in which a 0 was found. rng.Offset(0, -7) is the cell 7 columns to the left, i.e. in column AS. AV is 4 columns to the left, and BB is 2 columns to the right of AZ, so you can use

    rng.Offset(0, -4).Copy Destination:=wshCur.Range("F" & lngRow)
    rng.Offset(0, 2).Copy Destination:=wshCur.Range("G" & lngRow)

    2. We can use the worksheet function Max to find the highest value in column G. We won't copy, just set the value:

    wshCur.Range("H" & lngRow) = Application.WorksheetFunction.Max(wshTxt.Range("G: G"))

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    Sorry, it's all air code, so a typo easily slips in.

    Could you post a demo file for the 3rd question? The one you posted higher up in this thread has no data in columns E and G.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract cells from a workbook (excel)

    In the sample file, column E contains only zeros, so the average of 10 cells in column E is always 0. Is that what you intend?

Page 1 of 3 123 LastLast

Posting Permissions

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