Determine Excel file type without opening the file?

[ggunter]

Question: Is it possible, using VBA, to determine the actual Excel file type without opening the file?

Issue: I receive data files from other departments. Seems like every time someone changes their download structure, I end up with file types that do not match the download extension (example: xlm file with a xls extension).:banghead: The files can't even be opened because of this. I think I can fix it if I could figure out how to determine what the file type really is. I'm using Office 2010.

 

[The_Doc_Man]

OK - based on reading your problem statement, it seems like you are saying your users might do something like a "SaveAs" for an .XLM file as though it were an .XLS - and I don't think that it is then at all possible to know what it is without opening the file to READ what its REAL file type is.

See, a Windows file is inherently unstructured with respect to the file system, unlike some files systems where knowing the file's type/extension tells you something about the file. The only thing that actually KNOWS how the file is structured is the utility that created it or "viewer" utilities that can peek into it. To anyone/anything else, the file is just what UNIX and OpenVMS systems call "STREAM" format - literally a raw stream of bits to be interpreted that the programs built for that type of stream. The viewers and creation utilities would open the file to see the first several bytes, which include a file detailed structural header that tells you whether it was an .XLS or .XLSX or .XLM or whatever the heck it is. But therein lies the problem - you cannot know until you see that header whether the file is what its file-type says it is.

If you COULD rely on the extension to be accurate, you would use something similar to this snippet:

Public Function FindFileType(stFSpc as String) as String

Dim stTmpI as String
Dim stTmp as String
Dim oFileSys as Object

    stTmpI = Trim$(stFSpc)
    if stTmpI = "" then
        stTmp = ""
    else
        if oFileSys is Nothing then
          set oFileSys = CreateObject("Scripting.FileSystemObject")
        end if
        stTmp = oFileSys.GetExtensionName(stTmpI)
    end if

    FindFileType = stTmp

End Function

If you CAN'T rely on the file-type, you have to be able to read the header section, which is usually the first several dozen bytes, to find the type and sub-type codes. You can read that as a binary record (Use the OPEN verb, which you can look up if not familiar with it) and just do a Mid$ function to pluck out the type and sub-type codes - but that means you have to find them first. That requires a bit more research.

 

[Rx_]

The Doc Man brings up a very valid point.

Just pasting a code segment to illistrate the Wildcard * example
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)

As The Doc Man indicates, it still must be opened. It could be opened in a memory variable (not visible Excel). From there, the Application Properties such as version, extension and other things could be polled. That information could be used in a If-Then or Case statement to guide the rest of your automated process.

For example: Someone sends an older Excel Workbook, do you want to basically copy the data into a new Excel 2010 Workbook then save that new workbook As the working copy in your own directory?
In the Regulatory business, it is common to keep the original file intact whle copying the contents inot a more modern useable standard for analysis.

Private Sub CmdOpenCopyExcelAnyVersioin_Click()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
    'Reset data
    Range("A6:AA150000").Select
    Selection.ClearContents
 
    '---------Wildcard * to open any Excel file--------------
    'Open file with data to be copied
 
    vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
    "*.xl*", 1, "Select Excel File", "Open", False)
 
    'If Cancel then Exit
    If TypeName(vFile) = "Boolean" Then
    Else
    Set wbCopyFrom = Workbooks.Open(vFile)
    Set wsCopyFrom = wbCopyFrom.Worksheets(1)
    End If
 
    '----- End Wildcard * example -----------------------
  'Copy Range
    wsCopyFrom.Range("A2:Y10000").Copy
    wsCopyTo.Range("A6").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False            
    'Close file that was opened
    wbCopyFrom.Close SaveChanges:=False   
    Application.CutCopyMode = False 
    'close userform box after clicking
    Unload UserForm1    
            Application.CutCopyMode = True
End Sub

 

[sxschech]

As the others said, the file needs to get opened, but if done in vba it can be quick and or made not visible. In this thread posts 2 & 4 is code for importing files excel, text, etc and have it converted to latest version of excel then imported.
http://www.access-programmers.co.uk/forums/showthread.php?t=279900

Code for determining excel version. Would need to visit the second msdn link below to see all the format versions:

Public Function ExcelVersion(ByVal stfilepath As String)
'https://msdn.microsoft.com/en-us/library/office/ff840717.aspx
'https://msdn.microsoft.com/en-us/library/office/ff198017.aspx
    Dim objApp As Object
    Dim wb As Object
    Set objApp = CreateObject("Excel.Application")
    objApp.Visible = True
    Set wb = objApp.Workbooks.Open(stfilepath, True, False)
    ExcelVersion = wb.FileFormat
    wb.Close
  
    objApp.Quit

    Set objApp = Nothing
    
    Select Case ExcelVersion
        Case 39
            ExcelVersion = 5 'excel7
        Case 50, 51
            ExcelVersion = 9 'excel12 (2007-2013, xlsx)
        Case 56
            ExcelVersion = 8 'excel8 (97-2003 format in Excel 2007-2013, xls)
    End Select
End Function