Originally Posted by
Micron
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.