Solved

Run-time error if I output to EXCEL more than once

Posted on 2002-04-29
12
220 Views
Last Modified: 2010-05-02
I have a VB program that starts an EXCEL session, adds data to “Sheet1”, then subtotals column “I”.  The first time I run it, it runs fine.  The second time I run it, it starts an EXCEL session, adds data to “Sheet1” in the 2nd session, and I get this error when it gets to the line of code that does the subtotals:

Run-time error 1004
Method ‘Range’ of object ‘_Global’ failed.

What do I need to do to get this to run multilpe times?  (Following is the code I am running.)

Set objExcel = New Excel.Application
        objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
Set objworksheet = objWorkbook.Worksheets(1)
‘ Column headings
        With objworksheet
          .Cells(1, 1) = "Rec Date"
          .Cells(1, 2) = "Loc"
          .Cells(1, 3) = "Pay Type"
          .Cells(1, 4) = "Payment No"
          .Cells(1, 5) = "Received From"
          .Cells(1, 6) = "Program/Sub Program"
          .Cells(1, 7) = "Sort Code"
          .Cells(1, 8) = "Rec No"
          .Cells(1, 9) = "Amount Distributed"
          .Cells(1, 10) = "Account"
          .Cells(1, 11) = "Treas Rec No"
        End With

        With rsHSReceipting
          .MoveFirst
          introw = 2
          Do While Not .EOF
            objworksheet.Cells(introw, 1) = .Fields("RecDate")
            objworksheet.Cells(introw, 2) = .Fields("RecLocation")
            objworksheet.Cells(introw, 3) = .Fields("TypePayment")
            objworksheet.Cells(introw, 4) = .Fields("PayNo")
            objworksheet.Cells(introw, 5) = .Fields("RecFrom")
            objworksheet.Cells(introw, 6) = .Fields("ProgramCd")
            objworksheet.Cells(introw, 7) = .Fields("SortCd")
            objworksheet.Cells(introw, 8) = .Fields("RRecNo")
            varAmount = .Fields("AmtDisburse")
            objworksheet.Cells(introw, 9) = varAmount
            introw = introw + 1
            .MoveNext
          Loop

‘ THIS IS THE LINE THAT I GET THE ERROR ON
        Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        End With

    rsHSReceipting.Close
    frmSplash.Visible = True
    Unload Me
    Set objworksheet = Nothing
    Set objWorkbook = Nothing
    Set objExcel = Nothing
0
Comment
Question by:jhartski
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
try to use :

objExcel.Selection
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
plus you maybe need to activate your new objworksheet
like
objworksheet.Activate

plus, sometimes you need to use range("A1:B12").select
to select data
0
 

Author Comment

by:jhartski
Comment Utility
Where exactly does that code go?  I've tried it in a couple of places, and I still get the error.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
listening
0
 
LVL 3

Expert Comment

by:n_narayanan
Comment Utility
Hi

change this from
Set objworksheet = objWorkbook.Worksheets(1)
to
Set objworksheet = objWorkbook.Worksheets("sheet1")

Now it will work.

Note: If you have any specific name in your XLS file Sheet1 then give that.

Narayanan.


0
 
LVL 3

Expert Comment

by:n_narayanan
Comment Utility
The method objworkbook.Worksheets(1) will work for the first time. Since the reference is not closed util end of your program you are getting this error.

If you hardcode your sheet name there, then this problem will be solved.

Cheers

Narayanan
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jhartski
Comment Utility
n narayanan

I did as you said, but I still get the error message:
Run-time error 1004
Method ‘Range’ of object ‘_Global’ failed.

Any other suggestions?
0
 
LVL 3

Accepted Solution

by:
n_narayanan earned 50 total points
Comment Utility
I am sending you the MSDN Article on the same error.

PRB: Excel Automation Fails Second Time Code Runs
 
Q178510
 

--------------------------------------------------------------------------------
The information in this article applies to:
 
Microsoft Visual Basic Learning, Professional, and Enterprise Editions for Windows, versions 5.0, 6.0
Microsoft Visual Basic Standard, Professional, and Enterprise Editions for Windows, version 4.0
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Visual Basic for Applications version 5.0
Microsoft Office 2000 Developer
Microsoft Excel 2000
 
--------------------------------------------------------------------------------
 

SYMPTOMS
While running code that uses Automation to control Microsoft Excel, one of the following errors may occur:
 
With Microsoft Excel 97 or Excel 2000, you receive the error:
 
