Hi Bernie!

No, it doesn't help. Error 438.

However, I have been able to get the Selection.End to work with this:

oExcel.Worksheets("Sheet1").Activate

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

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

But now I'm getting an error at iIndexRow =
oBook.Worksheets("Sheet1").ActiveCell.Row

This property not supported, and the entire line is highlighted. I've tried
oExcel, and I've tried oExcel.oBook, but neither works.

I do appreciate your persistance.

cheers,
Matt.


"Bernie Deitrick" wrote in message
...

Matt,

Perhaps that extra space that I inadvertently put it in before .Workbooks
should be removed.....?

HTH,
Bernie
Excel MVP


"Matt." wrote in message
.. .
oExcel.oBook .Worksheets("Sheet1").Activate

I get an error stating:

Compile error: invalid or unqualified reference

and ".Worksheets" is highlighted.

Any other pointers?

cheers,
Matt.

"Bernie Deitrick" wrote in message
...
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