Clean up data before ADO transfer

[Chats]

I have an Excel (2000) spreadsheet containing data which has been entered without any validation.

I have created an ADO export sub procedure in VBA to send this data across to an Access(2000) table.

Everything works fine, until the sub procedure encounters an item of data in Excel which does not match the data type in Access.

My question is: can anybody suggest an efficient way to validate the data in the Excel spreadsheet before the export sub procedure is run? I am thinking something along the lines of a rule which says that "Column A must only contain text, maximum 50 characters", "Column B must contain a date in the format dd/mm/yyyy", "Column C must contain an integer"

The validation script would then alert the user to the cells which need amending before the import.

Thanks for any help

Adrian

 

[fredericofonseca]

On your procedure to do the ADO bit add a call to another sub, and on this one loop through the cells and do the validation.
If an error is found raise an error on a message box, stating the row/col where the error is occurring.
Then exit the main sub.

Should not be very hard for you if you've already done the ADO bit.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com