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

    This topic contains 8 replies, has 3 voices, and was last updated by  WSAndrewKKWalker 3 days, 14 hours ago.

    • 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 1 week, 5 days 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

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

    • #2089172 Reply

      WSAndrewKKWalker

      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.

    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.