Risky
08-13-03, 05:38
| 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. 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 |