Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Location
    London
    Posts
    87

    Unanswered: Reading data from Excel

    I've written a routine to read in some data from Excel. The data in the sheet is a bit messy so I need to loop through each cell to sort it out.

    Hoever this code is very, very slow. My approach to the sheet was to use Range.Offset(lngOffset,[0 or 1]) to reference the cells. Is there a more efficient way.

    I though of creating a link to the range $A:$B as an alternate approach and then just reading the linked table.

    Thanks in advance.

    Code:
    Sub ManagerImport(strManagersFile As String, strSheet As String)
    Dim xlExcelApp  As Excel.Application
    Dim xlImportWorkbook As Excel.Workbook
    Dim xlManRange As Excel.Range
    Dim xlAccRange As Excel.Range
    Dim blnManager As Boolean
    Dim strAcManager As String
    Dim strAccountName As String
    Dim lngAccount As Long
    Dim dbsCurrent As DAO.Database
    Dim rstImport As DAO.Recordset
    Dim lngOffset As Long
    
    
    Set xlExcelApp = New Excel.Application
    Set xlImportWorkbook = xlExcelApp.Workbooks.Open(strManagersFile, 0, True)
    Set xlManRange = xlImportWorkbook.Worksheets(strSheet).Range("A2")
    
    Set dbsCurrent = CurrentDb
    Set rstImport = dbsCurrent.OpenRecordset("tblManagersImport", dbOpenTable)
    
    lngOffset = 0
    
    strAcManager = ""
    dbsCurrent.QueryDefs("qryClearManagers").Execute dbFailOnError
    Do
        If Left(xlManRange.Offset(lngOffset, 0), 7) = "Mngr - " Then
            strAcManager = Trim(Mid(xlManRange.Offset(lngOffset, 0), 8, Len(xlManRange.Offset(lngOffset, 0)) - 7))
            blnManager = True
        Else
            If Len(xlManRange.Offset(lngOffset, 0)) > 1 Then
                'New Ac responsible
                strAcManager = Trim(xlManRange.Offset(lngOffset, 0))
                If strAcManager = "NOT AVAILABLE" Then
                    strAcManager = "No Manager Assigned"
                End If
                blnManager = False
            End If
        End If
    
        If IsNumeric(Left(xlManRange.Offset(lngOffset, 1), 8)) Then
            'We might have an account in here
            lngAccount = CLng(Left(xlManRange.Offset(lngOffset, 1), 8))
            strAccountName = Trim(Mid(xlManRange.Offset(lngOffset, 1), 10, Len(xlManRange.Offset(lngOffset, 1)) - 9))
    
        With rstImport
            .AddNew
            !Account = lngAccount
            !AccountName = strAccountName
            !Manager = blnManager
            !ManagerName = strAcManager
            .Update
        End With
        End If
        lngOffset = lngOffset + 1
    Loop Until xlManRange.Offset(lngOffset, 0) = "Manager"
    
    xlImportWorkbook.Close False
    Set xlExcelApp = Nothing
    End Sub
    Last edited by Risky; 08-12-03 at 09:16.

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    I'll try the excel forum.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi risky,

    i had a different brief: to harvest user-defined cell-pairs from all XLS in a user-defined directory. it wasn't that slow.

    take a look and see if it helps any

    izy

  4. #4
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Thanks.

    Essentially you are using

    Application.Cells(lngRow, lngCol).Value

    to read the data wheras I was using

    Application.Workbook(strBook).Range("A1").Offset(l ngRow, lngCol).Value

    which probably comes down to

    .Range().Offset()
    vs
    .Cells()

    I'lll try yours and see what happens.

  5. #5
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Yep, thanks. Its a good bit faster.

    Thanks.
    Last edited by Risky; 08-13-03 at 08:04.

Posting Permissions

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