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

    Unanswered: Reading Excel data from Access (VBA)

    I've written a routine in access 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

  2. #2
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188
    i think the linked table is much better as long as you dont need to save back to excel (read only) for some reasons when you link the table to excel and change data in the linked table, the excel file gets corrupted

    hope this helps
    qha_vn

Posting Permissions

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