Problem programming the Sort command

[marshy26]

Hi,
I have written a visual basic script that uses Excel to store 'recipe'
data. The user is able to see currently stored recipes in a combo box.
I've recently been asked to sort the recipes so that they appear in the
combo box in alphabetical order. This is where I'm having problems and
I wondered whether anyone reading this can point me in the right
direction?

This is my script, when the form is initiated I'm getting a "Run-time
error 1004: Method 'range of object '_Global' failed.



Private Sub UserForm_Initialize()

Dim intNextRow As Integer
Dim strRowID As String
Dim strMixID As String
Dim LastRow As String
Dim WriteRow As String
Dim WriteRange As String

'On Error Resume Next

intNextRow = 4

Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
to currently open Excel Application
ExcelAppRecipe.Application.Sheets("Recipe").Activate

LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
WriteRow = "" & "5" & ":" & LastRow & ""
WriteRange = "" & "A" & LastRow & ""

ExcelAppRecipe.Rows(WriteRow).Select
ExcelAppRecipe.Range(WriteRange).Activate

ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending


strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Do While strMixID <> ""
cboSelectMix.AddItem strMixID
intNextRow = intNextRow + 1
strRowID = "Recipe!B" & intNextRow
strMixID = ExcelAppRecipe.Range(strRowID).Value
Loop
Set ExcelAppRecipe = Nothing
End Sub

I'm pretty sure it has something to do with the line
ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
but I'm not sute what I've done wrong? If I look on the excel sheet it
has selected the correct area but hasn't sorted it. Any ideas?

Thanks in advance

[William Benson]

On a whim (I program in VBA, not VB script) I am wondering if perhaps you
need to refer to the sheet as well? So, Activesheet.Range("B5") as opposed
to just Range("B5")?


"marshy26" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I have written a visual basic script that uses Excel to store 'recipe'
> data. The user is able to see currently stored recipes in a combo box.
> I've recently been asked to sort the recipes so that they appear in the
> combo box in alphabetical order. This is where I'm having problems and
> I wondered whether anyone reading this can point me in the right
> direction?
>
> This is my script, when the form is initiated I'm getting a "Run-time
> error 1004: Method 'range of object '_Global' failed.
>
>
>
> Private Sub UserForm_Initialize()
>
> Dim intNextRow As Integer
> Dim strRowID As String
> Dim strMixID As String
> Dim LastRow As String
> Dim WriteRow As String
> Dim WriteRange As String
>
> 'On Error Resume Next
>
> intNextRow = 4
>
> Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
> to currently open Excel Application
> ExcelAppRecipe.Application.Sheets("Recipe").Activate
>
> LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
> WriteRow = "" & "5" & ":" & LastRow & ""
> WriteRange = "" & "A" & LastRow & ""
>
> ExcelAppRecipe.Rows(WriteRow).Select
> ExcelAppRecipe.Range(WriteRange).Activate
>
> ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
>
>
> strRowID = "Recipe!B" & intNextRow
> strMixID = ExcelAppRecipe.Range(strRowID).Value
> Do While strMixID <> ""
> cboSelectMix.AddItem strMixID
> intNextRow = intNextRow + 1
> strRowID = "Recipe!B" & intNextRow
> strMixID = ExcelAppRecipe.Range(strRowID).Value
> Loop
> Set ExcelAppRecipe = Nothing
> End Sub
>
> I'm pretty sure it has something to do with the line
> ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
> but I'm not sute what I've done wrong? If I look on the excel sheet it
> has selected the correct area but hasn't sorted it. Any ideas?
>
> Thanks in advance
>

[keepITcool]

your are writing latebound code.

xlAscending is a constant defined in excel library
(which is not referenced).

thus=> replace xlAscending with 1



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


marshy26 wrote :

