Macro Errors (1 Viewer)

StuartG

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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.
 

Attachments

  • DB Error Code.jpg
    DB Error Code.jpg
    36.2 KB · Views: 129

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:36
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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

Enthusiastic Amateur
Local time
Today, 07:36
Joined
Sep 21, 2011
Messages
14,050
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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

Wino Moderator
Staff member
Local time
Today, 00:36
Joined
Aug 30, 2003
Messages
36,118
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

Enthusiastic Amateur
Local time
Today, 07:36
Joined
Sep 21, 2011
Messages
14,050
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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?
 

Attachments

  • Module - modArnelgp.txt
    2.8 KB · Views: 128
  • VBA - Event - On Current.txt
    17.3 KB · Views: 125

Gasman

Enthusiastic Amateur
Local time
Today, 07:36
Joined
Sep 21, 2011
Messages
14,050
Nothing jumps out to me?:(
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:36
Joined
Aug 30, 2003
Messages
36,118
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:36
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:36
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:36
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 07:36
Joined
Sep 12, 2018
Messages
125
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.
 

Attachments

  • 1.1.JPG
    1.1.JPG
    18 KB · Views: 120
  • 1.2.JPG
    1.2.JPG
    35.8 KB · Views: 112

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:36
Joined
May 7, 2009
Messages
19,169
the second one has already xlApp.Quit, so you insert it on your first image.
insert a line before "Set xlApp = Nothing".
 

Users who are viewing this thread

Top Bottom