Solved

Excel 97 Instance Problem

Posted on 2003-12-08
17
300 Views
Last Modified: 2010-05-01
Hi Experts
      I have an Application , which has one module for Excel Output ,
Requirement is as follows
1) After Generating Excel output , User should direcly able to see Excel Window ,
2) The File user is viewing is in Excel must be Temporary file  (i.e.  user has option to save or not to save on disk )
3) VB 6 and Excel 97 is used here


I am using following code

   Dim objExcel As Excel.Application
   Dim objExcelWk  As Excel.Workbook

    ExcelRunning = IsExcelRunning()
    If Not ExcelRunning Then
        Set objExcel = CreateObject("Excel.Application")

    Else
        Set objExcel = GetObject(, "Excel.Application")
    End If

    objExcel.UserControl = True

   ''-------------  MY business logic ------------""

   Set objExcelWk = Nothing
   Set objExcel = Nothing

My Problems are as follws
1)  When user does not save generated outout and directly close the application . Excel.exe is still remains in process (Task manager)
2) When User saves File on Desktop , Close the Excel Application . Go to Desktop and opens last Saved file , It opens Excel program and file But Data part is not visible ( i.e. no worksheet  displayed , and to user it seems Excel application hang )

Please help me to solve this problem


   
0
Comment
Question by:seth_imran
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9901424
Duplicate in http://oldlook.experts-exchange.com/Applications/MS_Office/Excel/Q_20820205.html


Sub test()
Dim objExcel As Excel.Application
   Dim objExcelWk  As Excel.Workbook

    ExcelRunning = IsExcelRunning()
    If Not ExcelRunning Then
        Set objExcel = CreateObject("Excel.Application")

    Else
        Set objExcel = GetObject(, "Excel.Application")
    End If

    objExcel.UserControl = True
    objExcel.Visible = True

   ''-------------  MY business logic ------------""

   Set objExcelWk = Nothing
   Set objExcel = Nothing
   objExcel.Quit  
End Sub


Cheers
Dave
0
 

Author Comment

by:seth_imran
ID: 9901487
thanks dave
When Tried as  per ur suggestion
Set objExcel = Nothing
   objExcel.Quit  

it gives me error

"The object variable or the With block variable is not set. "

Then I tried
   objExcel.Quit  
Set objExcel = Nothing

Problem Is still persists

Also , it is not going as per my requirement number 2

Regards
Imran


0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9901509
Hi Imran

I tried this cutdown version of your code

It made the instance visible then it closed it, I copied an error with the Nothing & Quit approach above

Cheers

Dave

Sub test()
Dim objExcel As Excel.Application
   Dim objExcelWk  As Excel.Workbook
 
        Set objExcel = CreateObject("Excel.Application")

    objExcel.UserControl = True
    objExcel.Visible = True

   ''-------------  MY business logic ------------""

   Set objExcelWk = Nothing
      objExcel.Quit
End Sub
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:seth_imran
ID: 9901892
Hi Dave
 thanks for your quick response

    I have same line "    objExcel.Visible = True" at end of my code !!! as shown below

Also Problem is same , if I do Quit this was Excel.exe is still remains in process (Task manager)



     
Sub test()
Dim objExcel As Excel.Application
   Dim objExcelWk  As Excel.Workbook
 
        Set objExcel = CreateObject("Excel.Application")

    objExcel.UserControl = True

   ''-------------  MY business logic ------------""


    objExcel.Visible = True

   Set objExcelWk = Nothing
      objExcel.Quit
End Sub
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9901900
I've tested this on Excel 2000 Imran so my platform is not identical to yours - you are running this code directly from Excel 97?

If so then I am surprised that the instance stays open, I will try to test later tonight when I have access to a PC with 97

0
 

Author Comment

by:seth_imran
ID: 9901916
Hi Dave
  yes we have problem with Excel 97 only , On excel 2000  the instance is closed  with my  code , and it seems no problem .
     but since  my product is going to work with EXCEL 97 only ( I have no conrol here , it`s my client`s choice ) I have to fix the problem


