Prompt for error in importing excel table (setwarning)

[ili_sophia]

Hi there,

I have made a userform to allow users to import the excel file into the access table. So far i have made a combo box to select the worksheet in the workbook as well as to import it to a specific table "Dyeing".

i would like the userform to prompt the user with a message box of the errors before they can import it.

The errors are:

1. Missing fields in the the primary column. The primary keys are "Date", "Machines", "Batch Number"
The Msgbox " Date, Machines, Batch Number have blanks. Please check excel file again"

2. Check that the excel data type matches the data type in the access table
msgbox "Mismatch in data type. please check excel file again"

3. The columns of the excel worksheet matches the the access table
msgbox "Columns in excel file and access table do not match please check excel file again"

4 .Duplication of rows
msgbox "there is a duplication of rows in the excel file. please check again"

so far i only manage to turn the setwarning off .Is there for me to do this in my vba code?

This is my code:

Private Sub btnBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant


Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"

If diag.Show Then

For Each item In diag.SelectedItems
Me.txtFileName = item
Next

End If





End Sub

Private Sub btnImportSpreadsheet_Click()
Dim FSO As New FileSystemObject


If Nz(Me.txtFileName, "") = "" Then
MsgBox "Please Select a File"
Exit Sub
End If

If FSO.FileExists(Nz(Me.txtFileName, "")) Then

If MsgBox("Do You Want to Import This File?", vbYesNo) = vbYes Then

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Dyeing", Me.txtFileName, True, DyeingComboBox & "!"
MsgBox "File Imported"

Else
MsgBox "Please Select File Again"

End If
End If

DoCmd.SetWarnings False



End Sub

Private Sub DyeingComboBox_Click()
Dim xlObj As Object
Dim xlWB As Object
Dim varSheet As Variant
Dim i As Integer
'check if workbook really exists
If Nz(Me.txtFileName, "") = "" Then
'workbook does not exist, show message
MsgBox "Please Select a File"
Exit Sub
End If
'workbook exists so get the names of the sheets
Set xlObj = CreateObject("Excel.Application")
Set xlWB = xlObj.Workbooks.Open(Me.txtFileName)

'clear the content of your combobox first
For i = Me.DyeingComboBox.ListCount - 1 To 0 Step -1
Me.DyeingComboBox.RemoveItem (i)
Next
'now add sheet names
For Each varSheet In xlWB.Worksheets
Me.DyeingComboBox.AddItem varSheet.Name
Next
'were done
Me.DyeingComboBox.Requery

'housekeeping
Set varSheet = Nothing
xlWB.Close False
Set xlWB = Nothing
xlObj.Quit
Set xlObj = Nothing


End Sub

Thank you

 

[arnelgp]

point 1: can be done, easy
point 2: can be done, intermediate, instead explicitly convert column to table field type by casting CStr(), CDbl(), etc.
point 3: can be done, easy
point 4: can be done, hard, not advisable, use your table pk to save only the first occurance.

i dont know if there are willing coders out there.
done some of your points before but can't seem to find which db i build

 

[Cronk]

There is a more fundamental issue before addressing your points 1 to 4.

The user has clicked on the combo to run the code that populates the combo so the user will never be able to select and process a work sheet because the same code will run. Use a command button to populate the combo.

As to processing the data, I prefer to test Excel data for integrity and validity within Access by importing the data in a temporary table with all string fields. For example, you can test for invalid dates with IsDate()

Do you need to warn the user of duplicate records? A grouping query will give one instance of the data.

 

[ili_sophia]

How do i create a command button to populate the combo box?

Private Sub DyeingCommadButton_Click()
Call DyeingComboBox

End Sub

I tried this but it did not work

 

[arnelgp]

on your post, you have code for DyeingCombobox_Click().
move all the code, except the Sub declaration (Private Sub..., End Sub)
then delete the Sub completely.

to a new Sub in THE SAME form:

Private Sub FillCombo()
[pasted code here]
End Sub


now on the click of your button:

Private Sub DyeingCommandButton_Click()
Call FillCombo
End Sub