VBA access, how to determine if an Excel format file?

Question

Using MS Access VBA, how can I check the file to find out if it is in Excel format?

Answer 1

I never had a problem when the Excel file cannot be directly determined by the extension, but if I had to do this, the first thing that comes to mind is the UNIX file utility, which identifies the type of file 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 in an Excel 2010 file (xlsx), I renamed ".csv", I get:

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

This is a little awkward solution, but in VBA you can develop the C:\cygwin\bin\file.exe using the Windows Script Host and capture the output for each file.

If you encode the path to an Excel file with single ticks around it (for example, "C: \ path \ to \ file"), Cygwin should interpret it correctly (Cygwin utilities expect to see a unix-like path: / path / to / file). I just checked this on a regular 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 file binary in the GnuWin32 SourceForge Project , but it seems to be a bit outdated; I have not tried it, but it can still recognize current versions of Excel.

If you need your own solution for Excel, I’m not entirely sure of my head; hope someone else has done this before.

Answer 2

This is not for Access, but for Excel, I use this. This is not the best or any preferred solution, but get ready.

 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 

Yes, it uses Excel automagic. I know. It's disgusting. And ScreenUpdating does not work fully. Your taskbar will be updated when you open the close file. But still it works.

You may need to create an Excel instance in your Access VBA script and perhaps pass it on to work on something like this. Note. I have not 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. xx 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