Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how to extract data from different workbooks (excel)

    'The problem that I have run into is while the macro "attached", runs through a workbook it looks for a worksheet "coil summary" some of the workbooks dont have a coil summary worksheet so when the macro encounters this situation it stops. Is there a way to 'jump workbooks that dont have coil summary work sheets and move on instead of stoping the macro and messaging out of range?
    attached is the code that extracts data from coil summarys worksheet.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    Does this do what you want?

    <pre>Option Explicit

    Sub extracter()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim i As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim oCS As Worksheet

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B,J:J").Clear
    strPath = wsh.Range("R1")
    Set fld = fso.GetFolder(strPath)
    For Each sfl In fld.SubFolders
    i = i + 1
    wsh.Range("A" & i) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    If strFile = "" Then
    wsh.Range("C" & i) = "No workbook found"
    Else
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    Set oCS = Nothing
    On Error Resume Next
    Set oCS = wbk.Worksheets("Coil Summary")
    On Error GoTo ErrHandler
    If Not oCS Is Nothing Then
    wsh.Range("B" & i) = oCS.Range("J9")
    wsh.Range("C" & i) = oCS.Range("B5")
    wsh.Range("D" & i) = oCS.Range("J6")
    wsh.Range("P" & i) = oCS.Range("B6")
    oCS.Range("A29:K33").Copy
    wsh.Paste Destination:=wsh.Range("E" & i)
    Application.CutCopyMode = False
    oCS.Range("A61:K61").Copy
    wsh.Paste Destination:=wsh.Range("E" & (i + 5))
    Application.CutCopyMode = False
    wsh.Range("E" & (i + 6)) = "Frac Gradient(Kpa/M)"
    wsh.Range("E" & (i + 7)) = oCS.Range("J7")
    wbk.Close SaveChanges:=False
    Else
    wsh.Range("C" & i) = "Coil Summary not found"
    End If
    End If
    i = i + 9
    Next sfl

    ExitHandler:
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    Application.ScreenUpdating = True
    ThisWorkbook.Close SaveChanges:=True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: how to extract data from different workbooks (excel)

    FWIW I use a different approach from Legare's to test for the existence of a sheet within a Workbook:

    Dim strWksNames As String
    For Each wks In ActiveWorkbook.Worksheets
    strWksNames = strWksNames & wks.Name
    Next wks
    If InStr(strWksNames, "Coil Summaries") Then
    With ActiveWorkbook.Worksheets("Coil Summaries")
    etc.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: how to extract data from different workbooks (excel)

    At the Else statement I think you would want to close the workbook since it does not relate to your needs? so maybe add below
    Else wsh.Range("C" & i) = "Coil Summary not found"

    wbk.Close.SaveChange:=False <

    correct me if I am wrong but if you have many excel files to scan through you need to close the useless ones.

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

    Re: how to extract data from different workbooks (excel)

    In fact, you could put the line below the End If, since you want to close the workbook in all cases.

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    Thanks you, that did actually solved a memory speeed issue,

    I have relized though that the workbooks being skipped by the Else statment actually hold some valuable data. The workbooks being skipped have a worksheet called Job Summary. If the else statement could look for the Job summary worksheet and copy theses cells I would be jumping for joy.

    Ive been thinking that it be simlar to other copy cell precedures.

    wsh.Range("B" & i) = wbk.Worksheets(" Job Summary").Range("C5")
    wsh.Range("C" & i) = wbk.Worksheets(" Job Summary ").Range("J7")
    wsh.Range("D" & i) = wbk.Worksheets(" Job Summary ").Range("K29")
    wsh.Range("P" & i) = wbk.Worksheets(" Job Summary ").Range("C6")
    wbk.Worksheets("Job Summary").Range("J14:K16").Copy
    wsh.Paste Destination:=wsh.Range("E" & i)
    Application.CutCopyMode = False

    and if there is neither a coil summary, or Job summary worksheet then skip workbook and close

    the coil summary is primary, Work books with a coil summary worksheet have a jobsummary worksheet aswell, coil summary is only need in this case.

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

    Re: how to extract data from different workbooks (excel)

    You should be able to expand the code already given to you. In the Else part, test whether there is a worksheet named "Job Summary" and if so, copy data from it into wsh, otherwise skip the workbook.

    Note: computers are finicky. Your sample code uses " Job Summary" and " Job Summary ". If the name of the worksheet doesn't contain leading or trailing spaces, you shouldn't have them in the code either. Even the smallest difference in spelling will cause your code to fail.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    What is the name of the Job Summary sheet? Is it "Job Summary", " Job Summary", or " Job Summary "? You have it all three ways in your code. The code below uses "Job Summary". If that is not correct, you will need to change the code (you really should have been able to write this yourself by looking at what I have already done).

    <pre>Option Explicit

    Sub extracter()
    Dim fso As Object
    Dim fld As Object
    Dim sfl As Object
    Dim strPath As String
    Dim strFile As String
    Dim i As Long
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim oCS As Worksheet

    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wsh = ThisWorkbook.Worksheets(1)
    wsh.Range("A:B,J:J").Clear
    strPath = wsh.Range("R1")
    Set fld = fso.GetFolder(strPath)
    For Each sfl In fld.SubFolders
    i = i + 1
    wsh.Range("A" & i) = sfl.Path
    strFile = Dir(sfl.Path & "*.xls")
    If strFile = "" Then
    wsh.Range("C" & i) = "No workbook found"
    Else
    Set wbk = Workbooks.Open(Filename:=sfl.Path & "" & strFile, AddToMRU:=False)
    Set oCS = Nothing
    On Error Resume Next
    Set oCS = wbk.Worksheets("Coil Summary")
    On Error GoTo ErrHandler
    If Not oCS Is Nothing Then
    wsh.Range("B" & i) = oCS.Range("J9")
    wsh.Range("C" & i) = oCS.Range("B5")
    wsh.Range("D" & i) = oCS.Range("J6")
    wsh.Range("P" & i) = oCS.Range("B6")
    oCS.Range("A29:K33").Copy
    wsh.Paste Destination:=wsh.Range("E" & i)
    Application.CutCopyMode = False
    oCS.Range("A61:K61").Copy
    wsh.Paste Destination:=wsh.Range("E" & (i + 5))
    Application.CutCopyMode = False
    wsh.Range("E" & (i + 6)) = "Frac Gradient(Kpa/M)"
    wsh.Range("E" & (i + 7)) = oCS.Range("J7")
    Else
    Set oCS = Nothing
    On Error Resume Next
    Set oCS = wbk.Worksheets("Job Summary")
    On Error GoTo ErrHandler
    If Not oCS Is Nothing Then
    wsh.Range("B" & i) = oCS.Range("C5")
    wsh.Range("C" & i) = oCS.Range("J7")
    wsh.Range("D" & i) = oCS.Range("K29")
    wsh.Range("P" & i) = oCS.Range("C6")
    oCS.Range("J14:K16").Copy
    wsh.Paste Destination:=wsh.Range("E" & i)
    Application.CutCopyMode = False
    Else
    wsh.Range("C" & i) = "Summary not found"
    End If
    End If
    wbk.Close SaveChanges:=False
    End If
    i = i + 9
    Next sfl

    ExitHandler:
    Set wbk = Nothing
    Set sfl = Nothing
    Set fld = Nothing
    Set fso = Nothing
    Application.ScreenUpdating = True
    ThisWorkbook.Close SaveChanges:=True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </pre>

    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    thank you Legare and Hans for taking a look and spending your time on it. I have learned alot from your help and will use your examples to build on to it. Is there some common basics of loops you could share with me and certain ways of writing conditional statements? what do you always look at when you build a loop?

    P.s If I want ot add a title to the cell on the right beside the pasted cell, would i DO SOMETHING LIKE THIS?

    wsh.Range("C" & i) = "isip" & wsh.Range("D" & i)= oCS.Range("K29")

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

    Re: how to extract data from different workbooks (excel)

    No, that statement doesn't make sense. You cannot conbine two assignments in one instruction.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    I don't know what you are trying to do. That statement will set the cell in column C row i to either True or False.
    Legare Coleman

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: how to extract data from different workbooks (excel)

    Are you looking for 2 lines:

    wsh.Range("C" & i) = "isip"
    wsh.Range("D" & i)= oCS.Range("K29")

    Steve

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

    Re: how to extract data from different workbooks (excel)

    are you trying to give a title to the cell your pulling out? so on the right cell there will be isip "cell c" and on the left of it is the data?
    I beleive steves will do that

  14. #14
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: how to extract data from different workbooks (excel)

    yes, just trying to give the cells i pull out a title on the left side of the data so i know what the data is.

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

    Re: how to extract data from different workbooks (excel)

    impressive macro

Page 1 of 2 12 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
  •