Run-time error '1004':
Method '<name of method>' of object '_Global' failed
-or-
Application-defined or object-defined error
With Microsoft Excel 95, you receive the error:
Run-time error '-2147023174'
OLE Automation error
-or-
Run-time error '462':
The remote server machine does not exist or is unavailable.
 
 
 
CAUSE
Visual Basic has established a reference to Excel due to a line of code that calls an Excel object, method, or property without qualifying it with an Excel object variable. Visual Basic does not release this reference until you end the program. This errant reference interferes with automation code when the code is run more than once.
 
 
 
RESOLUTION
Modify the code so that each call to an Excel object, method, or property is qualified with the appropriate object variable.
 
 
 
STATUS
This behavior is by design.
 
 
 
MORE INFORMATION
To automate Microsoft Excel, you establish an object variable that usually refers to the Excel Application or Workbook object. Other object variables can then be set to refer to a Worksheet, a Range, or other objects in the Microsoft Excel object model. When you write code to use an Excel object, method, or property, you should always precede the call with the appropriate object variable. If you do not, Visual Basic establishes its own reference to Excel. This reference might cause problems when trying to run the automation code multiple times. Note that even if the line of code begins with the object variable, there may be a call to an Excel object, method, or property in the middle of the line of code that is not preceded with an object variable.
 
The following steps illustrate how to reproduce this problem, and how to correct it.
 
Steps to Reproduce Behavior
Start a new Standard EXE project in Visual Basic. Form1 is created by default.
 

Click References from the Project menu and check the Object Library for the version of Excel you intend to automate.
 

Place a CommandButton on Form1.
 

Copy the following code to the Code Window of Form1:
 
 
 
      Option Explicit
 
      Private Sub Command1_Click()
         Dim xlApp As Excel.Application
         Dim xlBook As Excel.Workbook
         Dim xlSheet As Excel.Worksheet
         Set xlApp = CreateObject("Excel.Application")
         Set xlBook = xlApp.Workbooks.Add
         Set xlSheet = xlBook.Worksheets("Sheet1")
         xlSheet.Range(Cells(1, 1), Cells(10, 2)).Value = "Hello"
         xlBook.Saved = True
         Set xlSheet = Nothing
         Set xlBook = Nothing
         xlApp.Quit
         Set xlApp = Nothing
      End Sub
On the Run menu, click Start or press the F5 key to start the program.
 

Click the CommandButton. No error occurs. However, a reference to Excel has been created and has not been released.
 

Click the CommandButton again and note that you receive one of the errors previously described.
 
NOTE: The error occurs because the code refers to the Cell's method without preceding the call with the xlSheet object variable.
 

Stop the project and change the following line:
 
 
 
xlSheet.Range(Cells(1,1),Cells(10,2)).Value = "Hello"
to:
 
xlSheet.Range(xlSheet.Cells(1,1),xlSheet.Cells(10,2)).Value = "Hello"
Run the program again. Note that you can run the code multiple times without error.
 
 
 
 
 
REFERENCES
For additional information, please see the following articles in the Microsoft Knowledge Base:
 
 
 
Q167223 Microsoft Office 97 Automation Help File Available on MSL
 
Q189618 PRB: Automation Error Calling Unqualified Method or Property
 
Additional query words: activex automation OLE kbinterop kbAutomation kbVBp kbVBp400 kbVBp500 kbVBp600
 
Keywords : kbGrpDSO kbOffice2000 kbExcel97 kbVBA500 kbexcel2000
Issue type : kbprb
Technology :
 

Last Reviewed: October 21, 2000
) 2001 Microsoft Corporation. All rights reserved. Terms of Use.
 
 
 
 
--------------------------------------------------------------------------------
Send feedback to MSDN.Look here for MSDN Online resources.
0
 
LVL 3

Expert Comment

by:n_narayanan
Comment Utility
Also Check your last lines

rsHSReceipting.Close
   frmSplash.Visible = True
   Unload Me
   Set objworksheet = Nothing
   Set objWorkbook = Nothing
   Set objExcel = Nothing


move the unload me to the last line like below.

   rsHSReceipting.Close
   frmSplash.Visible = True
   Set objworksheet = Nothing
   Set objWorkbook = Nothing
   Set objExcel = Nothing
   unload me
0
 

Author Comment

by:jhartski
Comment Utility
By using the code from the article, I was able to get it to work.

Moving the "unload me" command didn't help.
0
 
LVL 5

Expert Comment

by:rkot2000
Comment Utility
p.s

I had the same solution :
>>>
try to use :

objExcel.Selection
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Good information.

mlmcc
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now