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

EzGoingKev

Registered User.
Local time
Yesterday, 21:07
Joined
Nov 8, 2019
Messages
178
@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

..forever waiting... waiting for jellybean!
Local time
Today, 09:07
Joined
May 7, 2009
Messages
19,169
make a copy of your table.
delete the mapping records you don't want from the orig table.
 

EzGoingKev

Registered User.
Local time
Yesterday, 21:07
Joined
Nov 8, 2019
Messages
178
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

..forever waiting... waiting for jellybean!
Local time
Today, 09:07
Joined
May 7, 2009
Messages
19,169
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
Yesterday, 21:07
Joined
Nov 8, 2019
Messages
178
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