Sort a Table in Excel Instance

Full disclosure I posted this code on another forum, but it was for a different questions that was resolved.

I have a piece of MS Access code that opens an excel instance. Once open Access appends new records to a table. Think most recent monthly detail. This works great. My problem is that after the data is appended, I want to sort the table in descending order. Below is my code.

I should mention the one line of code works as expected if I just paste it into the regular excel file, (not the instance opened by MS Access)

Please Login or Register  to view this content.

When I run it as pasted above I get a compiler error saying "Sub or Function not defined" With the word "Range highlighted.
I'm sure I am making this harder than it needs to be. Any help or guideline at this point would be greatly appreciated.

@Greg M;

Using values of the constants would be better.... i.e.;

Please Login or Register  to view this content.

Hi Haluk,

Thanks for your feedback.

That's a good approach, and is a LOT better than inserting hard-coded values directly in the command itself, i.e.:

Please Login or Register  to view this content.

A possible slight advantage of the approach I suggested might be that it "future-proofs" the code against any Microsoft decision to change (for whatever reason!) the values of the constants.

Regards,

Greg M

Thank you very much for taking the time to answer my question. I added the code you suggested, but am still unable to get the table to sort.
Here is my updated code:

Please Login or Register  to view this content.

Hi again,

Once again this is an untested suggestion - anything else is almost impossible for me as I don't have access to your database/workbooks etc.

I'm assuming that the fault is occurring on the line marked as "Added this line", so try:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thank you everyone. I wasn't able to get it working, here is my full code. Like I stated above, the line to sort the table works in a regular excel file, but not the instance opened through MS Access. So I don't know if it has any value attaching a workbook, and it would be a ton of work to dial back the MS Access database and upload that and the corresponding excel file.

If anyone see something wrong in my code, it would be appreciated, otherwise I will close the question because Ia m not able to provide enough detail.

Thanks again,

Please Login or Register  to view this content.

Sorry, this line should not have been commented out:

Please Login or Register  to view this content.

That said, I don't think I left off the "xlUp". Please correct me if I am incorrect.

I have the "On Error Resume Next" only when creating the excel instance, would that error handler have any effect on any subsequent code?
If I run all the code with all the error handler (aside from the on error resume next) commented out, it runs and gives the same effect. Runs, appends data, but does not do the sort the table.

Good to know. Most of the research I did at the time had it written that way.
I have rewritten it as follows, creating the object first:

Please Login or Register  to view this content.

This works and I have made a little progress. I am now getting a runtime error 9 on the following line:

Please Login or Register  to view this content.

Not sure why, but at least the issue presented itself. The on error resume next was masking this error.

Should I replace the xlUp with "-4162" in the line:

Please Login or Register  to view this content.