[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

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


   
0
seth_imran
Asked:
seth_imran
  • 7
  • 4
  • 4
  • +1
1 Solution
 
Dave BrettCommented:
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
 
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


0
 
Dave BrettCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
0
 
Dave BrettCommented:
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
 
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



0
 
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
0
 
Dave BrettCommented:
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
 
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
0
 
Dave BrettCommented:
/off topic

Hi Suat,

What email address is best for you?

Cheers

Dave
0
 
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>
0
 
Dave BrettCommented:
/still offtopic

Which one do you read the most?

Can you email at my hotmail address
0
 
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  
0
 
Dave BrettCommented:
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
 
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
0
 
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now