Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    code help (xp 2003)

    hi all,
    this a revised version of my early post, hope that will makes it more understandable.

    I have a folder with multiple workbooks, each workbook has multiple sheets, I want to extract a range from these sheets if exist, the top of the range has two formats of headings and they can not exist in the same sheet, one format is

    PF Age PF Name PF Type Field Area Reservoir Shore Type Dev Type Well Type Drill Type (from Previous BPLN) Sp Equip Incl. ALFT 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 Total BP Total All
    The second one is

    Field Area Reservoir Shore Type Dev Type Well Type Drill Type (from Previous BPLN) Sp Equip Incl. ALFT BI-60 WO Flag 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 Total BP Total All

    The bottom part of the range can ends with all or two or one of the following lines in both format

    Total Count
    Total New Well Count
    Total BI-60 WO Count

    And I want the extract to be in another workbook and I want the rows to be aligned by the part of the heading "Field Area Reservoir", and to include the workbook and the sheet names..
    TIA
    dubdub

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

    Re: code help (xp 2003)

    It would have been better if you had posted this as a reply in the other thread, since it is a follow-up to it.

    For others reading this: the "early post" dubdub refers to is the thread starting at <post:=665,558>post 665,558</post:>, which has now been locked to prevent duplication.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    hi all,
    can some one help me modify the following code to start the extarct only from the sheets that has the text string "Field Area Reservoir" and to limit the range to be extracted to the row that has "Field Area Reservoir" in it is heading to the bottom part of the range which can ends with all or two or one of the following lines

    Total Count
    Total New Well Count
    Total BI-60 WO Count


    Private Declare Function SetCurrentDirectoryA Lib _
    "kernel32" (ByVal lpPathName As String) As Long

    Public Sub ChDirNet(szPath As String)
    ' Rob Bovey
    Dim lReturn As Long
    lReturn = SetCurrentDirectoryA(szPath)
    If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
    End Sub

    Sub Basic_Example_2()
    Dim MyPath As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long
    Dim SaveDriveDir As String
    Dim FName As Variant


    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    SaveDriveDir = CurDir
    ChDirNet "C:saleh"

    FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xl*), *.xl*", _
    MultiSelect:=True)
    If IsArray(FName) Then

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1


    'Loop through all files in the array(myFiles)
    For Fnum = LBound(FName) To UBound(FName)
    Set mybook = Nothing
    On Error Resume Next
    Set mybook = Workbooks.Open(FName(Fnum))
    On Error GoTo 0

    If Not mybook Is Nothing Then

    On Error Resume Next
    With mybook.Worksheets(1)
    Set sourceRange = .Range("A1100")
    End With

    If Err.Number > 0 Then
    Err.Clear
    Set sourceRange = Nothing
    Else
    'if SourceRange use all columns then skip this file
    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
    Set sourceRange = Nothing
    End If
    End If
    On Error GoTo 0

    If Not sourceRange Is Nothing Then

    SourceRcount = sourceRange.Rows.Count

    If rnum + SourceRcount >= BaseWks.Rows.Count Then
    MsgBox "Sorry there are not enough rows in the sheet"
    BaseWks.Columns.AutoFit
    mybook.Close savechanges:=False
    GoTo ExitTheSub
    Else

    'Copy the file name in column A
    With sourceRange
    BaseWks.Cells(rnum, "A"). _
    Resize(.Rows.Count).Value = FName(Fnum)
    End With

    'Set the destrange
    Set destrange = BaseWks.Range("B" & rnum)

    'we copy the values from the sourceRange to the destrange
    With sourceRange
    Set destrange = BaseWks.Cells(rnum, "B"). _
    Resize(.Rows.Count, .Columns.Count)
    End With
    destrange.Value = sourceRange.Value

    rnum = rnum + SourceRcount
    End If
    End If
    mybook.Close savechanges:=False
    End If

    Next Fnum
    BaseWks.Columns.AutoFit
    End If

    ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = CalcMode
    End With
    ChDirNet SaveDriveDir
    End Sub

    regards,
    dubdub
    TIA
    dubdub

  4. #4
    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: code help (xp 2003)

    I apologize but I have been away from a computer for a while and have not been able to respond or work on your problem. I am on a hotel computer and only have limited time, but i am still confused by what you have and what you want.

    I am trying to get some details and you keep trying to give more "generic" examples. Please provide a sample file set up like your data with real or dummy data in it. it should represent one or more sheets that will have to be processed by your code. this will allow us to see what needs to be done and how it is set up. If there are different column counts, column names, or column setups they should be represented as well as some representative differences in the row counts. If there sheets that should not be process than these should also be shown.

    With the example data (proprietary data should be removed and substituted with generic info) provided, you should then provide the desired output file using the sample data.

    With these examples we can see what needs to be extracted and how it should be extracted. You do make ti difficult to work on the problem since every time I think I have a handle on it, the question seems to change. Before we can even start to help with any coding, we must know what you have and what you want to do with it. I am confused on both counts.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    First let me say that I am thankful that you gave me this time of your time,
    Second, I will try to explain freshly what I have and what I want. I have a workbook for every field and in each workbook there are sheets, each contains certain data about the field, for example:
    Wells
    Reservoirs
    Production
    Injection
    Plan
    ..
    .
    .
    .

    Now, the wells sheet for some fields could be only one sheet but for others there could be as high as four or more sheets for the wells only, one based on location offshore or onshore and/or based on crude grades….etc.

    The good news they all follow two formats as per the attached file. The bad news they do not have common sheet names and will be very tedious to copy and/or to re-name the sheets to one common name, they do not even have static appearance number (order) in the workbooks.

    What I want is to extract the well data from all the workbooks for all the fields which varies in number of rows, but, it is contained between the heading as in the attached file and the bottom three lines shown in the attached file which I also want to extract, each line represent a specific type of well counts data, one is the total well counts, the second one the new well counts to be drilled and the third one well counts of wells to be maintained. for some fields there is possibility no wells to maintain and that will cause the line of well counts to be maintained not to be there and the same if there is no new wells to drill. the three lines data will always begin at years columns.

    I want the data to be extracted and pasted in another workbook and/or sheet called summary and to include with that the workbook name and the sheet name the data extracted from to ease tracking in case of errors and/or changes. In my early posts I have chosen "field Area Reservoir" as the key word to find the well sheets and the starting point or the (column reference) and you could see in wellson sheet there is no column prior the reference column to be extracted and in welloff there are three columns of data prior the reference column.

    I hope the coding will enable me also to play with the key word selection from the headings row.

    Regards,
    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  6. #6
    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: code help (xp 2003)

    This example appears unrelated to any of your examples. Before you were discusing multiple workbooks extracted to a new workbook, ias this still the case. Even the filed names have been change (There is no field starting with "Monthly"

    Should I ignore everything you indicated before and stick with this new one.post or is it still a similar question? I will never be able to get a handle on the question if the data you prodvide is not consistent.

    If this book is opened and the code run, what do you want the output to look like? What data needs to be extracted and where should it go.

    In the past your examples suggested that a sheet may have multiple sets of rows to extract, but your current examples do not seem to suggest that. If there are other data in the worksheets that need to be extracted that also needs to be part of the example sets.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    hi steve,

    i still have multiple workbooks one for each field and they all now in one folder, each workbook is similar to what i have shown in <post#=666,912>post 666,912</post#>, and i still want if possible the code be designed such that it will extract multiple sets of rows if exist in the same sheet beside each other and the data from each workbook to be under each other and all the extract to be in a new workbook and sheet.
    regards,
    dubdub
    TIA
    dubdub

  8. #8
    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: code help (xp 2003)

    Yes it is possible to get multiple sets of rows above other sets and multiple columns within the same rows.

    The question remains, what are some example data sets and what is the output. desired from these examples

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    Steve,

    The example of data will be that say in the last attached file i post which could for field1 there are could be multiple sets of rows contained between the heading row and the total rows in sheet “welloff” and/or sheet “wellson” . If such case exists then these will to the right of the first set and they will be followed by the next workbook or field data and the process will continue until the last workbook (remember the name of the well sheets is not common) and all going to be identified by the workbook and sheet names in the first columns of the output sheet.

    Regards
    dubdub
    TIA
    dubdub

  10. #10
    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: code help (xp 2003)

    Please provide an explicit example sheet of a source data sheet with this type of entry. I still have not seen an example sheet for the output of any sheets you have provided....

    Unless you can provide representative sample sheets of the source data to run code and test and an output sheet so we know when the code is running correctly, I know I can not help you and I also don't believe anyone else will be able to...

    This is not an unwarranted nor a complicated request and I don't understand why you seem to be unable to fulfill it.
    Steve

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    Steve,
    can the code be designed such that it will do the following:
    search thru the sheets in all the workbooks and look for example for three or four key words like "Field Area Reservoir/Shore Type/Dev Type/Well Type" to find the heading row.
    read all the row under it until it find the last total row.
    output that range from every sheet in a separate workbook and sheet.
    include the workbook and the sheet names where that data extracted from with the extracted data.
    should there be more than one heading row repeat the above steps.

    regards,
    dubdub
    TIA
    dubdub

  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: code help (xp 2003)

    I am sure it could be designed to do this.

    But as before could you provide examples and the details of what you have and what you want to do?

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    steve,
    can you please design it to do what i have mentioned in the post using those key words, and after it find those key words it will read all the words in that row and that will be the maximum number of columns or the heading in the output sheet and the number of rows not to exceed 1000 rows.

    regards
    dubdub
    TIA
    dubdub

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

    Re: code help (xp 2003)

    Since you refuse to give Steve what he has been asking for, why should he waste any more time asking you again?
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: code help (xp 2003)

    hi legare,

    i appreciate all the time steve gave to respond to my posts, and i just want the code to be flexible such that i can use it in the future and any one can also use with minor changes that does not require coding experience.

    regards,
    dubdub
    TIA
    dubdub

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
  •