Access/Excel automation problem with Exce Sort function

[VicM]

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:

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:

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]

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]

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]

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]

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]

Finally started something of my own with Excel to test... Try removing the red text.

impfile.Application.Worksheets(2).Sort.SortFields.Add Key:=[red]impfile.Application.Worksheets(2).[/red]Range("A1:A" & gRows), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[VicM]

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]

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]

make sure you reference the Excel Object

    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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[VicM]

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]

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,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lameid]

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 for more information about using declared objects.