Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > excel 2000 - manipulate two applications
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 43
Default excel 2000 - manipulate two applications
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 176
Default excel 2000 - manipulate two applications
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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 43
Default excel 2000 - manipulate two applications
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






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 176
Default excel 2000 - manipulate two applications

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








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 43
Default excel 2000 - manipulate two applications
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










Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 43
Default excel 2000 - manipulate two applications
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.

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












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I manipulate an excel chart by clicking and verify the ta Danesh New Users to Excel 1 March 11th 08 08:50 AM
How do I manipulate data pulled from within an excel calendar? wkalmbach Excel Discussion (Misc queries) 1 August 21st 07 03:46 PM
Excel: How to insert carriage returns in a formula to manipulate t Cardinal2B Excel Worksheet Functions 1 June 21st 07 04:17 PM
How do I manipulate/sort data from a drop-down Excel list? [email protected] Excel Discussion (Misc queries) 0 February 15th 06 06:11 PM
Manipulate an excel column Bill R Excel Worksheet Functions 2 May 17th 05 07:01 PM


All times are GMT +1. The time now is 05:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement