Determining wrong input file using VBA & Excel

Question

Any suggestions for determining if a user-selected Excel file is the incorrect one? I am having the user select the excel file to be parsed and I want to refuse processing it if appears to be an incorrect format.

Answer 1

Put a hidden version field or other string in the excel file and break the execution if the string does not match. Assuming of course that you have control over the file or template, from which the file is created.

Answer 2

If the file being parsed has headings, then check some of the text.

Sub ImportXYZ()

' ... Code to import

 If ActiveWorkbook.Worksheets("Sheet1").Range("A1") <> "XYZ" Then
  MsgBox CStr(ActiveWorkbook.Name) & vbCr & _
  "The file you selected does not contain XYZ data!" & vbCr & "Please try again."

' ... Code to reset

  Call ImportXYZ

 End If

' ... Code to process import

End Sub

Answer 3

As an alternative to putting a marker that identifies the right type of file inside a worksheet, you can also use Workbook properties. Create a Custom property "MyExcelFilesClassification", and give the property a value like "MyTypeOfWorkbook". When your code opens the file, check that the Property has the right value, with something like:

Office.DocumentProperties customProperties = workbook.CustomDocumentProperties as Office.DocumentProperties;
     foreach (Office.DocumentProperty property in customProperties)
     {
        if (property.Name == "MyExcelFilesClassification")
        {
           string modelType = property.Value as string;
           if (modelType == "MyTypeOfWorkbook")
           {
              // do something
           }
        }
     }

This is C# code, but the VBA or VB syntax shouldn't be very different.