Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting data from multiple Excel books (2003)

    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"
    <pre>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</pre>



    Any pointers?
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting data from multiple Excel books (2003)

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

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting data from multiple Excel books (2003)

    Sorry, copy/paste from somewhere else that hasn't been corrected.
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Getting data from multiple Excel books (2003)

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

  5. #5
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting data from multiple Excel books (2003)

    No, but it works great if this line <pre>curToReturn = curToReturn + .Activecall.Value</pre>

    reads <pre>curToReturn = curToReturn + .ActiveCell.Value</pre>

    like is was supposed to.

    Activecall != ActiveCell.

    Crazy typos!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

Posting Permissions

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