当excel已经打开时，使用vba打开excel [英] opening excel from access with vba when excel is already open

问题描述

我有一个访问应用程序，我正在尝试打开一个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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！