Excel VBA: Error Handling with Case Statement
- by AME
I am trying to validate a file that is uploaded by the user using the code below. The error handler checks the top row of the uploaded file for three specific column names. If one or more of the column names is not present, the program should return a prompt to the user notifying them which column(s) are missing from the file that they uploaded and then close the file.
There are a couple issues with my current VBA code that I am seeking help with:
The prompt doesn't specify which column(s) are missing to the user.
The error handler is triggered even when all required columns are present in the uploaded file.
Code:
Sub getworkbook()
' Get workbook...
Dim ws As Worksheet
Dim filter As String
Dim targetWorkbook As Workbook, wb As Workbook
Dim Ret As Variant
Set targetWorkbook = Application.ActiveWorkbook
' get the customer workbook
filter = ".xlsx,.xls"
caption = "Please select an input file "
Ret = Application.GetOpenFilename(filter, , caption)
If Ret = False Then Exit Sub
Set wb = Workbooks.Open(Ret)
On Error GoTo ErrorLine:
'Check for columns
var1 = ActiveSheet.Range("1:1").Find("variable1", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Column
var2 = ActiveSheet.Range("1:1").Find("variable2", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Column
var3 = ActiveSheet.Range("1:1").Find("variable3", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Column
ErrorLine: MsgBox ("The selected file is missing a key data column, please upload a correctly formated file.")
If Error = True Then ActiveWorkSheet.Close
wb.Sheets(1).Move Before:=targetWorkbook.Sheets("Worksheet2")
ActiveSheet.Name = "DATA"
End Sub
