Loading from multiple sheets on another workbook

luxcs · March 25th, 2004, 03:00 PM

Hi
I'm trying to extend an existing function I wrote to use different sheets on a different workbook, instead of the active workbook.

The function below did work when using 'local' sheets. But I want to load all my data into a another xls file and load/read from it as and when required. I use multiple sheets a lot and don't want to have to add the same sheets to each of my workbooks each time i want to use the data.

It had now stopped working. It works for a split-second if I manually load the test.xls file, but after a sec it refreshs and returns error values.

Also, as you can see, I'm trying to get it to return "n/a" if it can't find a matching set of values, instead of #VALUE!.

Any ideas anyone? :)

Code:

Public Function lookupref(ref As String, dateref As Date, season As String, reqdcol As String)
    Application.Volatile

   'Load and activate correct season sheet
    Dim wbTest As Workbook
    tmpfile = "c:\test.xls"
    Set wbTest = Workbooks.Open(Filename:=tmpfile)
    wbTest.Activate

    'Get size of dataset
    lastrow = ActiveWorkbook.Worksheets(season).Cells(2, "A").Value

    'Loop data
    lookupref = "n/a"
    For a = 3 To (Int(lastrow) + 2)
        tmpflt1 = UCase(ref)
        tmpflt2 = UCase(ActiveWorkbook.Worksheets(season).Cells(a, "B").Value)
        tmpdate1 = dateref
        tmpdate2 = DateValue(ActiveWorkbook.Worksheets(season).Cells(a, "A").Value)
        'MsgBox tmpflt1 & " " & tmpflt2 & " " & tmpdate1 & " " & tmpdate2
        If (tmpflt1 = tmpflt2) Then
            If (tmpdate1 = tmpdate2) Then
                'MsgBox "Match"
                lookupref = ActiveWorkbook.Worksheets(season).Cells(a, reqdcol).Value
                Exit Function
            Else
                lookupref = "n/a"
            End If
        Else
            lookupref = "n/a"
        End If
    Next a

    ActiveWorkbook.Close
End Function