Access VBA how to deterine if file is Excel Format?

Question

Using MS Access VBA how can I check a file to know whether it is in Excel format?

Answer 1

I have never had an issue where an Excel file can't directly be determined by extension, but if I had to do that, the first thing that comes to mind is the UNIX utility file, which identifies a file type by looking at its' contents. It recognizes a very large number of file types.

I use Cygwin for Windows, which is essentially a UNIX environment on Windows.

When I use the file command in Cygwin on an Excel 2010 (xlsx) file I've renamed '.csv', I get:

$ file afile.csv
afile.csv: Microsoft Excel 2007+

It's a slightly awkward solution, but in your VBA you could fork a C:\cygwin\bin\file.exe process using Windows Script Host, and capture the output for each file.

If you code the path to the Excel file with single ticks around it (i.e. 'C:\path\to\file'), Cygwin should interpret it correctly (Cygwin utilities expect to see a unix-like path: /path/to/file). I just verified this in a normal Windows command prompt, and it worked:

c:\>c:\cygwin\bin\file.exe 'C:\path\to\afile.csv'
C:\path\to\afile.csv: Microsoft Excel 2007+

There is also a native Windows binary of file in the GnuWin32 SourceForge project, but it seems to be a little outdated; I haven't tried it, but it may still recognize modern Excel versions.

If you need a native Excel solution -- I'm not entirely sure off the top of my head; hopefully someone else has done this before.

Answer 2

This isn't for Access but for Excel I use this. This is not the greatest nor anybodies preferred solution, but brace yourself.

Public Function IsExcelFormat(ByVal filePath As String) As Boolean

    On Error GoTo Nope
    Application.ScreenUpdating = False

    Dim wb As Workbook
    Set wb = Workbooks.Open(filePath )
    IsExcelFormat = (wb.FileFormat > 50)

CleanExit:
    Application.ScreenUpdating = True
Exit Function

Nope: ' Clearly not Excel format
    Err.clear
    IsExcelFormat = False
    Resume CleanExit:

End Function

Yeah it uses Excel's automagic. I know. It's hideous. And the ScreenUpdating doesn't work entirely. Your taskbar will update as you open an close the file. But still, it works.

You might need to create an instance Excel in your Access VBA script and optionally pass it to function something like this. Note I haven't tested this.

Public Function IsExcelFormat(ByVal file_path As String, _
        Optional byRef excel_instance as Excel.Application = Nothing) As Boolean
    On Error GoTo Nope

    Dim local_excel as boolean
    If excel_instance Is Nothing Then 
       Set excel_instance = New Excel.Application
       local_excel = True
    End If

    Dim wb As Excel.Workbook

    excel_instance.ScreenUpdating = False

    Set wb = excel_instance.Workbooks.Open(file_path)
    IsExcelFormat = (wb.FileFormat > 50)
    wb.Close savechanges:=False

CleanExit:
    If local_excel Then 
        excel_instance.Quit
    Else
        excel_instance.ScreenUpdating = True    
    End If
Exit Function
Nope: ' Clearly not Excel format
    Err.clear
    IsExcelFormat = False
    Resume CleanExit:
End Function

Answer 3

Some notes on a possible approach using ADOX

Sub SortFiles()
''Library reference: Windows Script Host Object Model
Dim fs As New FileSystemObject
Dim ts As TextStream
Dim sType As String
Dim sFile As File

For Each sFile In fs.GetFolder("Z:\Docs\").Files
    sType = sFile.Type

    If InStr(sType, "Microsoft") = 0 Then
        sList = ListTables(sFile.Name)
        If sList = "Error: Not Excel" Then
            ''Move to suitable folder
        Else
            Debug.Print sList
            Stop
            ''This can be read as Excel, most likely
        End If

    ElseIf sType Like "*Excel*" Then
       ''Includes CSV
        sFile.Move "z:\docs\Excelfiles\"
    Else
        sFile.Move "z:\docs\OtherMS\"
    End If
Next

End Sub

Function ListTables(sFile As String) As String
''Library reference: Microsoft ADO Ext. x.x for DDL and Security
Dim cat As New ADOX.Catalog
Dim scn As String
Dim t As ADOX.Table
Dim cn As New ADODB.Connection
Dim sList As String

On Error GoTo Handle_Err:

    scn = "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source=" & sFile & ";Extended Properties=""Excel 8.0;HDR=No"""

    cn.Open scn

    cat.ActiveConnection = cn

    For Each t In cat.Tables
        sList = sList & vbCrLf & t.Name
    Next t

    ListTables = sList

Exit_Proc:
Set cn = Nothing
Set cat = Nothing
Exit Function

Handle_Err:
    If Err.Number = -2147467259 Then
        ''External table is not in the expected format.
        ListTables = "Error: Not Excel"
        Err.Clear
        Resume Exit_Proc
    Else
        Debug.Print Err.Number, Err.Description
    End If

End Function