VBA Workbook.Open(File)不返回任何内容 [英] VBA Workbook.Open(File) returns Nothing

问题描述

在获得了很多帮助并且不知道正在发生什么之后，它使用了不同的打开方法(

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.

我只发现了Workbook.Open("file")的另一个实例，什么也不返回(链接到Q) .但是，他们的问题是由于在用户定义的函数中调用Workbook.Open("file")(据我所知).在这里，我在Sub中调用它，但是存在相同的问题，无法找到解决方案.我正在使用Excel 2013.

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.

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)

宏继续，但是最后一行Set wb = Workbooks.Open(myFile)将wb设置为Nothing.直到我在代码中更远的地方调用wb为止，这不会产生任何错误.

The macro continues, but the last line Set wb = Workbooks.Open(myFile) sets wb as Nothing. This does not produce any errors until I call wb farther down in the code.

更早的一行Set wsL = ThisWorkbook.Worksheets(3)还将wsL设置为Nothing.

An earlier line, Set wsL = ThisWorkbook.Worksheets(3), also sets wsL as Nothing.

我已经使用调试器检查了每一行和值，并确定myFile是正确的路径，文件名和扩展名.

I have checked each line and values using the debugger, and have determined myFile is the proper path, file name, and extension.

推荐答案

如果您打开的工作簿副本(在另一个文件夹中)的名称与您的VBA尝试使用的名称相同，即相同的名称.打开，它会默默地失败. ActiveWorkbook解决方案似乎可以正常工作-因为您已经打开了至少一个工作簿-并且该工作簿处于活动状态-但您认为不是这样.

If you have a copy of the workbook open (in a different folder) with the same name as 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项目时，您可能已经打开了目标工作簿的副本以检查列号等.

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 Workbook.Open(File)不返回任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！