CODEWITHSUNDEEP
vbaexcel
The Dude MAN
The Dude MAN

Reputation: 47

VBA to validate use of correct (updated) version of a file

Vendors are sent an excel workbook that they add data to and send back. Recently, we made some changes to this file (some data validation fields) that inhibit users from adding incorrect data. BUT...i've found that vendors are just using the old file because it doesn't contain these annoying validation features. I'm attempting to add a function to an uploading macro that basically checks that the file is the current version (the one with validation).

I thought about adding a hidden worksheet to the new file and doing a simple count through to check the presence of this worksheet. Like below

For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "Validation" Then
    exists = True
    End If
Next i

If Not exists Then
exit sub
End If

Another option might be to add some type of signature to the code and check for the presence of that signature before uploading. I'm open to new ideas on how to tackle this just looking for creative ideas.

thanks for your help!

Upvotes: 0

Views: 193

Answers (1)

Tehscript
Tehscript

Reputation: 2556

I should better write it down as an answer.

If you have data validation, you should have a relational table in Access consisting of these data, connected with a foreign key to your main table. While inserting the data in Excel to Access, use a sub-query where you can validate the data against the validation table in access. If an error occurs, handle it within your code so that you will know if the vendors are using the old workbook.

An example: 

"INSERT INTO main_table (validation_column, column2, ...) 
VALUES ((SELECT validation_id FROM validation_table WHERE validation_name = " & Cells(i,j).Value & "), value2, ...);"

Upvotes: 1

Related Questions