Results 1 to 8 of 8
  1. #1
    akkw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    5

    Question 50290 Application-defined or object-defined error unless in debug mode...

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

    It looks like something to do with the opening of the workbook object... but heh...

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,753
    Two things I can think of
    1) if you've had a recent update to Access, these things are known to introduce bugs. If it's real new, there may not be much posted anywhere about it.
    2) If your code really contains those modified quote marks, I have seen them cause issues. Make sure they're " everywhere and not
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    akkw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    5
    Quote Originally Posted by Micron View Post
    Two things I can think of
    1) if you've had a recent update to Access, these things are known to introduce bugs. If it's real new, there may not be much posted anywhere about it.
    2) If your code really contains those modified quote marks, I have seen them cause issues. Make sure they're " everywhere and not
    Those quotes were just how the board placed them.
    The actual quotes in the code are standard.

    The issue appears to be the code fails to recognise the range object if run directly from a command button.
    But as I said, placing a code break before the with statement, but after the workbook open line everythging works fine on continue.

    One could be led to think that the excel object doesn't exist.

    but now even weirder..

    placing this debug.print code before the with statement

    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

    oxlRC.ClearContents

    It appears to assign the correct Range to the Object variable oxlRC

    It also fails if the line is changed to oxlRC = ""

    There is NO protection set on the Workbook, or Worksheets in it.

    Code:
                    Set oxlRC = .Range("infClientID")
                    oxlRC.ClearContents
    I even changed the code from the original to use a range object oxlRC , but it still comes up with the Application Defined Error 50290

    Very very Bizarre.

  4. #4
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,814
    what have you defined oxlRC as?

    also, why assign to a variable before clearing? why not just use

    .Range("infClientID").clearcontents

  5. #5
    akkw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    5
    Quote Originally Posted by Ajax View Post
    what have you defined oxlRC as?

    also, why assign to a variable before clearing? why not just use

    .Range("infClientID").clearcontents
    Well to answer that...

    oxlRC is defined as an Excel.Range

    Why not just use

    .Range("infClientID").clearcontents

    If you read back to the initial post, you will see that was exactly how it was, but it failed on that line (obviously only when run directly).

    So, I assigned the range to a variable to see if it actually got created (since the error is an object defined error)

    And yes it does get assigned , but then fails on the .ClearContents method.

    In fact, it matters not how you try to trigger that method, it fails.

    Even if you use .Range("B2").ClearContents
    And it also fails if you try to assign a value as in .Range("infClientID")=""

    But NOT if you add a break point after the opening of the workbook.

    The fact that this worked just fine on 14th Dec and has now stopped with this error is maybe implying yet another MS update bug.

    If thay can create the Update query bug, they can break anything.

  6. #6
    akkw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    5
    OK....

    This Now looks like a timing issue... that is recently introduced.

    If you add the following line (incidentally it doesn't work if you use MsgBox instead), after the workbook open command and before Set oxlS command

    Code:
               'Open the Template
               Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)
                         
               strPause = InputBox("Workbook Opened" & vbLf & "Press Any Key to Continue", "Processing")
          
               Set oxlS = oxlB.Worksheets("Title")
                
               With oxlS
    Then it runs fine.....

    Who know?
    Still doesn't explain why it worked on 14th Dec, and then stopped last week....

    It's not perfect, but it's a work around!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,753
    That's interesting. I suppose you could go back to what you had and introduce a pause instead to see if that's the reason. Some use Do Events to permit Windows to do processing outside of Access code, some use a Pause function to permit that processing to take place then resume code execution. In case you're interested, here's one that I use but it allows integer or decimal input whereas most you'll find posted just use integer.
    Public Function Pause(interval As Variant)
    Dim Start As Double
    Start = Timer
    Do While Timer < Start + interval
    Loop
    End Function
    For 2 seconds you'd call it like
    Pause 2
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  8. #8
    akkw is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    5
    I had tried a do loop like that.
    And a For Next loop.
    For 2, 5, 10 and 15 secs.

    Strangely, the only thing that worked was the InputBox

    And it has to go after the workbook.open method.

    TBH, I'm only transferring data to Excel, because the charts in Access are so tragic.

    Eventually it will be shifted to SQL server and Net or similar.
    But that will take a while.

    Andrew.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Application Defined Error
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 2
    Last Post: 08-06-2015, 06:31 AM
  2. Application-defined or object-defined error
    By SYFYLADY in forum Access
    Replies: 4
    Last Post: 03-05-2014, 05:25 PM
  3. Replies: 3
    Last Post: 11-12-2013, 04:13 PM
  4. Replies: 1
    Last Post: 03-14-2013, 12:39 PM
  5. Application-defined or object-defined error
    By hawkins in forum Access
    Replies: 6
    Last Post: 07-01-2011, 01:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums