• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 67
  • Last Modified:

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

I am in the process of going from Access 2010 to Access 2013 and some things just aren't working.  Access is asking me to declare the syntax xIAscending in my code, which worked perfectly fine in Access 2010.  I don't really understand this.  Any insight would be appreciated.
0
Teri Draper
Asked:
Teri Draper
  • 10
  • 7
  • 2
  • +1
2 Solutions
 
Bill PrewCommented:
Could you please share the code you are having trouble with, and the specific error you are getting (screen capture would be great).


»bp
0
 
Teri DraperProgram AnalystAuthor Commented:
Yes, it's the xIAscending that is giving the trouble which is part of this entire Access excel sorting errorline:
oSheet.Range("A1:E" & LastRow).Sort Key1:=oSheet.Range("E:E"), Order1:=xIAscending, HEADER:=xlYes
0
 
Ryan ChongCommented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Teri DraperProgram AnalystAuthor Commented:
If I need to define it, then why didn't I have to do that in Access 2010?
0
 
Bill PrewCommented:
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...



»bp
0
 
Teri DraperProgram AnalystAuthor Commented:
I currently have the reference for MS Excel Object Library selected, but it hasn't changed anything.  Still receive the same error.
0
 
Bill PrewCommented:
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
0
 
Ryan ChongCommented:
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.
0
 
Teri DraperProgram AnalystAuthor Commented:
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!
0
 
Bill PrewCommented:
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
0
 
Teri DraperProgram AnalystAuthor Commented:
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
0
 
Bill PrewCommented:
Okay, those references look okay, the definition of that xlAscending constant should have been pulled in.  Let's see, what else...

In your code, how is the variable oSheet declared (what is it's DIM statement)?

Also, if you start a brand new Access file on the computer having the problem, go to Visual Basic Editor, and add those references that you showed above, and then add a module and procedure like below, does it compile and execute, or same error?

Option Explicit

Sub Test()
    MsgBox xlAscending
End Sub

Open in new window


»bp
0
 
Teri DraperProgram AnalystAuthor Commented:
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!
0
 
Bill PrewCommented:
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
0
 
Teri DraperProgram AnalystAuthor Commented:
Finally got back to this.  Just ran that code in a new module and it returns a 1.
0
 
Bill PrewCommented:
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
0
 
Teri DraperProgram AnalystAuthor Commented:
Understand, thanks for all your help.  I will try what you have suggested and see how it goes.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Hi Teri,

Welcome to Experts Exchange.

the problem is a typo! (typing mistake)

the code does NOT use "xlAscending" -- what is written is "xIAscending"  --> that second letter is a capital "i" not an "L". It says "xiAscending"

Therefore, Access thinks you have an undeclared variable.

I missed that too, until I copied and pasted into the Immediate window (which uses a serif font).  All the good stuff you learned from Bill, even though it was not how to fix the typo, is information you need to know anyway. It is important to be knowledgeable about References.

have an awesome day,
crystal
2
 
Teri DraperProgram AnalystAuthor Commented:
Wow, thanks so much.  A silly typo, ugg.
0
 
Teri DraperProgram AnalystAuthor Commented:
Thanks for everyone's assistance with my challenge.  You all have been great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now