Question Opening Excel Spreadsheets

[cpremo]

Question Opening Excel Spreadsheets

You can run the data through a two step process. First import the raw data into a table X (rows) by x (columns). Then, extract all the columns of data into another table (you name them as desired). Then run you comparison against the second table. Finally delete both when done.

The only possible problem you may have is if the names are not normalized (e.g., some Last, First NI and some First. MI. Last). That will cause you some head aces but is doable.

I use this code to Split the "Last, First MI" names in a column to seperate fields since this is how my database is configured. You can probably figure something out for the "First. MI. Last" configuration as well.

Function SplitName() As String
    Dim LastName As String
    Dim FirstName As String
    Dim MiddleName As String
    Dim Title As String
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open "[tblRosterData]", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
    rst.MoveFirst
    Do While Not rst.EOF
        If Not rst.BOF And Not rst.EOF Then
            LastName = Left(rst![Name], (InStr(rst![Name], " ") - 1))
            FirstName = Mid(rst![Name], (InStr(rst![Name], " ") + 1), 99)
            rst![LName] = LastName
            rst![FName] = FirstName
            If InStr(rst![FName], " ") = "0" Then
            
            Else
                MiddleName = Mid(rst![FName], (InStr(rst![FName], " ") + 1), 99)
                FirstName = Left(rst![FName], (InStr(rst![FName], " ") - 1))
                rst![FName] = FirstName
                rst![MI] = MiddleName
                If InStr(rst![MI], " ") = "0" Then
                Else
                    MiddleName = Left(rst![MI], (InStr(rst![MI], " ") - 1))
                    Title = Mid(rst![MI], (InStr(rst![MI], " ") + 1), 99)
                    rst![MI] = MiddleName
                    rst![Title] = Title
                End If
            End If
        End If
            rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
End Function

 

[zzpprk]

You can run the data through a two step process. First import the raw data into a table X (rows) by x (columns). Then, extract all the columns of data into another table (you name them as desired). Then run you comparison against the second table. Finally delete both when done.

This is exactly what I did but found all this table updates a little slow so I used an array of strings as the second table. Makes things a little quicker.

 

[cpremo]

Question Opening Excel Spreadsheets

What does your code look like?

 

[zzpprk]

Here is a sample of my code.

Function AddData(theData() As String, theText, theCount As Long) As Long
  If Not (IsNull(theText)) Then
    theText = Trim(theText)
    If theText <> "" Then
      theCount = theCount + 1
      theData(theCount) = UCase(theText)
    End If
  End If
  AddData = theCount
End Function
 
Private Sub cmdList_Click()
  Dim theImport
  Dim thePath As String
  Dim theCount As Long
  Dim i As Long
  Dim theData() As String
  thePath = "C:\Temp\Example.xls"
  If thePath <> "" Then
    Screen.MousePointer = 11
    DoCmd.TransferSpreadsheet acImport, 8, "tblImport", thePath, False, "A4:F65536"
    Set theImport = Application.CurrentDb.OpenRecordset("SELECT * FROM tblImport", dbOpenDynaset)
    theCount = 0
    theImport.MoveLast
    ReDim theData(theImport.RecordCount * 6) As String
    theImport.MoveFirst
    While Not (theImport.EOF)
      theCount = AddData(theData, theImport![F1], theCount)
      theCount = AddData(theData, theImport![F2], theCount)
      theCount = AddData(theData, theImport![F3], theCount)
      theCount = AddData(theData, theImport![F4], theCount)
      theCount = AddData(theData, theImport![F5], theCount)
      theCount = AddData(theData, theImport![F6], theCount)
      theImport.Delete
      theImport.MoveFirst
    Wend
    For i = 1 To theCount
      ' Processing here ...
    Next i
    Screen.MousePointer = 0
  End If
End Sub

The size of the array containing the strings to search for (theData in my code) is the maximum number of data items I can have (i.e. the number of records in the imported table * the number of fields in each record). As some fields might be empty, the actual number of strings to search for will be less than or equal to this maximum. The AddData() function keeps track of that number.