VBA Workbook.Open(File) 不返回任何内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44169301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Workbook.Open(File) returns Nothing
提问by MTJ
EDIT:After lots of help and not having a clue what's going on, it worked using a different method of opening (see @JohnMuggin's help below)--so I un-commented my original code and suddenly it works.
编辑：经过大量帮助并且不知道发生了什么事情后，它使用了不同的打开方法（请参阅下面的@JohnMuggin 帮助）——所以我取消了我的原始代码的注释，突然它起作用了。
I've only found one other instance of Workbook.Open("file")returning nothing (Link to Q). However, their problem was because of calling Workbook.Open("file")in a user-defined function (to my understanding). Here, I am calling it in a Sub, but am having the same issue and can't find a solution. I am using Excel 2013.
我只发现了另一个不Workbook.Open("file")返回任何内容的实例(Link to Q)。但是，他们的问题是因为调用Workbook.Open("file")了用户定义的函数（据我所知）。在这里，我在 a 中调用它Sub，但我遇到了同样的问题并且找不到解决方案。我正在使用 Excel 2013。
Private Sub CommandButton2_Click()
'Set variables
Dim wb As Workbook 'Workbook to open
Dim wbR As Worksheet 'This is the raw data on the new workbook
Dim wsL As Worksheet 'Worksheet in current file
Dim myFile As String 'File to open
Dim FilePicker As FileDialog
'Set light chain hit worksheet
Set wsL = ThisWorkbook.Worksheets(3)
'Optimizes Speed
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
'Opens folder-picking window
With FilePicker
.Title = "Select a file."
.AllowMultiSelect = False
If .Show = True Then
myFile = .SelectedItems(1)
Else: GoTo NextCode
End If
End With
'If folder is not selected
NextCode:
myFile = myFile
If myFile = "" Then GoTo ResetSettings
'Set variable equal to opened workbook
Set wb = Workbooks.Open(myFile)
The macro continues, but the last line Set wb = Workbooks.Open(myFile)sets wbas Nothing. This does not produce any errors until I call wbfarther down in the code.
宏继续，但最后一行Set wb = Workbooks.Open(myFile)设置wb为Nothing. 这不会产生任何错误，直到我wb在代码中调用更远的地方。
An earlier line, Set wsL = ThisWorkbook.Worksheets(3), also sets wsLas Nothing.
较早的一行 ,Set wsL = ThisWorkbook.Worksheets(3)也设置wsL为Nothing。
I have checked each line and values using the debugger, and have determined myFileis the proper path, file name, and extension.
我使用调试器检查了每一行和值，并确定myFile了正确的路径、文件名和扩展名。
回答by TomFp
If you have a copy of the workbook open (in a different folder) with the same nameas the one your VBA is trying to open, it fails silently. The ActiveWorkbook solution appears to work - as you have at least one workbook open already - and that is active - but its not the one you think it is.
如果您打开的工作簿副本（在不同的文件夹中）与您的 VBA 尝试打开的副本同名，则它会以静默方式失败。ActiveWorkbook 解决方案似乎有效 - 因为您已经至少打开了一个工作簿 - 并且它处于活动状态 - 但它不是您认为的那个。
I imagine it it could be a common mistake - as while developing a VBA project you might have a copy of the target workbook open to check on column numbers etc.
我想这可能是一个常见的错误 - 因为在开发 VBA 项目时，您可能会打开目标工作簿的副本以检查列号等。
回答by John Muggins
And at the very last try this little sub. If it doesn't open your workbook then there is a problem with the path or filename
最后试试这个小潜艇。如果它没有打开您的工作簿，则路径或文件名有问题
Sub opendfghj()
Dim wb As Workbook
Workbooks.Open Filename:="C:\Users\User\Desktop\Templates and Example data\Repeat Tests\file.xlsx"
Set wb = ActiveWorkbook
wb.Worksheets("Sheet1").Range("A1") = "It Works"
End Sub
