Solved

Excel 97 Instance Problem

Posted on 2003-12-08
17
294 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
 

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:
smozgur 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 17

Expert Comment

by:smozgur
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:smozgur
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:smozgur
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

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!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 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

16 Experts available now in Live!

Get 1:1 Help Now