vba当其他用户使用文件时打开excel [英] vba Open excel when File is used by another user
问题描述
这是我当前的代码
Public Sub OpenFiles()
'Set LiveDealSheet file path
'Check if LiveDealSheet is already open
LDSP = "C:\Users\DCHEUNG\Desktop\Programing\LiveDealSheet.xlsm"
IsOTF = IsWorkBookOpen(LDSP)
'Set quick workbook shortcut
Set TWB = ThisWorkbook
If IsOTF = False Then
Set LDS = Workbooks.Open(LDSP)
Else
Workbooks("LiveDealSheet.xlsm").Activate
Set LDS = ActiveWorkbook
End If
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
'i was just browsing through the online library and I found that "Open FileName For..."
'have a lot of keywords. If I only want to open the file and copy stuff out to
'another workbook do I use "Open FileName for Input Read As #ff"?
'Then when I actually open the file in OpenFiles() I change
'"Set LDS = Workbooks.Open(LDSP)" to "Set LDS = Workbooks.Open(LDSP) (ReadOnly)"
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
我要打开的文件是一个共享文件。当没有使用,或当我已经打开文件,这段代码工作正常。但是每当其他用户已经打开一个文件,这个代码就会停止。
The file I am trying to open is a shared file. When no is it using, or when I already opened the file, this code works fine. But whenever another user already open a file, this code stops.
我知道一个事实,即使另一个用户使用该文件,我仍然可以在Read - 只有模式。所以我的问题是如何在这里包含该代码,希望没有弹出窗口询问您是否要以只读模式打开。
I know for a fact that even if another use is using the file, I can still open it in Read-Only mode. So my question is how to include that code in here, and hopefully without the pop-up asking if you want to open in Read-Only mode.
对不起,如果这是一个愚蠢的问题,但我完全不懂编码。
Sorry if this is a dumb question, but I am totally new to coding.
推荐答案
首先感谢您的输入。我已经通过一些试验和错误解决了我自己的问题。
First of all thanks for you input. I have solve the problem on my own with some trial and error.
将代码更改为以下
Public Sub OpenFiles()
'Set LiveDealSheet file path
'Check if LiveDealSheet is already open
LDSP = "Z:\LiveDealSheet.xlsm"
IsOTF = IsWorkBookOpen(LDSP)
'Set quick workbook shortcut
Set TWB = ThisWorkbook
If IsOTF = False Then
Set LDS = Workbooks.Open(LDSP)
Debug.Print "Stage 1 Success"
更改了其他语句中的所有内容
changed everything in this else statement
Else
On Error Resume Next
Set LDS = Workbooks("LiveDealSheet.xlsm")
If LDS Is Nothing Then Workbooks.Open FileName:=LDSP, ReadOnly:=True, IgnoreReadOnlyRecommended:=True
End If
End Sub
Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
这篇关于vba当其他用户使用文件时打开excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!