Results 1 to 5 of 5
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Sorting Excel from Access (Access 2003)

    I need to import data from Excel. One column (Teaching Period) contains mainly numeric data, but has some text values. To ensure that this column is imported as a text field, I want to sort on this column (in reverse order) to put any text values at the top.

    I can't be sure which column this will be, so I need to find it first.

    So I am adding a sort routine to my import code. For testing I have created a procedure that just sorts.

    The first time I run this procedure it works OK, except that it leaves an Excel process running.

    If I run it a second time, it stops at the highlighted line and gives the error "Type Mismatch".

    I assume this is caused by the hidden process, but I don't know what causes the problem .

    I have tried using Set appExcel = New Excel.Application but that did not make any difference.



    Private Sub cmdsort_Click()
    Dim appExcel As Excel.Application
    Dim strFilename As String
    Dim xCell As Excel.Range

    On Error GoTo cmdsort_Click_Error

    Set appExcel = GetObject(, Excel.Application)
    strFilename = Me!txtFileName

    ' need to sort on the teaching period col
    appExcel.Workbooks.Open strFilename
    appExcel.Visible = True
    appExcel.Range("A1").Activate

    appExcel.Cells.Find(What:="Teaching Period", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Set xCell = appExcel.ActiveCell</span hi>
    Set xCell = appExcel.Cells(xCell.Row + 1, xCell.Column)
    Debug.Print xCell.Row
    appExcel.Range("A1").CurrentRegion.Sort Key1:=xCell, Order1:=xlDescending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    appExcel.ActiveWorkbook.Save
    appExcel.ActiveWorkbook.Close


    Exit_cmdsort_Click:
    appExcel.Quit
    Set xCell = Nothing
    Set appExcel = Nothing
    Exit Sub


    cmdsort_Click_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 429 Then ' Excel is not running
    Set appExcel = New Excel.Application
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") ""
    Resume Exit_cmdsort_Click
    End If

    End Sub
    Regards
    John



  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Sorting Excel from Access (Access 2003)

    Thanks Hans

    I don't do enough Excel automation to have a clear understanding of the object model, and when to use what.

    I now find I have a related problem on the same form.

    I provide an option for the user to preview the file they are planning to import, to check that it is what they think it is.
    The job of this code is just to open the file in Excel, and leave it open. The user then does what they want in Excel and closes it.

    The first time I run this it leaves an Excel process running, which does not go away until I exit the Access application.

    If I run it a subsequent time it does not leave an extra instance of Excel.

    Does this matter? I don't know. sometimes, when I try both sorting and opening in succession, I get the error:
    "The remote server is unavailable."

    The bit of code for opening the file is just:

    Dim appExcel As Excel.Application
    Set appExcel = GetObject(, Excel.Application)
    appExcel.Workbooks.Open (strFilename)
    appExcel.Visible = True
    Exit_cmdCheckdata_Click:
    Set appExcel = Nothing
    Exit Sub
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Excel from Access (Access 2003)

    I'd try to avoid selecting cells, and relying on ActiveCell and the like. Also, you must make sure that the workbook is always closed. Here is a slightly modified version.

    Private Sub cmdsort_Click()
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wsh As Excel.Worksheet
    Dim strFilename As String
    Dim xCell As Excel.Range

    On Error GoTo cmdsort_Click_Error

    Set appExcel = GetObject(, "Excel.Application")
    strFilename = Me!txtFileName

    ' need to sort on the teaching period col
    Set wbk = appExcel.Workbooks.Open(strFilename)
    Set wsh = wbk.Worksheets(1)

    Set xCell = wsh.Cells.Find(What:="Teaching Period", LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    Set xCell = xCell.Offset(1, 0)
    Debug.Print xCell.Row
    wsh.Range("A1").CurrentRegion.Sort Key1:=xCell, Order1:=xlDescending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    wbk.Save

    Exit_cmdsort_Click:
    On Error Resume Next
    wbk.Close SaveChanges:=False
    appExcel.Quit
    Set xCell = Nothing
    Set wsh = Nothing
    Set wbk = Nothing
    Set appExcel = Nothing
    Exit Sub

    cmdsort_Click_Error:
    If Err.Number = 0 Then
    Resume Next
    ElseIf Err.Number = 429 Then ' Excel is not running
    Set appExcel = New Excel.Application
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume Exit_cmdsort_Click
    End If
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Excel from Access (Access 2003)

    The only problem I see (I overlooked it originally) is that GetObject expects a string argument:

    Set appExcel = GetObject(, "Excel.Application")

    Apart from that, I see nothing problematic.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Sorting Excel from Access (Access 2003)

    Thanks Again

    That seems to have made a difference.
    Regards
    John



Posting Permissions

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