Finding current cell row in spreadsheet from Word macro

  • Thread starter Thread starter boswellgw
  • Start date Start date
B

boswellgw

Group,
I am writing a macro in MS Word v.2002. I have open an Excel workbook
in which I want to place some data. Since the position of the data
varies, I want to place the data in the row where the cursor is
located. In an Excel macro I can determine the row
with the cursor as follows:

ThisRow=ActiveCell.Row

This code doesn't work in the Word macro. So I used the following
code:


Set BSParameters = GetObject("MODEL.xls")
BSParameters.Application.Visible = True
BSParameters.Parent.Windows(1).Visible = True
Set WorkingSheet = BSParameters.Worksheets("SUMMARY")
BSParameters.Worksheets("SUMMARY").Activate

ThisRow = BSParameters.Worksheets("SUMMARY").ActiveCell.Row

This also did not work. If I substitute this line:

ThisRow = BSParameters.Worksheets ("SUMMARY").Cells 4,1).Value. 'works
fine

the code functions as expected. However, if I use this line:

ThisRow = BSParameters.Worksheets("SUMMARY").ActiveCell.Value 'does
not work

The code doesn't work.

So there is some problem with using the "ActiveCell" function. Is
there an equivalent way to do this
in MS Word?

Regards,

Garry
 
This seemed to work ok for me when called from MSWord.

Option Explicit
Sub testme01()

Dim XLApp As Object
Dim XLWkbk As Object
Dim xlWks As Object

Dim ThisRow As Long
Dim XLWasRunning As Boolean
Dim myFileName As String

myFileName = "C:\my documents\excel\book1.xls"

XLWasRunning = True
On Error Resume Next
Set XLApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set XLApp = CreateObject("Excel.Application")
XLWasRunning = False
End If
On Error GoTo 0

XLApp.Visible = True 'Nice for testing

Set XLWkbk = XLApp.workbooks.Open(FileName:=myFileName)
Set xlWks = XLWkbk.worksheets("summary")

xlWks.Select
ThisRow = XLApp.ActiveWindow.activecell.Row

xlWks.Cells(ThisRow, "A").Value = "hi there"

XLWkbk.Close savechanges:=True
If XLWasRunning Then
'do nothing
Else
XLApp.Quit
End If

Set xlWks = Nothing
Set XLWkbk = Nothing
Set XLApp = Nothing

End Sub
 
Dave,

Thank you for the response. I compared your working code with mine and
found a couple of differences, which seemed to be critical. I had
defined xlApp=Excel.Aplication and xlWkbk=Excel.Workbook. And where
you had xlWks.Select I had xlWks.Activate. With those changes the
macro works like a charm. Thanks for the help. It will save me many
hours by being able to use the macro instead of entering the data
manually.

Regards,

Garry
 
Dave,

Thank you for the response. I compared your working code with mine and
found a couple of differences, which seemed to be critical. I had
defined xlApp=Excel.Aplication and xlWkbk=Excel.Workbook. And where
you had xlWks.Select I had xlWks.Activate. With those changes the
macro works like a charm. Thanks for the help. It will save me many
hours by being able to use the macro instead of entering the data
manually.

Regards,

Garry
 
Back
Top