Matt,

Try changing

oExcel.Worksheets("Sheet1").Activate

to

oExcel.oBook .Worksheets("Sheet1").Activate

since worksheets isn't an application level property.

HTH,
Bernie
Excel MVP

"Matt." wrote in message
.. .
Hi all!

Below is my unfinished sub to read data from one sheet and transfer the
value to another. Not very complicated, I admit. I know that it can be
done via excel's linking and embedding, but the sheer volume of the data
creates horrific data transfer volumes, and I'm trying to avoid that.

That said, I am having some trouble selecting the appropriate excel
application. You can see that I am opening a New excel application. Once
I
open the file with this:

'open the right source file
Set oBook = oExcel.Workbooks.Open _
(Filename:=strFileName, _
ReadOnly:=True)

oExcel.Worksheets("Sheet1").Activate

do I need to specify oExcel again? If so, how? The Selection.End
statement
below seems to be working on the workbook where the code is run from, as
opposed to the workbook that I selected.

' find the first KPI index value
oExcel.Worksheets("Sheet1").Range("A1").Select
Selection.End(xlDown).Select

Any help is greatly appreciated.

cheers,
Matt.

P.S. the WeekNumber function referenced in the InputBox function is
available on Microsoft's web site
======================================
Sub UpdateData()

Dim iWkNumber As Integer
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim strFileName As String
Dim iCount As Integer
Dim iIndexRow As Integer
Dim iIndexCol As Integer
Dim iIndexValue As Integer
Dim iKPIRow As Integer
Dim iKPICol As Integer
Dim vKPIValue As Variant

iWkNumber = CInt(InputBox("What week do you wish to copy?", "Enter a
week",
CStr(WeekNumber(Date) - 1)))

Set oExcel = New Excel.Application
oExcel.Visible = True

For iCount = 13 To 13
' determine which file to open
Select Case iCount
Case 1
strFileName =
"\\hserver01\hworking\weeklyKPIs\AmaliaZ\AmaliaWee klyOperatingReport.xls"

Case 2
strFileName =
"\\hserver01\hworking\weeklyKPIs\DarrenF\DarrenFWe eklyOperatingReport.xls"

Case 3
strFileName =
"\\hserver01\hworking\weeklyKPIs\DianaM\DianaMWeek lyOperatingReport.xls"

Case 4
strFileName =
"\\hserver01\hworking\weeklyKPIs\DougC\DougCWeekly OperatingReport.xls"

Case 5
strFileName = "\\hserver01\hworking\weeklyKPIs\GavinB\John E
WeeklyOperatingReport.xls"

Case 6
strFileName = "\\hserver01\hworking\weeklyKPIs\JohnH\JohnHWe ekly
KPIs.xls"

Case 7
strFileName =
"\\hserver01\hworking\weeklyKPIs\KathyP\KATHYPWeek lyOperatingReport.xls"

Case 8
strFileName =
"\\hserver01\hworking\weeklyKPIs\KenL\KenLWeeklyOp eratingReport.xls"

Case 9
strFileName =
"\\hserver01\hworking\weeklyKPIs\MaraB\MARABWeekly OperatingReport.xls"

Case 10
strFileName =
"\\hserver01\hworking\weeklyKPIs\PeteOD\WeeklyOper atingReport.xls"

Case 11
strFileName =
"\\hserver01\hworking\weeklyKPIs\RaquelG\RAQUELGWe eklyOperatingReport.xls"

Case 12
strFileName =
"\\hserver01\hworking\weeklyKPIs\RaziaD\raziaWeekl yOperatingReport.xls"

Case 13
strFileName =
"\\hserver01\hworking\weeklyKPIs\Warren\RollieWeek lyOperatingReport.xls"

End Select
'open the right source file
Set oBook = oExcel.Workbooks.Open _
(Filename:=strFileName, _
ReadOnly:=True)

oExcel.Worksheets("Sheet1").Activate

' find the first KPI index value
oExcel.Worksheets("Sheet1").Range("A1").Select
Selection.End(xlDown).Select

iIndexRow = oExcel.Worksheets("Sheet1").ActiveCell.Row
iIndexCol = oExcel.Worksheets("Sheet1").ActiveCell.Column
iIndexValue = oExcel.Worksheets("Sheet1").ActiveCell.Value

' find the right week and the right year
oExcel.Worksheets("Sheet1").Range("A1").Select
Cells.Find(What:=iWkNumber, After:=ActiveCell, LookIn:=xlValues, LookAt:=
_
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate

iKPIRow = ActiveCell.Row + 1
iKPICol = ActiveCell.Column
vKPIValue = ActiveCell.Value
Range(Cells(iKPIRow, iKPICol)).Select

If Year(ActiveCell.Value) < 2003 Then
iKPIRow = iKPIRow - 1
Range(Cells(iKPIRow, iKPICol)).Select
Cells.FindNext(After:=ActiveCell).Activate
'Cells.Find(What:=iWkNumber, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False) _
.Activate
End If

iKPIRow = iIndexRow
iKPICol = ActiveCell.Column
Range(Cells(iKPIRow, iKPICol)).Select
vKPIValue = ActiveCell.Value

MsgBox vKPIValue

Set oBook = Nothing
Set oExcel = Nothing

Next iCount

End Sub