Good morning Experts.
I'm working with some code provided by another expert, and I want to make sure that the methods are correct, as I had other code that was not correct and a few experts here helped to fix.
My knowledge of code is VERY limited and this is why I'm here asking for your assistance.
The below code imports the data only after I convert the Excel file to CSV. It does not allow me to import and evaluate the Excel file that contains the same information. Although the CSV import occurs, those fields containing null values are not correctly evaluated. Null values should be acceptable values and TRUE placed in the chk_Field for the corresponding field.
Review code methods and make code modification recommends where needed.
Modify code to allow import from Excel directly
Modify code to allow Null value acceptance during import check process
Thank you in advance for your assistance. Any code explanations you can provide would be greatly appreciated. I would like to try and understand the flow of the code. Thanks again.
The purpose of this database is to upload files manipulated by customers and verify that the data submitted is correctly formatted.
This is what should happen:
1) Navigate to Excel file provided by customer and import data into TEMP table containing all text fields.
2) Evaluate each field to ensure that numbers, text, dates, and other field types are what they are supposed to be. Customer's sometimes change the field and/or do not provide the right formatted data and this is our way of confirming the data provided before pushing forward. Within the TEMP table is a chk field for each imported field (Date has chk_Date). If the data provided is formatted correctly, the chk_field is updated to True, else False.
3) Once the checks on each field and record is completed, the user opens a form that utilizes the chk_fields and conditional formatting to quickly identify data issue and allows the user to modify the data into the correct format. If Date is anything but null or an actual date, chk_Date = False and conditional formatting will highlight Date of the record/field containing the issue. Changes made on the form calls the same functions used when checking the whole table and chk_field is updated if correct.
4) Once all data format issues are resolved, the result will be pushed into a CSV file for a corporate system process.
Code needing assistance with:
This is the code that Experts here assisted with and works as it is supposed to. I'm providing as a reference because the above code uses this code in the data validation process.
Public Function GetFile() As Variant
Dim dialog As Object
Dim pickedfile As Boolean
Set dialog = Application.FileDialog(3)
GetFile = Null
.InitialFileName = "%USERPROFILE%\Desktop\*.xlsx"
.AllowMultiSelect = False
.Title = "Please select file for import"
.Filters.Add "Excel Files", "*.xlsx"
pickedfile = False
pickedfile = .Show
If pickedfile Then
GetFile = .SelectedItems.Item(1)
Public Sub readdata(FileName As Variant)
'Dim i As Integer, j As Integer, S As Boolean, ErrCnt
Dim ErrCnt As Integer
Dim FN As Integer
Dim Str As String
Dim A() As String
Dim rs As Recordset
FN = FreeFile
CurrentDb.Execute "Delete * FROM tbl_TEMP_RFI"
Set rs = CurrentDb.OpenRecordset("tbl_TEMP_RFI")
Open FileName For Input As #FN
Line Input #1, Str ' Read line with headings and ignore
ErrCnt = 0
Do Until EOF(FN)
Line Input #1, Str
' Str = Replace(Str, "'", "") 'Remove extra apostrophe
A = Split(Str, ",")
ErrCnt = ErrCnt + 3 + chk0(A(0)) + chk1(A(1)) + chk2(A(2)) ' + chk3(A(3))
rs("ProductInfo_NDC") = A(0)
rs("ProductInfo_ExpectedLaunchDate") = A(1)
rs("ProductInfo_PkgSize") = A(2)
'rs("PkgQty") = A(3)
rs("chk_ProductInfo_NDC") = chk0(A(0))
rs("chk_ProductInfo_ExpectedLaunchDate") = chk1(A(1))
rs("chk_ProductInfo_PkgSize") = chk2(A(2))
'rs("cPkgQty") = chk3(A(3))
'If ErrCnt = 0 Then
'Here you can directy import data from tblProducts
' DoCmd.OpenForm "CheckImport"
Function chk0(ByVal varValue As Variant) As Boolean
' Check if varValue is Null or contains exactly 11 digits.
chk0 = IsNull(varValue) Or (Format(Val(Nz(varValue)), String(11, "0")) = Trim(varValue))
Function chk1(ByVal varValue As Variant) As Boolean
'Check if varValue is Null or a valid date expression.
chk1 = IsNull(varValue) Or IsDate(varValue)
Function chk2(ByVal varValue As Variant) As Boolean
'Check if varValue is Null or numeric
chk2 = IsNull(varValue) Or IsNumeric(varValue)