Workbooks.Open返回与文件名不同的文件 [英] Workbooks.Open returns different file than Filename

问题描述

我遇到了最奇怪的问题.前几天，我在笔记本电脑上编写了以下代码，并且工作正常.现在，我正在台式机上对其进行测试，并且该软件已停止工作.

I am having the strangest problem. I was writing the below code on my laptop the other day and it worked fine. Now, I am testing it on my desktop and it's stopped working.

首先，这是我的代码

Dim oApp As Application
Dim oWb As Workbook

Set oApp = New Application
oApp.Visible = True
Set oWb = oApp.Workbooks.Open(Filename:="C:\myFile.xlsx", ReadOnly:=True)

debug.print oWb.name
'returns "SOLVER.XLAM"
' "SOLVER.XLAM" is not "myFile.xlsx'

debug.print oApp.Workbooks.Count
'returns 1

debug.print oApp.Workbooks(1).name
'returns "myFile.xlsx"

现在，我知道solver是一个插件，在创建它时就将其加载到新应用程序中...但是它如何执行此switcheroo?我仍然可以找到正确的文件，但是我不想冒这个风险，碰巧我的Application对象中只有一个文件(或者第一个文件是我加载的文件)

Now, I know that solver is an add in, and it gets loaded in the new application upon creating it... but how does it perform this switcheroo? I can still get to the correct file, but I don't want to risk it on the coincidence that there is only 1 file in my Application object (or that the first file is the one I loaded)

我正在从excel实例中调用执行此宏，我希望打开一个单独的excel实例，然后在该另一个实例中打开特定的工作簿("C:\myFile.xlsx").

I am calling executing this macro from within an excel instance and I wish to open a separate excel instance and then open particular workbook ("C:\myFile.xlsx") inside that other instance.

我遇到的关键问题是，当我打开另一个实例，然后添加工作簿并将其设置为我的oWb变量时，...以某种方式，当我稍后调用该oWb变量时，它所指的是与我的东西不同的东西.设置为.

The key problem I'm having is that when I open the other instance and then add the workbook and set it to my oWb variable... somehow, when I later call that oWb variable it refers to something different from what I had set it to.

'This is how it makes me feel:
Dim x As Integer
x = 5

Debug.Print x
' 12

推荐答案

我认为，如果您只是稍微完善一下代码以确保完全按照自己的意愿进行操作，那么您会没事的.由于尚不清楚您是从Excel还是另一个MS Office应用程序中调用代码，因此我在下面提供了一些内容.

I think if you just refine your code a bit to ensure you are doing exactly what you want, you will be fine. Since it's unclear whether you are calling the code from within Excel or another MS Office Application, I placed to subs below.

如果在Excel中运行它，请运行它:

Run this if running it in Excel:

Option Explicit

Sub insideXL()

Dim oWb As Workbook
Set oWb = Workbooks.Open("C:\myFile.xlsx", ReadOnly:=True)

Debug.Print oWb.Name
Debug.Print Workbooks.Count
Debug.Print Workbooks(1).Name

oWb.Close false

Set oWb = Nothing    

End Sub

如果在另一个程序中运行，请运行此命令.我使用早期绑定，但是如果您愿意，也可以使用晚期绑定:

Run this if running in another program. I use early binding, but you could use late binding as well, if you wish:

Sub outsideXL()
'make sure Microsoft Excel X.X Object Library is checked in Tools > References

Dim oApp As Excel.Application
Set oApp = New Excel.Application

Dim oWb As Excel.Workbook
Set oWb = oApp.Workbooks.Open("C:\myFile.xlsx", ReadOnly:=True)

oApp.Visible = True

Debug.Print oWb.Name
Debug.Print Workbooks.Count
Debug.Print Workbooks(1).Name

oWb.Close = True
Set oWb = Nothing
Set oApp = Nothing    

End Sub

这篇关于Workbooks.Open返回与文件名不同的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！