> Hi,
> I have written a visual basic script that uses Excel to store 'recipe'
> data. The user is able to see currently stored recipes in a combo box.
> I've recently been asked to sort the recipes so that they appear in
> the combo box in alphabetical order. This is where I'm having
> problems and I wondered whether anyone reading this can point me in
> the right direction?
>
> This is my script, when the form is initiated I'm getting a "Run-time
> error 1004: Method 'range of object '_Global' failed.
>
>
>
> Private Sub UserForm_Initialize()
>
> Dim intNextRow As Integer
> Dim strRowID As String
> Dim strMixID As String
> Dim LastRow As String
> Dim WriteRow As String
> Dim WriteRange As String
>
> 'On Error Resume Next
>
> intNextRow = 4
>
> Set ExcelAppRecipe = GetObject(, "excel.application") ' Set Reference
> to currently open Excel Application
> ExcelAppRecipe.Application.Sheets("Recipe").Activate
>
> LastRow = ExcelAppRecipe.Selection.SpecialCells(11).Row
> WriteRow = "" & "5" & ":" & LastRow & ""
> WriteRange = "" & "A" & LastRow & ""
>
> ExcelAppRecipe.Rows(WriteRow).Select
> ExcelAppRecipe.Range(WriteRange).Activate
>
> ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
>
>
> strRowID = "Recipe!B" & intNextRow
> strMixID = ExcelAppRecipe.Range(strRowID).Value
> Do While strMixID <> ""
> cboSelectMix.AddItem strMixID
> intNextRow = intNextRow + 1
> strRowID = "Recipe!B" & intNextRow
> strMixID = ExcelAppRecipe.Range(strRowID).Value
> Loop
> Set ExcelAppRecipe = Nothing
> End Sub
>
> I'm pretty sure it has something to do with the line
> ExcelAppRecipe.Selection.Sort Key1:=Range("B5"), Order1:=xlAscending
> but I'm not sute what I've done wrong? If I look on the excel sheet it
> has selected the correct area but hasn't sorted it. Any ideas?
>
> Thanks in advance

[marshy26]

Thanks for the replies. I've tried both solutions that have been
suggested but I'm still having problems?
What I have noticed is, if I create a macro in excel to do the sort and
then add the following line it works.

ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"

I don't really want to do it this way and would rather go down the
route i was going if it is possible?

I've done a few searches on the net and have tried various ways of
using the sort command but I'm still getting error 1004 :o(

Thanks once again

[parr301]

I was having the same problem trying to sort a data table that is stored in
an add-in. I think that William Benson is on the right track.

This worked for me...each time that you refer to the range, you need to
define the worksheet.

ExcelAppRecipe.Selection.Sort Key1:=ExcelAppRecipe.Range("B5"),
Order1:=xlAscending


"marshy26" wrote:

> Thanks for the replies. I've tried both solutions that have been
> suggested but I'm still having problems?
> What I have noticed is, if I create a macro in excel to do the sort and
> then add the following line it works.
>
> ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"
>
> I don't really want to do it this way and would rather go down the
> route i was going if it is possible?
>
> I've done a few searches on the net and have tried various ways of
> using the sort command but I'm still getting error 1004 :o(
>
> Thanks once again
>
>

[William Benson]

Did you read KeepItCool's response? I thought he had already hit on the
real issue...
"keepITcool" <[email protected]> wrote in message
news:<[email protected]>...

>

>

> your are writing latebound code.

>

> xlAscending is a constant defined in excel library

> (which is not referenced).

>

> thus=> replace xlAscending with 1

>

>

>

> --

> keepITcool

> | www.XLsupport.com | keepITcool chello nl | amsterdam

>



"parr301" <[email protected]> wrote in message
news:[email protected]...
>I was having the same problem trying to sort a data table that is stored in
> an add-in. I think that William Benson is on the right track.
>
> This worked for me...each time that you refer to the range, you need to
> define the worksheet.
>
> ExcelAppRecipe.Selection.Sort Key1:=ExcelAppRecipe.Range("B5"),
> Order1:=xlAscending
>
>
> "marshy26" wrote:
>
>> Thanks for the replies. I've tried both solutions that have been
>> suggested but I'm still having problems?
>> What I have noticed is, if I create a macro in excel to do the sort and
>> then add the following line it works.
>>
>> ExcelAppRecipe.Application.Run "'Theale SH Recipe.xls'!Sort_ID"
>>
>> I don't really want to do it this way and would rather go down the
>> route i was going if it is possible?
>>
>> I've done a few searches on the net and have tried various ways of
>> using the sort command but I'm still getting error 1004 :o(
>>
>> Thanks once again
>>
>>