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 =
Set oSheet = oWB.Worksheets(GlWorkBookName)

'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,

'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
RowI = oSheet.Cells(Rows.Count, "A").End(xlUp).Row

This last line is where the error always occurs.

Thanks in adance

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?


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

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!

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question