excel 2000 - manipulate two applications

[Matt.]

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\AmaliaWeeklyOperatingReport.xls"

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

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

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

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

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

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

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

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

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

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

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

Case 13
strFileName =
"\\hserver01\hworking\weeklyKPIs\Warren\RollieWeeklyOperatingReport.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

 

[Bernie Deitrick]

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.]

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]

Matt,

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

HTH,
Bernie
Excel MVP

 

[Matt.]

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.

 

[Matt.]

This works! You don't need to reference the Worksheet!

iIndexRow = oExcel.ActiveCell.Row
iIndexCol = oExcel.ActiveCell.Column
iIndexValue = oExcel.ActiveCell.Value
MsgBox iIndexRow & "; " & iIndexCol & "; " & iIndexValue


cheers,

Matt.