Check file extension in excel

[remeng]

Hi guys,

I would like to add a file extension check to this code. The file must be a CSV file. How would I go about performing that task? Additionally, can the solution also be used for different file extensions?

Thanks!

 '-------------- Open CSV File

MsgBox "Please Select the .CSV File to be Analysed"

    Dim CSV As Long
    With Application.FileDialog(msoFileDialogOpen)          ' Open the file dialog
        .AllowMultiSelect = True
        .Show
        For CSV = 1 To .SelectedItems.Count                 ' Display paths of each file selected
            Workbooks.Open .SelectedItems(CSV)              'OPEN the selected file
        Next CSV
    End With

 

[PHV]

Replace this:
For CSV = 1 To .SelectedItems.Count ' Display paths of each file selected
Workbooks.Open .SelectedItems(CSV) 'OPEN the selected file
Next CSV
with this:
For CSV = 1 To .SelectedItems.Count ' Display paths of each file selected
If UCase(Right(.SelectedItems(CSV), 4)) = ".CSV" Then
Workbooks.Open .SelectedItems(CSV) 'OPEN the selected file
End If
Next CSV

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[remeng]

Can you explain what your change is actually doing? I'm still really new to VBA and would like to understand what is actually going on. The main question I have is with the UCase(Right section of the code.

Thanks again PHV

 

[PHV]

Place the cursor inside the function name in your code and press the F1 key.

 

[remeng]

I'm still a little confused as to what it is doing. Am I understanding this correct that it is going to search the file name for .CSV and that it will end if the .CSV is not there?

 

[ettienne]

That is correct.
UCase() converts the text to upper case.
Right(,4) looks at the last 4 characters in the string.

 

[Andrzejek]

it will end if the .CSV is not there?

No, it will not end

The logic will check if the selected file (one by one) has an extension of CSV and only then it will go to the line:

Workbooks.Open .SelectedItems(CSV) 'OPEN the selected file

so if you select the file with extension of .DOC or .exe, or anything else than .csv, those files will NOT be processed.

Have fun.

---- Andy

 

[combo]

With FileDialog you can add (what you did) and delete filters. The user will still be able to write custom file name and extension, but will not see the file (BTW, with the code above you don't test the way of pointing the file).
Example of using filter:

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "csv files", "*.csv"
    If .Show = True Then MsgBox .SelectedItems(1)
End With

combo

 

[combo]

Obviously, there were no filters in your code

combo