Application-defined or object-defined error excel from access

Topic

New Reply

This was working last week, and now fails.
Office 365 Click to Run
MSO (16.0.12325.20280) 64 Bit  on Windows 10 (Latest Version Update)

All file names and locations are valid

All variables are defined

The code is running from inside MS Access and opens an Excel Workbook

Below is the part that fails in italic

The variables holding file name and path are ok

On Error Goto UpdateExcelError

Set oXL = New Excel.Application
Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)
Set oxlS = oxlB.Worksheets(“Title”)
With oxlS
.Range(“infClientID”).ClearContents
.Range(“infClient”).ClearContents

‘etc

End With

The worksheet exists, and the Range exists.

So, now the weird bit.

This is run off  a command button, and fails on the italic line with an application-defined object error.
BUT…..
If you put a break point on the line
Set oxlS = oxlB.Worksheets(“Title”) in debug mode.
Then press continue execution everything works fine.
With no break point it always errors on the line

.Range(“infClientID”).ClearContents

with
Error
50290
Application-defined or object-defined error

It’s either something I have stupidly missed, or a bug.
It has been driving me nuts all day.

Anyone see what is going on?????

Reply | Quote

Replies

The issue appears to be the code fails to recognise the range object if run directly from a command button when asked to clear or change any data on any sheet.
But as I said, placing a code break before the first WITH statement, but after the workbook open line everything works fine on pressing continue.

To test if the object actually exists I added some debug.print lines

placing this debug.print code before the with statement

<pre class=”bbcode_code”> ‘Open the Template
Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)
Set oxlS = oxlB.Worksheets(“Title”)
Debug.Print “oxl = ” & oXL.Name
Debug.Print “oxlB = ” & oxlB.Name
Debug.Print “oxlS = ” & oxlS.Name
For Each oxlName In oxlB.Names
If Left(oxlName.Name, 3) = “inf” Then
Debug.Print oxlName.Name & ” References: ” & oxlName.RefersTo
End If
Next
DoEvents
With oxlS
‘Clear
‘On Error Resume Next
Set oxlRC = .Range(“infClientID”)
oxlRC.ClearContents
Set oxlRC = .Range(“infClient”)
oxlRC.ClearContents

Generates this output.

oxl = Microsoft Excel
oxlB = IPM3rdPartyExpensesSummaries117.xlsm
oxlS = Title
infAssignmentType References: =Title!$C$11
infBookMark References: =Title!$A$2
infClient References: =Title!$C$7
infClientID References: =Title!$B$7
infCurrency References: =Title!$B$9
infCurrencyName References: =Title!$C$9
infDates References: =Title!$C$8
infFor References: =Title!$C$10
infHomeCountry References: =Title!$C$13
infHostCountry References: =Title!$C$14
infReports References: =Title!$G$5
infReportSelection References: =Title!$A$3:$C$24
infServices References: =Title!$C$16:$C$38
infServicesStart References: =Title!$C$16
infSupplier References: =Title!$C$12
infUpdated References: =Title!$C$5

So, it recognises the Excel Application Object, and the workbook object, and the range objects.
BUT, unless there is a breakpoint in the code, it crashes out at the line

<b>oxlRC.ClearContents</b>

It appears to assign the correct Range to the Object variable <b>oxlRC</b>

It also fails if the line is changed to <b>oxlRC = “”</b>

<i>There is NO protection set on the Workbook, or Worksheets in it.</i>
And the error only appears to be when changing data, not referencing the objects.

<pre class=”bbcode_code”> Set oxlRC = .Range(“infClientID”) ‘Works OK
oxlRC.ClearContents ‘Fails with the error

I even changed the code from the original to use a range object <b>oxlRC </b>, but it still comes up with the <b>Application Defined Error 50290</b>

Very very Bizarre.

Reply | Quote

Andrew,

I’ve not seen this before but it may be a timing issue.

Instead of a break point try adding this code:

 Dim x As String
 x = InputBox(“Press any key to continue”, “Test”)

in it’s place. If the code runs after you press enter it’s a timing issue that was somehow introduced, maybe an update to excel? You can look in the Windows Update settings to see what updates were installed and when and if you find one for Excel you might try uninstalling it.
HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

