Multiple Values not Links (2000/SR-1)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

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

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

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

Reply | Quote

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!

Reply | Quote

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))

Reply | Quote