问题描述
我有一个访问应用程序,我正在尝试打开一个Excel文件。如果excel尚未运行,则没有问题。如果excel打开,它会在新实例中正确打开excel,但也会尝试在excel的现有实例中以只读方式打开该文件。
我该如何阻止这个?我的代码如下。 访问用户有多个版本的excel因此后期绑定。
I have an access app and am trying to open an excel file. if excel is not already running, there is no problem. if excel is open, it correctly opens excel in a new instance but also tries to open the file as read only in the existing instance of excel. how do I stop this? my code is below. the access users have multiple versions of excel hence the late binding.
理想情况下我想在打开文件之前检查以下内容:
ideally I wanted to check the following before opening the file:
是在用户PC上打开文件? - 如果是,则激活它
is the file open on the users pc? - if yes then activate it
是网络上有人打开的文件吗? - 显示用户打开的消息x
is the file open by someone on the network? - show a message that its open by user x
否则打开文件
Public Sub openXL(strfile As String, Optional Vis As Boolean)
On Error GoTo openXL_Error
If Nz(strfile, "") = "" Then Exit Sub
Dim xlApp As Object
If IsWBOpen(strfile) = False Then
'createNew:
On Error Resume Next
Set xlApp = GetObject("Excel.application")
Debug.Print "creating new"
If xlApp Is Nothing Then
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.Visible = True
xlApp.Workbooks.Open strfile ', True, False
End If
checkvis:
If xlApp Is Nothing Then GoTo LocalExit
If Nz(Vis, False) = True Then
xlApp.Visible = True
Else
xlApp.Visible = False
End If
LocalExit:
On Error Resume Next
Set xlApp = Nothing
Exit Sub
openXL_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure openXL of Module ExcelControl"
GoTo LocalExit
End Sub
Function IsWBOpen(fileName As String)
Dim ff As Long, ErrNo As Long
IsWBOpen = False
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: IsWBOpen = False
Case 70: IsWBOpen = True
Case Else: Error ErrNo
End Select
End Function
推荐答案
GetObject方法的第一个参数是filespec。您是否尝试添加filespec以查看是否可以"获取"打开的Excel文件?
The first argument to the GetObject method is the filespec. Have you tried adding the filespec to see if you can "get" the open Excel file?
例如:
设置xlApp = GetObject(strfile)
Set xlApp = GetObject(strfile)
或
设置xlApp = GetObject(strfile," Excel.Application")
Set xlApp = GetObject(strfile, "Excel.Application")
只是想一想......
Just a thought...
这篇关于当excel已经打开时,使用vba打开excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!