Regards
Imran



0
 
LVL 17

Accepted Solution

by:
Suat Ozgur earned 500 total points
ID: 9902981
Hi Imran,
Hi Dave,

Imran,

Your project uses early binding (means you added Excel Object Library Reference, so declaring objects by using Excel library) but you also want to use CreateObject. Also, you should first quit application, then set the object variables as Nothing. Please try following code instead.

'-----Code Start-----
Sub test()
Dim objExcel As Excel.Application
  Dim objExcelWk  As Excel.Workbook

  Set objExcel = Excel.Application

   objExcel.UserControl = True
   objExcel.Visible = True

  ''-------------  MY business logic ------------""

  objExcel.Quit  
  Set objExcelWk = Nothing
  Set objExcel = Nothing
End Sub
'----Code End-----

Since we cannot see the middle part, I can say this changes should solve the current problem.
Please let us know. I'll also suggest late binding if this still doesn't work how you need.

Suat
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9903182
Suat,

ROFL - I cant believe I missed the early binding after our discussion the other day!

I jumped straight to the Quit and Visible solutions without seeing the double new instances

Cheers

Dave
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 9903202
Hi Dave,

I don't know if it would solve the trouble but I think it should.  I hope it does.

<off topic>
Dave- I saw your message today but has to leave home immediately so couldn't reply, sorry. You can access my tnn address (which is in profile) during day (at least for a week, because I might quit that host). Sometimes my PC downloads the message before I leave home so I cannot get the messages at office. So I reply when I get home. Now tnn is ok to reach me.
</off topic>

Suat
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9903209
/off topic

Hi Suat,

What email address is best for you?

Cheers

Dave
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 9903226
<off topic>
Dave- I do check "smozgur AT tnn.net" during day currently.
What do you mean "best" ?
Suat
</off topic>
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9903373
/still offtopic

Which one do you read the most?

Can you email at my hotmail address
0
 

Author Comment

by:seth_imran
ID: 9909340
Hi Dave
Hi Suat
     Suat`s Solution is not solving my problem !!! I have tried same code !!! Still Instance of application is not dying
 Suat , Here If I Elaborate Requirement in terms of binding , I can explain it like this

 My Application Should start Instance of Excel , Put Data from grid to excel then FORGET about Excel.
i.e. After Putting Data from grid to Excel WorkSheet There Must not be any relation between my application and Excel  
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9917276
Sorry, Imran I've missed this one. Will try this again when I have access to Excel 97 later today. I'll send Suat a reminder too

Cheers

Dave
0
 
LVL 17

Expert Comment

by:Suat Ozgur
ID: 9929409
Imran,

Descriptions and request are not clear (for me). You are trying to remove Excel instance, which means your application needs to QUIT Excel.

However the new comment:

>After Putting Data from grid to Excel WorkSheet There Must not be any relation between my application and Excel  

and earlier request from your original question:

>1) After Generating Excel output , User should direcly able to see Excel Window ,

makes me confused.

So, if you want NO relation between Excel and your application after putting data, #1 above means to me you DON'T want to quit Excel.

Then you only need the following. You don't need to use Quit method.

'-----Code Start-----
Sub test()
Dim objExcel As Excel.Application
 Dim objExcelWk  As Excel.Workbook

 Set objExcel = Excel.Application

  objExcel.UserControl = True
  objExcel.Visible = True

 ''-------------  MY business logic ------------""

 Set objExcelWk = Nothing
 Set objExcel = Nothing
End Sub
'----Code End-----

I already mentioned that I am confused.

Suat
0
 
LVL 27

Expert Comment

by:planocz
ID: 10217966
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
No response from seth_imran from 12/12/2003 comment
Award points to smozgur is recommend.
Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

planocz
EE Cleanup Volunteer
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…

765 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