Using MS Access VBA how can I check a file to know whether it is in Excel format?
如何使用MS Access VBA检查一个文件,以确定它是否是Excel格式?
3 个解决方案
#1
2
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.
我从未遇到过Excel文件不能通过扩展直接确定的问题,但如果必须这样做,首先想到的是UNIX实用程序文件,它通过查看文件的内容来标识文件类型。它识别大量的文件类型。
I use Cygwin for Windows, which is essentially a UNIX environment on Windows.
我对Windows使用Cygwin,它本质上是Windows上的UNIX环境。
When I use the file
command in Cygwin on an Excel 2010 (xlsx) file I've renamed '.csv', I get:
当我在Excel 2010 (xlsx)文件中使用Cygwin中的file命令时,我将其重命名为'。csv”,得到:
$ 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.
这是一个有点尴尬的解决方案,但是在你的VBA中,你可以使用C:\cygwin\bin\file。使用Windows脚本主机执行exe进程,并捕获每个文件的输出。
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:
如果您对Excel文件的路径进行了编码,并在其周围进行了一次标记(例如:“C:\path\to\file”),Cygwin应该正确地解释它(Cygwin实用程序期望看到一个类unix的路径:/path/to/file)。我刚刚在一个普通的Windows命令提示符中验证了这一点,并且成功了:
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.
在GnuWin32 SourceForge项目中也有一个本地Windows二进制文件,但是它似乎有点过时了;我还没有尝试过,但它可能仍然支持现代的Excel版本。
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.
如果你需要一个本地的Excel解决方案——我还不能完全确定;希望以前有人做过。
#2
2
This isn't for Access but for Excel I use this. This is not the greatest nor anybodies preferred solution, but brace yourself.
这不是访问权限,但我用Excel。这不是最好的也不是任何人都喜欢的解决方案,但是要振作起来。
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.
是的,它使用Excel的自动机。我知道。这是可怕的。而且屏幕更新也不是完全有效。当您关闭文件时,任务栏将会更新。但是,它的工作原理。
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.
您可能需要在Access VBA脚本中创建一个Excel实例,并可以选择将其传递给这样的函数。注意,我还没有测试过这个。
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
#3
1
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
#1
2
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.
我从未遇到过Excel文件不能通过扩展直接确定的问题,但如果必须这样做,首先想到的是UNIX实用程序文件,它通过查看文件的内容来标识文件类型。它识别大量的文件类型。
I use Cygwin for Windows, which is essentially a UNIX environment on Windows.
我对Windows使用Cygwin,它本质上是Windows上的UNIX环境。
When I use the file
command in Cygwin on an Excel 2010 (xlsx) file I've renamed '.csv', I get:
当我在Excel 2010 (xlsx)文件中使用Cygwin中的file命令时,我将其重命名为'。csv”,得到:
$ 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.
这是一个有点尴尬的解决方案,但是在你的VBA中,你可以使用C:\cygwin\bin\file。使用Windows脚本主机执行exe进程,并捕获每个文件的输出。
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:
如果您对Excel文件的路径进行了编码,并在其周围进行了一次标记(例如:“C:\path\to\file”),Cygwin应该正确地解释它(Cygwin实用程序期望看到一个类unix的路径:/path/to/file)。我刚刚在一个普通的Windows命令提示符中验证了这一点,并且成功了:
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.
在GnuWin32 SourceForge项目中也有一个本地Windows二进制文件,但是它似乎有点过时了;我还没有尝试过,但它可能仍然支持现代的Excel版本。
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.
如果你需要一个本地的Excel解决方案——我还不能完全确定;希望以前有人做过。
#2
2
This isn't for Access but for Excel I use this. This is not the greatest nor anybodies preferred solution, but brace yourself.
这不是访问权限,但我用Excel。这不是最好的也不是任何人都喜欢的解决方案,但是要振作起来。
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.
是的,它使用Excel的自动机。我知道。这是可怕的。而且屏幕更新也不是完全有效。当您关闭文件时,任务栏将会更新。但是,它的工作原理。
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.
您可能需要在Access VBA脚本中创建一个Excel实例,并可以选择将其传递给这样的函数。注意,我还没有测试过这个。
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
#3
1
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