News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Application-defined or object-defined error excel from access

    Posted on WSAndrewKKWalker Comment on the AskWoody Lounge

    Home Forums AskWoody support Microsoft Office by version Questions: Microsoft Office Application-defined or object-defined error excel from access

    Viewing 6 reply threads
    • Author
      Posts
      • #2047416 Reply
        WSAndrewKKWalker
        AskWoody Lounger

        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?????

      • #2052117 Reply
        WSAndrewKKWalker
        AskWoody Lounger

        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.

      • #2052402 Reply
        RetiredGeek
        AskWoody MVP

        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.
      • #2053088 Reply
        WSAndrewKKWalker
        AskWoody Lounger

        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?

         

      • #2084940 Reply
        RetiredGeek
        AskWoody MVP

        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 2 months, 2 weeks ago by RetiredGeek.
        • #2089129 Reply
          WSAndrewKKWalker
          AskWoody Lounger

          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.

           

          • #2089160 Reply
            RetiredGeek
            AskWoody MVP

            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

      • #2084950 Reply
        WSAndrewKKWalker
        Guest

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

      • #2138283 Reply
        anonymous
        Guest

        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.

        • #2138351 Reply
          anonymous
          Guest

          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.

      • #2089172 Reply
        WSAndrewKKWalker
        Guest

        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.

    Viewing 6 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Application-defined or object-defined error excel from access

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.