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?
