Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Values not Links (2000/SR-1)

    I have found the answer to most of my question by reviewing past posts, but one thing in particular still eludes me.

    The basics. I am changing a "legacy" spreadsheet which contains over a hundred sheets (each representing an Operating Location) - each sheet linking to a different spreadsheet (in some cases more than one) on a network drive. Each month this "destination" spreadsheet would be updated with an Edit-Find-Replace to change all the links' directories to the current month. Lots of problems with this process - which I will not go into.

    The change I am attempting is to list every Operating Location on ONE page and write a macro to extract the data from the appropriate file and place the DATA ONLY into the correct cell on the new spreadsheet. Essentially, creating one table of all the data instead of massive spreadsheet with hundreds of links.

    The solutions that I have found all deal with how to find/get the data on a one-to-one basis from a known single source to a known destination; some even handle this problem on a many-to-many basis. My problem is I have over a hundred potential many-to-one issues, and I do not know how to handle the error checking.

    The Issue. For each Operating Location there on occasion may be up to THREE different sources for the data (which must be combined). This was handled in the OLD VERSION with a manual review of the source file directory and an update to the destination's link formula to include more than one source (e.g., ='S:....[source1.xls]Total'!B1 + 'S:....[source2.xls]Other'!E4, etc.) - yuck!

    What I would like to do is for each Operating Location search for all 3 possible files; skip those sources that do not exist (without blowing up the macro); and sum the appropriate data for sources that do exist; and place that sum into my new table. The three possible source files for each are ######finpkg.xls, ######MEDHS.xls and ######finpkg2.xls; where ###### is the six-digit identifier for the Operating Location.

    Thanks in advance for any help, or a pointer to a post I may have overlooked.

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

    Re: Multiple Values not Links (2000/SR-1)

    From your description, it is a little difficult to determine exactly what you need to do. However, see if THIS POST might get you started in the right direction.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Values not Links (2000/SR-1)

    Apologize if too vague or confusing. I think I noticed the post you reference earlier, but it either will not solve my problem - or I do not fully understand how it works.

    In the simplest form, I need...
    * For "Destination.xls" I need data for Operating Location number 123456
    * Source data is from Excel files on Network Drive s:...MMYY (where MM is the two-digit month, YY is two-digit year)
    * Source files can be none or one or more of the following - 123456finpkg.xls, 123456MEDHS.xls and 123456finpkg2.xls
    * No way to know in advance which files will or will not be present for an individual Operating Location
    * Need to check to see if each Source file exists;
    * If Source file does exist - open the Source file pull data from Source (cell address) and add to DestinationDataRange in Destination.xls - close Source file. Check next source file, etc.
    * If Source file doe not exist - move to next Source file

    Ideally,
    * Need "Outside Loop" for 1 to N number of locations (I assume I can use a Count function or something similar to determine N)
    * Need "Inside Loop" for 1-3 number of possible Source files

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

    Re: Multiple Values not Links (2000/SR-1)

    The following code can be used to see if a file exists:

    <pre>Dim strFileName
    strFileName = Dir("C:worktest.xls", vbNormal)
    If strFileName = "" Then
    MsgBox "The file does not exist"
    Else
    MsgBox "The file exists."
    End If
    </pre>


    You would subistitute your path and file name for "C:worktest.xls". You could also use the code in the message I pointed you to to loop through all of the files in the directory and process the ones you want to. Does that get you started?

    I assume that you would have a list of locations somewhere that you are going to loop through. If those are in cells on a worksheet, then you would need to loop through those cells. If they are in an array in the code, then loop through the array. You could use a loop like the one in the message I pointed you to with a file name like "123456*.xls" to find all of the files in the directory related to location "123456". There are many more ways to do this depending on how you will have things set up.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Values not Links (2000/SR-1)

    I think this will get me there - thanks for the help and patience.

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

    Re: Multiple Values not Links (2000/SR-1)

    If you get stuck, come back with some more specific questions.
    Legare Coleman

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Values not Links (2000/SR-1)

    This is working (on my test version), but one problem I am encountering but did not anticipate is missing tabs. I need to be able to find either a sheet named "TOTAL" or one named "AGING" after successfully opening a file. If neither exists, then I need to skip that file and go to the next. If both exist I'll need to use data on the "TOTAL" sheet.

    Thanks.

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

    Re: Multiple Values not Links (2000/SR-1)

    The code below might get you started on how to do this:

    <pre>Dim oSheet As Worksheet
    Set oSheet = Nothing
    On Error Resume Next
    Set oSheet = Worksheets("TOTAL")
    If oSheet Is Nothing Then
    Set oSheet = Worksheets("AGING")
    If oSheet Is Nothing Then
    'Skip this workbook
    End If
    End If
    On Error GoTo 0
    MsgBox "Using worksheet " & oSheet.Name
    </pre>

    Legare Coleman

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Values not Links (2000/SR-1)

    Hi,

    Apart from checking in the macro, I guess you *could* include an error check in the formula omitting the ones that are missing from the sum:

    ='S:....[source1.xls]Total'!B1 + 'S:....[source2.xls]Other'!E4,

    =SUM(IF(ISERROR('S:....[source1.xls]Total'!B1),"",'S:....[source1.xls]Total'!B1),IF(ISERROR('S:....[source2.xls]Other'!E4),"",''S:....[source2.xls]Other'!E4))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Star Lounger
    Join Date
    Jun 2001
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Values not Links (2000/SR-1)

    Legare,

    Finally got a chance to get back on this today. Not sure I understand this error checking code exactly (being a VBA novice), but it seems to be working great. Thanks!

Posting Permissions

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