Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when WB already open
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(GlWorkB ookName) 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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when WB already open
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. -- Regards, Tom Ogilvy "David Sisson" wrote: 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(GlWorkB ookName) 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error when WB already open
On May 4, 10:01 am, Tom Ogilvy
wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error message on open | Excel Discussion (Misc queries) | |||
Open Workbook error | Excel Discussion (Misc queries) | |||
EXCEL:Can't open any file without error saying it's ALREADY open??? | Excel Worksheet Functions | |||
Open window - on error | Excel Programming | |||
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming |