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

EzGoingKev

Registered User.
Local time
Today, 13:09
Joined
Nov 8, 2019
Messages
122
@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

once i caught a fish alive...
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
13,328
make a copy of your table.
delete the mapping records you don't want from the orig table.
 

EzGoingKev

Registered User.
Local time
Today, 13:09
Joined
Nov 8, 2019
Messages
122
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

once i caught a fish alive...
Local time
Tomorrow, 01:09
Joined
May 7, 2009
Messages
13,328
on CleanExcel() function, replace the code with this one:
Code:
    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

Registered User.
Local time
Today, 13:09
Joined
Nov 8, 2019
Messages
122
That worked perfect!

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

Users who are viewing this thread

Top Bottom