Macro Errors

[StuartG]

Hi all,
Sorry it has been a while since I posted, i trust you are all well?

I'm writing today today as i'm starting to experience a few issues to which i am unsure if it is Office 365 related or the code, The code was written for me by someone in this group (Arnelgp) to which I am really grateful for but this will mean that I don't understand at all or how to fix the issue.

Can you please confirm what you would need to know in order to help me diagnose/rectify the issue or provide assistance on how I can fix it.

I have attached to this post the error that I keep receiving, to which is happening quite a bit now, and users of by db are starting to lose faith in it.

 

[pbaldy]

Seeing the code may help. I assume nobody else has the file open? When you get it, look in Task Manager and see if there's a ghost Excel instance running. If, do you get the error after killing that process?

 

[StuartG]

Seeing the code may help. I assume nobody else has the file open? When you get it, look in Task Manager and see if there's a ghost Excel instance running. If, do you get the error after killing that process?

Hi pbaldy,
Thanks for the reply.
The code is quite extensive, which is the best way to get this into the forum, notepad etc.

Its funny you should mention the "Ghost" instance of excel as I have seen this a couple of times on users PCs, though it seems to be making itself more obvious now, is it a trait of excel/office 365?

 

[Gasman]

Are you closing the Excel object after use?
Is that file unique to a particular record?

Please use code tags icon # if you are going to post that extensive code.

 

[StuartG]

Are you closing the Excel object after use?
Is that file unique to a particular record?

Please use code tags icon # if you are going to post that extensive code.

Hi Gasman
When the user has finished creating the document they click "Close and Delete record"

The form in the DB is created from a particular field.
Would it help if I uploaded my DB with a few records in so you can see?

 

[pbaldy]

Hi pbaldy,
Thanks for the reply.
The code is quite extensive, which is the best way to get this into the forum, notepad etc.

Its funny you should mention the "Ghost" instance of excel as I have seen this a couple of times on users PCs, though it seems to be making itself more obvious now, is it a trait of excel/office 365?

If it's too long to include in a post within code tags, then a notepad file would be fine.

I don't think it's a trait of Excel, but it can happen with code that isn't written properly.

 

[Gasman]

That would help everyone. I sometimes are unable to open later DBs after 2007, but you'll have plenty who can.

I was just thinking that you had not closed the Excel object.?
I've had that when my code has failed in testing.

I am assuming that all the options shown in the error message have been checked.?

Hi Gasman
When the user has finished creating the document they click "Close and Delete record"

The form in the DB is created from a particular field.
Would it help if I uploaded my DB with a few records in so you can see?

 

[StuartG]

If it's too long to include in a post within code tags, then a notepad file would be fine.

I don't think it's a trait of Excel, but it can happen with code that isn't written properly.

Here is the codes.
I have named the files to relevance of the DB.

Hopefully this should help?

 

[Gasman]

Nothing jumps out to me?

 

[pbaldy]

I'm on the road, but did you check whether you would still get the error after closing out the ghost Excel process? That may help determine if the code is causing it.

 

[arnelgp]

the last part of the error msg says that the worksheet is already open.
try closing all instance of excel. even Kill it in task manager.

 

[StuartG]

Hi,

Thanks for the replies.

I had attempted to replicate the issue and it appears it is the "Ghost" application is running.

Can this be resolved as I would have thought that when the user closes the form and deletes the record, the Ghost application should close itself, or is this a trait of Microsoft?

 

[arnelgp]

close xl automation in this code (VBA-event on current):

CreateExcel_Exit:
SysCmd acSysCmdClearStatus
Set xlWB1 = Nothing
Set xlWB2 = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

 

[StuartG]

close xl automation in this code (VBA-event on current):

CreateExcel_Exit:
SysCmd acSysCmdClearStatus
Set xlWB1 = Nothing
Set xlWB2 = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

where should i put this is the code, in the Mod or On event?

 

[arnelgp]

open your VBA.
well, use the Search utility (Ctrl-F).
on the Option box, select Current Project.
then type this to search: Set xlApp = Nothing
then insert it above it.

 

[StuartG]

open your VBA.
well, use the Search utility (Ctrl-F).
on the Option box, select Current Project.
then type this to search: Set xlApp = Nothing
then insert it above it.

Which area in the code though?
Attached is the area where the current project was.

 

[arnelgp]

the second one has already xlApp.Quit, so you insert it on your first image.
insert a line before "Set xlApp = Nothing".