Announcement

Collapse
No announcement yet.

Using loop to extract data from another workbook

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using loop to extract data from another workbook



    I have an Excel workbook that collects and then formats, summarises and interogates data which is sourced from another workbook.
    This other Excel workbook (called Data Dump) updates at irregular intervals through the day, and may, at any one time, contain a single, or multiple lines of data.
    What I am trying to do is to write some code which will perform the following steps:
    (1) Open the Data Dump workbook
    (2) Check if Cell A1 is empty
    (3) If cell A1 is empty then close Data Dump
    (4) If cell A1 contains data then copy the row and paste it in a particular location in Summary workbook
    (5) In Summary workbook, run a series of macros on the data
    (6) When macro's in (5) have finished running, go back to Data Dump and check if there is any data in A2
    (7) If cell A2 is empty then close the Data Dump file
    (8) If cell A2 contains data then loop through (4) above etc etc until there is no more data and then close the Data Dump file

    I am able to accomplish most of the above component parts, but I am not sure how to bring it all together with the code to loop down the series of events and run other macros in between, and then to go back and step down. Can someone please tell me what the section of code would be to loop through (4) - (7) and which would allow me to run a series of macros in the loop? Thanks.

  • #2
    Re: Using loop to extract data from another workbook

    bbromley,

    Nice of you to have done all the coding work. But can you post the code. Might be usefull if you put it in an empty workbook along with a small copy of the Data Dump workbook.


    Should be an easy thing to put it all together.
    Regards,
    Barry

    My Favorite New Thing:
    Dynamic Named Ranges



    The alternative for
    "Press Any Key To Continue."

    and we all have one we'd like to use it on

    1. Cross Posting Etiquette
    2. Are You Here To Learn: What Have You Tried?
    3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

    Comment


    • #3
      Re: Using loop to extract data from another workbook

      The code that I am trying to create a loop with is shown below. I want to loop through the process so that after the Enter_Trade macro has run it will loop back to the Data Dump workbook and check if there is any data in cell A2 and so on, until it encounters a blank cell.

      Code:
      Sub Collect_Trade_Data()
      
      '   Open Data Dump workbook
          Workbooks.Open Filename:="K:\Utilities\Hedge Fund P&L\Data Dump.xls"
          Windows("Data Dump.xls").Activate
      
      '   Check if the first cell contains data. If not then close file
          If Range("Sheet 1!A1") = "" Then
                  ActiveWorkbook.Close SaveChanges:=False
                  ActiveWorkbook.Saved = True
      
      '   If the cell does contain data then transfer the row of data across to the Summary file
          Else
              Range("A1:J1").Select
              Selection.Copy
              Windows("Summary.xls").Activate
              Sheets("FormData").Activate
              Range("C13").Select
                 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=True
          
      '   Run the trade entry and formatting macros in Trade Data file
          Enter_Trade
          End If
           
      '   Save and close the Trade Data file
          ActiveWorkbook.Close SaveChanges:=True
          ActiveWorkbook.Saved = True
      
      End Sub

      Comment


      • #4
        Re: Using loop to extract data from another workbook

        bbromley,


        Try this. It's not tested.




        Code:
        Sub Collect_Trade_Data()
             
        '   Local Variables
            Dim wkbData     As Workbook
            Dim cell        As Range
        
        '   Retreive all available data in source workbook
            Do While True
            ' Open DATADUMP
                Set wkbData = Workbooks.Open(Filename:="K:\Utilities\Hedge Fund P&L\Data Dump.xls", ReadOnly:=True)
             
            ' Check status of source data
                If wkbData.Worksheets("Sheet 1").Range("A1") = "" Then
                    wkbData.Close False
                    Exit Do
                Else
                    For Each cell In wkbData.Worksheets("Sheet 1").Range(wkbData.Worksheets("Sheet 1").Range("A1"), wkbData.Worksheets("Sheet 1").Range("A65536").End(xlUp))
                        cell.Range("A1:J1").Copy
                        ThisWorkbook.Worksheets("FormData").Range("C13").PasteSpecial _
                                Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
                         '   Run the trade entry and formatting macros in Trade Data file
                        Call Enter_Trade
                    Next cell
                    wkbData.Close False
                End If
            Loop
             
             '   Save and close the Trade Data file
            ActiveWorkbook.Close SaveChanges:=True
            ActiveWorkbook.Saved = True
             
        End Sub
        Last edited by iwrk4dedpr; October 26th, 2005, 04:34.
        Regards,
        Barry

        My Favorite New Thing:
        Dynamic Named Ranges



        The alternative for
        "Press Any Key To Continue."

        and we all have one we'd like to use it on

        1. Cross Posting Etiquette
        2. Are You Here To Learn: What Have You Tried?
        3. Your Best Resource to begin learning VBA: RECORD A MACRO . . . Then Study It!

        Comment


        • #5


          Re: Using loop to extract data from another workbook

          Barry, you're a star. I'll give it a go now, but more importantly I think you have helped me understand the Do While process a bit better. Thank you for taking the time!

          Comment

          Working...
          X