CODEWITHSUNDEEP
vbaexcelfile-extension
aLearningLady
aLearningLady

Reputation: 2088

If file extension is .xls, then MsgBox

I have written a VBA script that performs different sorting/matching/filtering functions for a client with a large amount of data (anywhere from 45,000 to 500,000 rows).

The issue is, if the user exports or saves this data as, say, an .xls file, only 65,536 rows will be saved which would be potentially insufficient. On execution of the script, I'd like a MsgBox to appear IF the user "mistakenly" exports the data as an .xls file, else continue.

I've tried the code below in order to see if I can even get the file extension properly, but my MsgBox isn't returning anything:

Sub ext()

Dim extFind As String
Dim sFile As String

    Dim FilePath As String
    FilePath = Application.ActiveWorkbook.Path
    sFile = Dir(FilePath & Filename & "*")
    extFind = Right$(sFile, Len(sFile) - InStrRev(sFile, "."))
    MsgBox extFind
End Sub

Any suggestions would be greatly appreciated.

Upvotes: 1

Views: 16358

Answers (2)

Bond
Bond

Reputation: 16311

The FileSystemObject library has a GetExtensionName() function to make life easier:

With CreateObject("Scripting.FileSystemObject")
    strExt = .GetExtensionName(ActiveWorkbook.Path)
End With

If StrComp(strExt, "xls", vbTextCompare) = 0 Then
    ' Display error
End If

Or, you could just check the number of rows in the workbook:

If ActiveSheet.Rows.Count = 65536 Then
    ' Display error
End If

Upvotes: 3

mielk
mielk

Reputation: 3940

You miss a slash between FilePath and Filename:

sFile = Dir(FilePath & "\" & Filename & "*")

By the way, where do you assign value to variable Filename? If you meant to use the name of the active workbook you should use ActiveWorkbook.Name.

Upvotes: 1

Related Questions