VBA code to verify file contents

[Mr2017]

Hi

I have written some code which opens a folder and display a list of CSV files.

(This would be done after a user has downloaded a report from a website).

However, I need to write code that verifies the contents of the file i.e it

i) checks how many columns there are (there are 8 in total) and

ii) checks that the titles of the columns are correct.

Does anyone know how to do this in VBA?

The column titles are shown below. Thanks in advance!

[TABLE="width: 542"]
<tbody>[TR]
[TD="width: 67"]Client[/TD]
[TD="width: 64"]Project[/TD]
[TD="width: 88"]Work Item[/TD]
[TD="width: 62"]User[/TD]
[TD="width: 55"]Date[/TD]
[TD="width: 65"]Hours[/TD]
[TD="width: 89"]Time Code[/TD]
[TD="width: 52"]State[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Johnny C]

You need to open the CSV first. Note you need to specify the format as CSV when you open it
By default if you open a CSV file Excel will try and parse it using a semicolon. You need

Workbooks.OpenText Filename:= <file>, DataType:=xlDelimited, Comma:=True, Local:=True

You can check the number of used columns :-

     If ActiveSheet.Cells.SpecialCells(xlLastCell).Column <> 8 Then ...

To check the titles you would just use :-

    If ActiveSheet.Cells(1,1).Value > "Client" Then ...
    If ActiveSheet.Cells(1,2).Value > "Project" Then ...
    If ActiveSheet.Cells(1,3).Value > "Work Item" Then ...
and so on

 

[Johnny C]

You need to open the file as a CSV, but by default it will try and use semi-colons. You need to use:-

Workbooks.OpenText Filename:= [The file selected from the DIR()], DataType:=xlDelimited, Comma:=True, Local:=True

 

[Mr2017]

Thanks for the detailed response, Johnny.

The name of the CSV file will vary, depending on when a user has donwloaded it - at present, the current name is "Utilisation Tracker (5).csv" but the previous versions were

Utilisation Tracker (4).csv
Utilisation Tracker (3).csv
Utilisation Tracker (2).csv

etc.

I added the other parts of code you mentioned above - thank you! However, I got a compile error "Compile error: Block If without End If"

Do you know what I have done wrong? The full code is below - thanks again, in advance:

Option Explicit


Sub OpenCeloxisDownload()


'the code below will open a folder or file path then allow you to choose a file to open.


Dim fd As FileDialog
Dim filewaschosen As Boolean




Set fd = Application.FileDialog(msoFileDialogOpen)


'This removes 'filters' so that you don't see Word, PowerPoint and other file types which you can't open in Excel


fd.Filters.Clear


'this allows you to add your own filters for file types


fd.Filters.Add "Old Excel files", "*.xls"
fd.Filters.Add "New Excel files", "*.xlsx"
fd.Filters.Add "Macro Excel files", "*.xlsm"
fd.Filters.Add "Any Excel files", "*.xl*"
fd.Filters.Add "CSV files", "*.csv"


'You can then pick one of the options above as a default option so only files with that extension appear when you
'run the macro. I'll pick option 5 (CSV files is the 5th option in the list above) because we want to open CSV files in this instance. So we need to
'set the fd.filterindex to 5 as per below


fd.FilterIndex = 5


'To ensure users can only open one file when this macro is run you add the line below


fd.AllowMultiSelect = False


'this code specifies the folder that will be opened so users don't have to navigate which folder to go to


fd.InitialFileName = Environ("UserProfile") & "\Downloads"


filewaschosen = fd.Show


If Not filewaschosen Then
MsgBox "You didn't select a file"
Exit Sub

End If

fd.Execute


If ActiveSheet.Cells.SpecialCells(xlLastCell).Column <> 8 Then


MsgBox "Check you've selected the right file"

Else

End If

If ActiveSheet.Cells(1, 1).Value <> "Client" Then
If ActiveSheet.Cells(1, 2).Value <> "Project" Then
If ActiveSheet.Cells(1, 3).Value <> "Work Item" Then
If ActiveSheet.Cells(1, 4).Value <> "User" Then
If ActiveSheet.Cells(1, 5).Value <> "Date" Then
If ActiveSheet.Cells(1, 6).Value <> "Hours" Then
If ActiveSheet.Cells(1, 7).Value <> "Time Code" Then
If ActiveSheet.Cells(1, 8).Value <> "State" Then


MsgBox "Check you've selected the right file"

Else

End If


Range("E1").CurrentRegion.Copy




'Application.GetOpenFilename
'Range("E1").CurrentRegion.Copy
'Workbooks("Utilization tracker - test.xlsm").Activate
'Range("E9").CurrentRegion.Offset(1, 0).Delete
'
'Range("A9", Range("A1").End(xlDown).End(xlToRight)).Select
'
'Range("A8").PasteSpecial xlPasteValues






End Sub

 

[Johnny C]

It's the section

If ActiveSheet.Cells(1, 1).Value <> "Client" Then
If ActiveSheet.Cells(1, 2).Value <> "Project" Then
If ActiveSheet.Cells(1, 3).Value <> "Work Item" Then
If ActiveSheet.Cells(1, 4).Value <> "User" Then
If ActiveSheet.Cells(1, 5).Value <> "Date" Then
If ActiveSheet.Cells(1, 6).Value <> "Hours" Then
If ActiveSheet.Cells(1, 7).Value <> "Time Code" Then
If ActiveSheet.Cells(1, 8).Value <> "State" Then


MsgBox "Check you've selected the right file"

Else

End If

When an IF statement ends a line with a Then, then it expects an end if on a line below.
So the End If you have only applies to the

If ActiveSheet.Cells(1, 8).Value <> "State" Then

All the IF statements above it need an end if on a line below and before the next If.

IntError = 0
If ActiveSheet.Cells(1, 1).Value <> "Client" Then 
    MsgBox "First column must have a 'Client' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 2).Value <> "Project" Then 
    MsgBox "2nd column must have a 'Project' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 3).Value <> "Work Item" Then 
    MsgBox "3rd column must have a 'Work item' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 4).Value <> "User" Then 
    MsgBox "4th column must have a 'User' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 5).Value <> "Date" Then 
    MsgBox "5th column must have a 'Date' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 6).Value <> "Hours" Then 
    MsgBox "6th column must have a 'Hours' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 7).Value <> "Time Code" Then 
    MsgBox "7th column must have a 'Time code' heading. Check you've selected the right file"
    intError = 1
End If
If ActiveSheet.Cells(1, 8).Value <> "State" Then 
    MsgBox "8th column must have a 'State' heading. Check you've selected the right file"
    intError = 1
End If

If intError <> 0 Then Exit Sub

Unless you are going to put something where the XXX is you don't need the else. I'm guessing you want to check if the file fails any of those checks. if that's the case you need an Exit Sub ... you can either do all the checks and set an error counter as I have done above and if the error counter is set you Exit Sub, or you could Exit Sub when 1 check is failed on the line before each the End If.