Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: Authomation Error

    Hi All,
    I have below code for sorting range of data.
    But it fails when I run it more than once....

    I have tried so many different ways to modify it but it doesnt work...

    Any help would be appreciated


    With appexcel

    Dim LastRow As Long
    Dim lastCol As Integer

    . With Activesheet.Sort
    .SetRange Activesheet.Range(Sheets("Customer Analysis").Cells(1, 1), Activesheet.Cells(LastRow, lastCol))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    End With
    Last edited by bareveveryone; 08-01-12 at 07:11.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How and where do you define appexcel? How and where do you specify the active workbook for the instance of Excel you control with appexcel?
    Have a nice day!

  3. #3
    Join Date
    Jul 2012
    Posts
    16
    Hi, thanks for quick response
    I specify the excel in the begining of the code.

    Dim appexcel As Object
    Set appexcel = CreateObject("Excel.Application")

    and then the sheets are added.
    The problem is the sorting code works only once, then I need to close the db and open again and it works but not without closing ....

    I need to make it work in a way that I can run the code more than once and it should work.

    Thanks again

  4. #4
    Join Date
    Jul 2012
    Posts
    16
    I made few changes:

    With appexcel

    Dim LastRow As Long
    Dim lastCol As Integer

    . With Activesheet.Sort
    .SetRange Activesheet.Range(Sheets("Customer Analysis").Cells(1, 1), Activesheet.Cells(LastRow, lastCol))
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    End With


    and it has woked for now... only tried running it 4 times but didnt get errors.
    So I think it is OKK

    but any sugestions please let me know
    Last edited by bareveveryone; 08-01-12 at 07:11.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bareveveryone View Post
    Hi, thanks for quick response
    I specify the excel in the begining of the code.

    Dim appexcel As Object
    Set appexcel = CreateObject("Excel.Application")

    and then the sheets are added.
    As far as I know, you must either create a workbook or open an existing one before manipulating sheets (a Sheet object is member of the Sheets collection of a WorkBook object, according to the Excel Objects Model: Excel Object Model Reference).

    I suspect that you do not properly close Excel when you're done and that, when you try to re-use it, you receive an invalid reference. What's the scope of appexcel (Global, Public, Private) and where is it declared? Do you close it when you're done?
    Code:
    appexcel.Quit
    Set appexcel = Nothing
    Have a nice day!

  6. #6
    Join Date
    Jul 2012
    Posts
    16
    HI, made the following change:
    Last edited by bareveveryone; 08-01-12 at 07:10.

  7. #7
    Join Date
    Jul 2012
    Posts
    16
    Hi,

    I get "automation error the server threw an exception" error !!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There are several odd lines into your code. Sometimes you use the correct reference to an object in appexcel:
    Code:
    With appexcel
    
    .Sheets("Customer Analysis").Select
    and sometimes you do not and part of the reference is missing:
    Code:
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row()
    or:
    Code:
    .SetRange Sheets("Customer Analysis").Range(Sheets("Customer Analysis").Cells(1, 1), Sheets("Customer Analysis").Cells(LastRow, lastCol))
    Always use fully qualified references when you work with Automation. If you don't you never now what can happen and strange errors can occur randomly.
    Have a nice day!

  9. #9
    Join Date
    Jul 2012
    Posts
    16
    Hi Thanks,

    So I will change it as follow
    Last edited by bareveveryone; 08-01-12 at 07:09.

  10. #10
    Join Date
    Jul 2012
    Posts
    16
    is that what you meant ? thanks Sinndho

  11. #11
    Join Date
    Jul 2012
    Posts
    16
    will also add
    Dim Range As Range
    in the begining

  12. #12
    Join Date
    Jul 2012
    Posts
    16
    done as this



    Hopefully this time will work.
    Thanks again
    Last edited by bareveveryone; 08-01-12 at 07:09.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not yet:

    LastRow = Sheets("Customer Analysis").Cells(Rows.Count, 1).End(xlUp).Row()
    Should become:
    Code:
    LastRow = .Sheets("Customer Analysis").Cells(Rows.Count, 1).End(xlUp).Row()
    lastCol = Sheets("Customer Analysis").Cells(1,.Cells.Columns.Count).End(xlToL eft).Column()
    Should become:
    Code:
    lastCol = .Sheets("Customer Analysis").Cells(1,.Cells.Columns.Count).End(xlToL eft).Column()
    .Sheets("Customer Analysis").Sort.SortFields.Add Key:=Sheets("Customer Analysis").Cells(1, lastCol), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    Should become:
    Code:
    .Sheets("Customer Analysis").Sort.SortFields.Add Key:=.Sheets("Customer Analysis").Cells(1, lastCol), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With Sheets("CustomerAnalysis").Sort
    Should become:
    Code:
    With .Sheets("CustomerAnalysis").Sort
    Set Rng = Sheets("Customer Analysis").Range(Cells(1, 1), Cells(LastRow, lastCol))
    Should become:
    Code:
    Set Rng = .Sheets("Customer Analysis").Range(Cells(1, 1), Cells(LastRow, lastCol))
    Note: I did not check the whole syntax, just the references, and I'm not sure I did not miss one or two.
    Have a nice day!

  14. #14
    Join Date
    Jul 2012
    Posts
    16
    Thanks for the help , I will try and let u know how it goes
    hopefully it will go well

    Thanks again

    Talk tomorrow

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •