Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Access/Excel automation problem with Exce Sort function

VicM (Programmer)
5 Apr 12 14:28
Have a client who has an Access DB in 2002-2003 format.  They upgraded Office to 2010.  But have also asked me to add functionality to the DB.

In part of the new functionality I'm trying to perform an Excel Sort from within Access VBA.  I keep getting a 1004, Application-defined or object-defined error.

What is totally weird is that I first recorded the sort macro in Excel, then ported it into the Access VBA.

The Excel Macro code works perfectly when applied standalone in Excel.
Excel Macro code follows:

CODE

Sub Macro1()

Dim grows As Integer

        Worksheets(2).Columns("A:A").Select
        grows = Worksheets(2).UsedRange.Rows.Count
        Worksheets(2).Range("A1:A" & grows).Select
        Worksheets(2).Sort.SortFields.Clear
        Worksheets(2).Sort.SortFields.Add Key:=Worksheets(2).Range("A1:A" & grows), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Worksheets(2).Sort
        .SetRange Range("A1:A" & grows)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub


I then copy this code and insert it into the Access VBA and append the appropriate objects.
That code snippet follows:

CODE

dim impfile as object
dim grows as integer

set imfile = CreateObject("Excel.Sheet")

        impfile.Application.Worksheets(2).Select
   grows = impfile.Application.Worksheets(2).UsedRange.Rows.Count
        impfile.Application.Worksheets(2).Range("A1:A" & gRows).Select
        impfile.Application.Worksheets(2).Sort.SortFields.Clear
        impfile.Application.Worksheets(2).Sort.SortFields.Add Key:=impfile.Application.Worksheets(2).Range("A1:A" & gRows), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With impfile.Application.Worksheets(2).Sort
        .SetRange Range("A1:A" & gRows) ' THROWS 1004 error
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Any suggestions openly welcomed!
Thanks,
Vic
lameid (Programmer)
5 Apr 12 15:37
Whenever doing any thing with another application I have always created an object that insantiates it....

  
ObjectVar = CreateObject("Excel.Application")


That being said you are setting the variable imfile but using the variable impfile.  Note the "P".

I suggest adding Option Explicit to the top of your module and compliling it.
VicM (Programmer)
5 Apr 12 16:30
lameid

That was strictly a typo.  I actually set impfile = CreateObject("Excel.Sheet")

If that weren't the case, none of the commands would have worked.  I would have gotten an 'Object required' type of error the first time I used impfile.
lameid (Programmer)
5 Apr 12 17:24
Does Access know the value of xlSortOnValues if you print it in the immediate window?  If not you need to set a reference to Excel.
VicM (Programmer)
5 Apr 12 20:28
When I check the Object Browser in Access, I do find xlSortOnValues.

If that were the issue, wouldn't that line of code thrown the error?  

The line that threw it was essentially the next line of code.
lameid (Programmer)
6 Apr 12 17:32
Finally started something of my own with Excel to test... Try removing the red text.

CODE

impfile.Application.Worksheets(2).Sort.SortFields.Add Key:=impfile.Application.Worksheets(2).Range("A1:A" & gRows), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 
VicM (Programmer)
7 Apr 12 11:16
lameid,

Thanks for your trouble.  But I have already tried doing what you suggested and the code still fails.  But, once again, it doesn't fail on the line you are questioning; it fails on the line: .SetRange.Range ("A1:B" & gRows)

Vic
lameid (Programmer)
9 Apr 12 10:27
Aside from my original comment that I always start at the application object level in my code, that was the only material difference I saw in code I got to run.  Technically I did not concatenate the range string together but that should not be an issue and I have seen it done in running code.

Is grows in fact a number when that line executes?  If so, the only thing I have is to start with an excel application object and drill down.
SkipVought (Programmer)
9 Apr 12 14:34


make sure you reference the Excel Object

CODE

    Dim impfile As Object
    Dim grows As Integer
    Dim rng As Object
    
    Set imfile = CreateObject("Excel.Sheet")

    With impfile.Application.Worksheets(2)
        grows = .UsedRange.Rows.Count
        Set rng = .Range("A1:A" & grows)
        With rng
            .Sort.SortFields.Clear
            .Sort.SortFields.Add _
                Key:=.Cells, _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            With .Sort
                .SetRange rng ' THROWS 1004 error
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End With

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

VicM (Programmer)
10 Apr 12 10:30
Skip,

Thanks for your input.  

Basically you've confirmed what an answer in another forum stated. That the line of code: .SetRange Range("A1:A" & grows) should be .SetRange.Range("A1:A" & grows).

Although I'm a bit confused by your method of dimensioning rng as an object.  You set it equal to .Range("A1:A" & grows).  I guess I'm not understanding setting an object with the dot in front of the object.  And also, how does the code translate when you have a space between .SetRange and rng?

I understand the object, rng when used with the With statement.  But again, I don't understand how it translates in the .SetRange rng statement.

Thanks,
Vic
SkipVought (Programmer)
10 Apr 12 14:21


Have you looked at Excel VBA Help for SetRange?

The reason I assigned the rng object is that the DOT reference at the point of usage (in the SetRange statement) has a different incorrect DOT reference for assigning that range.  The RANGE has the worksheet as the parent, not the Sort object as the parent.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

lameid (Programmer)
10 Apr 12 15:09
Ironic that is an issue as I was just bit hard by a related issue.  Have a look at my second post here thread705-1680024: Orphaned Excel.Exe Process.... for more information about using declared objects.  

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close