Excel Hangs - Ideas?

[RoguePoet01]

Hi,

I've got a program that runs in the background and writes two XL spreadsheets then e-mails them to people.

The problem is that after the program runs, it hangs up XL so that when one of the e-mailed Excel files is opened from Outlook, you can't see the file.

I've made sure to close the application and "Set XLApp = Nothing", but it still keeps getting hung up.

Any ideas?

 

[Nunina]

Hi!

I am having the exact same problem. I save data into an Excel file, and when trying to read the information, Excel doesn't even make the effort to open the file!

If any of you guys know how to solve this issue, please let me know...

Thanks again, guys!

Nunina

 

[vladk]

Hi

Set to Nothing ALL the Excel related objects you use in the application.

You can identify the persisting objects by commenting them out one by one. Run, close and check if EXCEL is still in the list of the running processes in Windows Task Manager panel. Check for processes, not applications.

Vladk

 

[RoguePoet01]

Vladk,

I appreciate the note, but that's still not helping.

I must be doing something else wrong.

 

[vladk]

RoguePoet01,

Do you Quit Excel?

Vladk

 

[RoguePoet01]

Yes, I quit Excel and then I set it to nothing.

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' CLOSE XL
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub CloseXL()

On Error Resume Next
XLApp.ActiveWorkbook.Close savechanges:=False
XLApp.Quit
Set XLApp = Nothing

End Sub

I'm sometimes opening two workbooks, though.

The program needs to run two reports on Monday (a weekly and a daily) and one report for every other day. But sometimes there's no data in the reports because the lab is behind.

I think that somehow I'm opening two instances of Excel, but end up closing only one.

Thanks for your input.

 

[vladk]

I see, you use on Error Resume next.
This is the sure way to hide errors.

 

[RoguePoet01]

Well said.

I've noticed that Excel still runs after I close it.

Sometimes I opent it as a special file, a comma delimited file.

I also have to open a file and save it as *.dei, because that is what the home office wants.

I use the "Open file #1 as..." (I'm at home now, so I can't remember exactly). "Close #1"

But that is where something goes wrong.

 

[ToshTrent]

I'm having the same problem with my application, it doesn't even have to access excel for the problem to happen. Once my program has been run then closed any network excel documents take a century to open. Would this be due to the ADO connection?


[red]
Thankyou[/red]
Matt

 

[roccorocks]

One question I have is whether you are using Early or Late binding. I dont every quit the app object, I close the workbook object.

For a possible solution try this,

For a brandnew workbook:
wrkexcel.close true, path

For an existing workbook:
wrkexcel.save
wrkexcel.close

set appexcel = nothing
set wrkexcel = nothing
set shtexcel = nothing

Just so you know I am using early-binding (ie dim appexcel as excel.application). I am not sure how much it would modify using late-binding.

thanks,

Rocco

 

[Nunina]

Guys,

I have tried everything you've said here, and still can't get my workbook to open. I even restarted the machine, and tried to open the workbook by itself, and still nothing.

I thought that maybe it wasn't saving the information, but it turn out the workbook is about 522k, so that's not it.

Could it be a VB bug?

Nunina

 

[roccorocks]

Could you post your Excel code, I would like to see it. Also, are you saying you can't open the sheet manually? Or are you saying in code "ALONE" you can't open the sheet. If you can't open it manually, then it sounds as though you might need to reinstall excel (or the dependency files for excel).

Rocco

 

[dvannoy]

I have this same issue..Im running Office97. I have found that as long as Excel stays open after the first report get exported the rest export fine. But if I close excel and then try to export so more data, thats when excel opens but you cannot get to the file. I am also hung up on this as well. It makes me feel better knowing other people have this same issue and it's not just me. I am going to try using Office 2000 and see if that makes a difference.

 

[ToshTrent]

Na office 2000 does it too.

We are using Office 2000 in work, and it seems to hang, are you using the files over a network drive or local disk?

 

[dvannoy]

network drive..I dont think the drive really has anything to do with it.I really think it's a combo of excel and VB some kind of bug there.

 

[ToshTrent]

blarst, i'll look into and post l8r

 

[AGIMA]

Dont test Office XP as it also does it. It also does it with .NET... Bloody Excel.



AGIMA - professional web hosting is our business.

AGIMA Computing

http://www.agima.com.au

 

[ToshTrent]

strangly enough though, when copying the file to the local disk, excel opens the workbook like a treat, might have to start copying the files to a temp directory on the pc, for the short time, until we discover the problem.

[red]
Thankyou[/red]
Matt

 

[combo]

Do you have excel an active application? If not, there is no active workbook. If you have only one workbook, it is safer to use:
[tt]XLApp.Workbooks(1).Close savechanges:=False[/tt]
(or work with variable of excel.workbook type).


combo

 

[RoguePoet01]

FYI for Everyone: Found the Problem.

I was deleting a series of rows at the end of the worksheet just before saving the file and closing it out.

Some kind of error was occurring as a result and Excel would close, but the instance would remain in memory, which lead to other problems.

I decided to select a "printrange" instead of deleting the rows, and that solved the problem.

In essense: the problem was within a formatting (row deletion) procedure within Excel itself.