Error when WB already open

[David Sisson]

I have a macro in Word that calls Excel to store some info.

If I already have Excel open to another WB, I always get this error.

Application-defined or object-defined error 16
Error: 1004

If Excel is closed, it runs fine.

Here is the code up to the line that gives the error.

Sub AddToTable2(OANum)
' paste information from O&A into Excel Sheet
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRng As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim WorkbookToWorkOn As String
Dim FileNum As Integer

Set oRng = Nothing
Set oSheet = Nothing
Set oWB = Nothing
Set oXL = Nothing

'specify the workbook to work on
WorkbookToWorkOn = "C:\my documents\O&A\OA index.XLS"

'If Excel is running, get a handle on it; otherwise start a new
instance of Excel
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
ExcelWasNotRunning = True
Set oXL = New Excel.Application
End If

On Error GoTo Err_Handler

'Open the workbook
Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)

'Process each of the spreadsheets in the workbook
'Set oSheet =
oXL.Workbooks(WorkbookToWorkOn).Worksheets(GlWorkBookName)
Set oSheet = oWB.Worksheets(GlWorkBookName)
oSheet.Activate

'Paste fields from Word table to Excel table
Dim WONum$, SN$
Dim MyTable As Table
Dim RowI As Integer

Set MyTable = Documents(GlLogName).Tables(1)

Documents(GlLogName).Tables(1).Range.EndOf Unit:=wdTable,
Extend:=wdMove

'Position to last row
Set topCel = oSheet.Range("A2")
Set bottomCel = oSheet.Range("A65536").End(xlUp)
Set SourceRange = oSheet.Range(topCel, bottomCel)
'Set Selection = oSheet.Range(activerow).End(xlDown)

'Find last cell with data in column A. Should be one less than Totals
line.
RowI = oSheet.Cells(Rows.Count, "A").End(xlUp).Row

This last line is where the error always occurs.

Thanks in adance
David

P.S. Before I posted this, I realized I could use
SourceRange.rows.count + 1 to get the same result, but I'm still
curious why the Cells(rows.Count) doesn't always work?

 

[Tom Ogilvy]

You need to qualify rows I would suspect


RowI = oSheet.Cells(oSheet.Rows.Count, "A").End(xlUp).Row

if that doesn't fix it, then you might replace the constant with its actual
value

RowI = oSheet.Cells(oSheet.Rows.Count, "A").End(-4162).Row

You still need to qualify Rows, however, or you could create a ghost
reference to Excel and that might cause problems with closing excel.

 

[David Sisson]

You need to qualify rows I would suspect

RowI = oSheet.Cells(oSheet.Rows.Count, "A").End(xlUp).Row

That was it!

Thanks, Tom!

As always, you're the best!