Excel 97 Instance Problem

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


   
seth_imranAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DaveCommented:
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
seth_imranAuthor Commented:
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


DaveCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

seth_imranAuthor Commented:
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
DaveCommented:
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

seth_imranAuthor Commented:
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



Suat OzgurWeb / Application DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DaveCommented:
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
Suat OzgurWeb / Application DeveloperCommented:
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
DaveCommented:
/off topic

Hi Suat,

What email address is best for you?

Cheers

Dave
Suat OzgurWeb / Application DeveloperCommented:
<off topic>
Dave- I do check "smozgur AT tnn.net" during day currently.
What do you mean "best" ?
Suat
</off topic>
DaveCommented:
/still offtopic

Which one do you read the most?

Can you email at my hotmail address
seth_imranAuthor Commented:
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  
DaveCommented:
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
Suat OzgurWeb / Application DeveloperCommented:
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
planoczCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.