|
楼主 |
发表于 2014-12-30 12:37
来自手机
|
显示全部楼层
萨秋 发表于 2014-12-29 17:20
复制的话,不需要打开文件了吗。
请教,在代码中 如何解决,多谢!
Private Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function lClose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
'判斷某文件是否在使用中
Public Function IsFileAlreadyOpen(ByVal FileName As String) As Boolean
Dim hFile As Long
Dim lastErr As Long
hFile = -1 ' 初始化文件句柄.
lastErr = 0
hFile = lOpen(FileName, &H10)
If hFile = -1 Then ' 文件是否能正确打開并可共享
lastErr = Err.LastDllError
Else
lClose (hFile)
End If
IsFileAlreadyOpen = (hFile = -1) And (lastErr = 32)
End Function
Function ReadExcel(strName As String, strSheetName As String) As ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer
Dim sql As String
On Error Resume Next
If IsFileAlreadyOpen(strName) = True Then '如果文件已經被打開,就複製一份,讀取副本,缺點是自己打開的也會複製
Dim fso As New FileSystemObject
Dim fl As File
Set fl = fso.GetFile(strName)
strName = fl.ParentFolder & "\tmp" '副本去掉xls後綴,避免別人去打開
fl.Copy (strName)
Set fl = fso.GetFile(strName)
fl.Attributes = Hidden '隱藏副本文件
End If
Conn.Open "provider=microsoft.ace.oledb.12.0;extended properties=""Excel 12.0 Xml;HDR=YES"";data source=" & strName '?接EXCEL文件"
sql = "select * from [" & strSheetName & "$]" '打?EXCEL表
rs.Open sql, Conn, adOpenForwardOnly, adLockReadOnly
Set ReadExcel = rs
End Function |
|