Solved Linking/Importing Excel File w/ "Dirty Data" Into Access (1 Viewer)

[arnelgp]

make a copy of your table.
delete the mapping records you don't want from the orig table.

 

[EzGoingKev]

make a copy of your table.
delete the mapping records you don't want from the orig table.

Are you saying that I should have two mapping tables, use one for cleaning and the other for importing?

 

[arnelgp]

on CleanExcel() function, replace the code with this one:

    With objExlSht
        For i = 2 To lastRow
            Do Until rs.EOF
                'get the cell value
                varValue = .Range(rs![excel_column] & i).Value
                'if not Null then convert it
                If Not IsNull(varValue) Then
                    'convert it to correct datatype
                    If Not IsNull(rs![Expression]) Then
                        expr = rs![Expression]
                        expr = Replace$(expr, "p1", varValue)
                        varValue = Eval(expr)
                        .Range(rs![excel_column] & i).Value = varValue
                    End If
                End If
                rs.MoveNext
            Loop
            rs.MoveFirst
        Next
    End With

 

[EzGoingKev]

That worked perfect!

I tried "If Not IsNull([Expression]) Then". I did not know to add the "rs!" to it.