INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

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!

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

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...Thank you. It's already helped me greatly, and I enjoy just reading the inputs from the other members..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > Programmers > DBMS Packages > Microsoft: Access Modules (VBA Coding) Forum

Access/Excel automation problem with Exce Sort function

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,
_{Just traded in my old subtlety...
for a NUANCE!}

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,
_{Just traded in my old subtlety...
for a NUANCE!}

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!

Back To Forum

Back To Microsoft: Access Modules (VBA Coding)

Member Login

Come Join Us!

LINK TO THIS FORUM!

Tek-Tips LinkedIn® Group

Partner With Us!

Feedback

Geography

Tek-Tips Shirts!

Access/Excel automation problem with Exce Sort function

CODE

CODE

CODE

CODE

Reply To This Thread

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

Back To Forum

Join Tek-Tips® Today!

Tek-Tips LinkedIn^® Group

Join Tek-Tips^® Today!