Solved Linking/Importing Excel File w/ "Dirty Data" Into Access

[EzGoingKev]

@arnelgp - I finally got a chance to use this and it works great. Thanks for the help with this!

 

[EzGoingKev]

@arnelgp - I have a question on the CleanExcel function.

I am working with (52) fields. It takes (12) minutes to format the data.

In an attempt to shorten that time I tried leaving the expression field in the mapping table blank for the fields that do not need formatting. I get an invalid use of null error.

Can the code be modified to skip the formatting of that field if the expression field value is null?

 

[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.