Getting data from multiple Excel books (2003)

Topic

New Reply

I have an app that must get some values from multiple Excel books.

The Excel books are named YYYYQQ_FileName.xls where YYYYQQ is the year and then the quarter (01,02,03,04). Within the books are multiple sheets named YYYYQQ_DeviceName.

We never have any quarters older than 200701.

For a given quarter (200801, for example) and a given device (Handset1, for example) I need to have the values in cell I18 for all workbooks prior to the given quarter.

In my example I would need
200701_FileName.xls!700701_Handset1!I18 +
200702_FileName.xls!700702_Handset1!I18 +
200703_FileName.xls!700703_Handset1!I18 +
200704_FileName.xls!700704_Handset1!I18 +

I’m trying to get a function that I can pass the quarter & the HandsetName and it will return the correct value for me but I’m not sure how to do this.

This is what I have so far, but I don’t know how to properly reference my cells to get the values out. See the 3 lines following “‘NOT SURE HOW TO DO THIS PROPERLY”

Sub GetPredeploymentTIL(rQtrin As String, sTypein As String, DevIDin As String)
'REMOVE THESE LINES FOR PRODUCTION
setPaths
Set xlapp = CreateObject("excel.application")
'END REMOVE THESE LINES FOR PRODUCTION

    Dim curToReturn As Currency
    Dim tmpRYear As Integer
    tmpRYear = Left(rQtrin, 4)
    Dim tmpRQtr As Integer
    tmpRQtr = Right(rQtrin, 2)
    
    Dim tmpQtr As String
    tmpQtr = "200701"
    
    For xYear = 2007 To tmpRYear
        For xQtr = 1 To 4
        
        tmpQtr = xYear & "0" & xQtr
        If CLng(tmpQtr) >= CLng(rQtrin) Then
            'Done
        Else
            Debug.Print tmpQtr
            With xlapp
                .Workbooks.Open fileName:=finalReportPath & tmpQtr & "_TCO_Detail_Report_" & _
                    sTypein & ".xls"
'NOT SURE HOW TO DO THIS PROPERLY
                xlapp.Sheets(tmpQtr & "_" & getDeviceName(DevIDin)).Select
                xlapp.Range("$J$8").Activate
                curToReturn = curToReturn + .Activecall.Value
                .Workbooks.Close
            End With
            
        End If
        Next xQtr
    Next xYear
End Sub

Any pointers?

Reply | Quote

Replies

Why does your code refer to Range(“$J$8”) if you need I18?

Reply | Quote

Sorry, copy/paste from somewhere else that hasn’t been corrected.

Reply | Quote

Does it work better if you correct the cell reference in the code?

Reply | Quote

No, but it works great if this line

curToReturn = curToReturn + .Activecall.Value

reads

curToReturn = curToReturn + .ActiveCell.Value

like is was supposed to.

Activecall != ActiveCell.

Crazy typos!

Reply | Quote