Why do I need to delclare the sort order syntax for an excel spreadsheet from access 2013 to excel?

Could you please share the code you are having trouble with, and the specific error you are getting (screen capture would be great).

---

»bp

Yes, it's the xIAscending that is giving the trouble which is part of this entire line:
oSheet.Range("A1:E" & LastRow).Sort Key1:=oSheet.Range("E:E"), Order1:=xIAscending, HEADER:=xlYes

try check the References in Visual Basic Editor and make sure you got include Microsoft Excel Object Library there?

alternatively, you need to define a Constant or variable for xIAscending, which is equal to 1.

XlSortOrder Enumeration (Excel)
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xlsortorder-enumeration-excel?f=255&MSPPError=-2147217396

If I need to define it, then why didn't I have to do that in Access 2010?

You probably had a reference to the Microsoft Excel Object Library in the VBA project before, and don't now.  That would explain what you are seeing.  When you have a reference to the library (early binding) the related data types and constants are available in Access.  If you don't (late binding) then they are not and you have to use Object data types for certain things and define any constants yourself.

If you google VBA and "early binding" "late binding" you will find more info, but here is a pretty simple explanation...

• VBA references and early binding vs late binding | Excel Matters

---

»bp

I currently have the reference for MS Excel Object Library selected, but it hasn't changed anything.  Still receive the same error.

Odd...

Can you show the rest of your code?

And can you post a screen capture of the top of the References dialog on the computer where the error is occurring?

---

»bp

If I need to define it, then why didn't I have to do that in Access 2010?

you need to... add Microsoft Excel Object Library into your Access 2010 project. It's not there by default.

I have attached a copy of the references and here's the last line of my code: oSheet.Range("A1:E" & LastRow).Sort Key1:=oSheet.Range("E:E"), Order1:=xIAscending, HEADER:=xlYes.  Thank you!

Nothing attached...

If you define xlAscending, do you then get an error on xlYes, I would expect you would but I don't think you mentioned that before...

---

»bp

References attached.  When I compile the DB it stops at the xIAscending so I am not sure if HEADER:=xlYes throws an error or not.
DB-References.jpg

The Dim statement is Dim oSheet As Object.   I will give the new module a try it and let you know how that goes. I am kind of multi-tasking at the moment so it may take a few minutes or so.  Thanks for your assistance on this!

Okay, it makes sense that the DIM didn't throw a compile error since you are using Object as it's data type, rather than Worksheet.

But that indicates the code was written for "late binding", so I would expect it to have the constants defined locally in that case.  But can't really comment further on that without seeing the full code project, or the database file.

Let us know how that test goes with a fresh database.  If it doesn't work then it may be a problem with the Office / Excel installation on that computer.

---

»bp

Finally got back to this.  Just ran that code in a new module and it returns a 1.

Okay, that is how it should work, and the reference did it's job pulling in the related objects.  So I would say that computer and Access in general don't seem to be the problem.

So not real sure what is happening with the existing database.  It doesn't seem to have that reference.  You could try removing the reference, save the database, then open and re-add the reference, then save again.  Just a thought, since it seems like some of the plumbing isn't doing the job.  Without having the database it's hard to explore other potential problems.

---

»bp

Understand, thanks for all your help.  I will try what you have suggested and see how it goes.

Wow, thanks so much.  A silly typo, ugg.

Thanks for everyone's assistance with my challenge.  You all have been great!