WSAndrewKKWalker

Reply | Quote

RetiredGeek wrote:

Andrew,

I’ve not seen this before but it may be a timing issue.

Instead of a break point try adding this code:

 Dim x As String
 x = InputBox(“Press any key to continue”, “Test”)

in it’s place. If the code runs after you press enter it’s a timing issue that was somehow introduced, maybe an update to excel? You can look in the Windows Update settings to see what updates were installed and when and if you find one for Excel you might try uninstalling it.
HTH

Good shout!

Here’s still weird(ish) as code below

Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)

strPause = InputBox(“Workbook Opened” & vbLf & “Do you want to Proceed”, “Continue?”, “Yes”)
If strPause <> “Yes” Then
       oxlb.close SaveChanges:=False
       oxl.Quit 
Exit Sub

Set oxlS = oxlB.Worksheets(“Title”)

Using the above , it executes correctly.

 

If I use

intPause = MsgBox(“Workbook Opened” & vbLf & “Do you want to proceed”, vbYesNo + vbQuestion + vbDefaultButton1, “Continue…”)
if intPause = vbNo 
  ‘etc

It still fails….
I suspect MsgBox may pause all processing, whilst InputBox doesn’t.

It’s a work around for now… Cheers…

I wonder why there is now a timing issue, that didn’t exist before 14th Dec?

 

Reply | Quote

[RetiredGeek]

Andrew,

Now that you know it’s a timing thing you can try this to eliminate the prompt.

Application.Wait (Now + TimeValue("0:00:10"))

Replace the InputBox with this code which will pause execution for 10 seconds to give the Workbook time to fully open.
You can then adjust the time up/down to fit the operation speed of your machine and eliminate the prompt and user interaction.
HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

• This reply was modified 5 years, 4 months ago by RetiredGeek.

Reply | Quote

Unfortunately that doesn’t work.
Been doing a different project, so only just got back to this.

Application.Wait is not available in MS ACCESS, at least not in 365 Click to Run

So you have to call it from Excel via the Excel Object

oXL.Application.Wait (Now + TimeValue(“00:00:10”))

But for some reason you then hit the same old object lag time runtime issue as before.

Looks to me at the moment, that the only solution is to use the Inputbox Pause.

Strangely, on a local PC, this only gets triggered once.

When I ran it on a Cloud based file and database via the client, it actually triggered the need to put the same pause into the error trap.
I did add a counter in that to make sure it didn’t loop for ever.
So far, it has never needed more than 3

This appears to be either a local bug, or maybe a new bug in Access.

I may dig out an old laptop running Office 2010 and see if it was an issue back then.

 

Reply | Quote

Andrew,

Ok, you can try using a do nothing loop in place of the Wait function:

 For x=1 to 20000
  Y=Y+1
Next x

Play with the 20000 until the timing is right.
HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

<!–more–>Thanks. I’ll give that a go, and let you know how it works.

Reply | Quote

Hi Andrew,

I am having the exact same problem with the Range.ClearContents command, and it is driving me insane. It also started around mid December 2019.
I run the code from a command button in a userform, and once I reach the .ClearContent line, the userform suddenly disappears and Excel freezes. I then have to reboot Excel every time. If I run in Debug mode, it usually works though.
I tried adding a 1 second pause before each .ClearContents call (as suggested by RetiredGeek), and this seems to work. Also disabling ScreenUpdates seems to work.

Have you found the root cause and a more durable solution? I need my code to run as quickly as possible and do not wish to have pauses all over the code.

Reply | Quote

Sadly, no solution.

The fact that it all began in December, implies another Microsoft Update mess, like the dreadful Update Query chaos from earlier in the year.
What would be great, would be if updates did more good than damage.

Reply | Quote

I’d tried that 1st, before I originally posted, and a Do Events, and a Do Loop that used Time. Even  Messagebox function.

The only thing that currently works is the InputBox..

 

The whole lot is moving to SQL server and a Web interface shortly, well, in the next 8 months, so this is a work around.

Reply | Quote