VBA Workbook.Open(File) returns Nothing

Question

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.

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)

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.

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

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

What is calling `CommandButton2_Click` - is it just a button on a UserForm? (I have to ask - I have seen people call code like this from a UDF.) — YowE3K, May 24 '17 at 22:18
Are you sure the `Open()` line gets executed? I can't see how it could fail without at least raising an error. — Tim Williams, May 24 '17 at 22:23
After the `myFile = .SelectedItems(1)`, add `Debug.Print myFile` and let us know what it returns in the Immediate Window (press CTRL+G in the VBE to see it). — BruceWayne, May 24 '17 at 22:24
@TimWilliams I see no reason as to why `Workbooks.Open(myFile)` wouldn't be executed. In the question at the [link](https://stackoverflow.com/questions/7693530/excel-vba-cant-open-workbook) I included, there wasn't an error raised either. — MTJ, May 24 '17 at 22:26
@BruceWayne I've done that, and it returns the expected string: `C:\Users\User\Desktop\Templates and Example data\Repeat Tests\file.xlsx` — MTJ, May 24 '17 at 22:27
The link you gave also mentions other issues that prevent a workbook from being opened, e.g. being within the Workbook_BeforeSave event. Is anything like that likely to be happening in your situation? Do you have any `Workbook` or `Worksheet` events at all in your code that might be confusing things (even though `EnableEvents` is set to `False`)? — YowE3K, May 24 '17 at 22:37
I've never seen this type of line fail without raising some kind of error, so something quite specific is going on in your situation which we don't have access to. Try putting a break on that line and check it's actually running. — Tim Williams, May 24 '17 at 22:42
@TimWilliams I added the break. It doesn't seem to be running--no Excel file opens and `wb = Nothing` remains--, but I can't come up with a reason why it wouldn't run. — MTJ, May 24 '17 at 23:09
Your code runs for me with no modifications. I would put a break on the first line of the sub and use F8 to step through and see what happens. — Tim Williams, May 24 '17 at 23:18
@TimWilliams Stepping through is how I determined the `Set wb = Workbooks.Open(myFile)` line is the issue. The next line is `Set wbR = wb.Worksheets(3)` which produces an error (`run-time error 91`) because `wb = Nothing`. Only way `wb` can equal `Nothing` is if something is going wrong with the `Set wb` line or `myFile` isn't what I want it to be (but `myFile` is the proper string)., — MTJ, May 24 '17 at 23:24
I meant step through the whole sub and see what is causing the `Open()` line to get skipped. — Tim Williams, May 24 '17 at 23:38
@TimWilliams When going through the whole sub the line `Set wb = Workbooks.Open(myFile)` is not skipped over. It executes the line but `.Open()` doesn't open the file. Even when I step through with the full path & file string instead of `myFile` it doesn't work. — MTJ, May 25 '17 at 16:08
Check your tools references to make sure you have all the ordinary references. 1. Visual Basic for Applications, 2. Microsoft Excel 15.0 Object Library, 3. OLE Automation and 4. Microsoft Office 15.0 object library. — John Muggins, May 25 '17 at 16:43
I've got no ideas - something else is going on here. Try cutting your code down to only the problem line - comment out all of the other lines and hard-code the path passed to Open (similar to John's suggestion) - does it still fail? — Tim Williams, May 25 '17 at 17:43
@TimWilliams I don't know how or why it worked, but after adding in the lines John Muggins suggested below and deleting them, the original line `Set wb = Workbooks.Open(myFile)` suddenly works. — MTJ, May 25 '17 at 17:55

score 7 · Answer 1 · answered May 11 '18 at 14:56

7

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.

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.

answered May 11 '18 at 14:56

TomFp

469
1
5
13

This helped with my Workbook that was Active but not open in Excel. I just `ActiveWorkbook.Close` and then could open the same file, this time opening in Excel too. Thanks. – Ovi Oprea Jul 21 '23 at 19:29

score 2 · Answer 2 · answered May 25 '17 at 16:47

2

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

answered May 25 '17 at 16:47

John Muggins

1,198
1
6
12

Are you sure your file is named "file.xlsx" – John Muggins May 25 '17 at 16:50
1

I added in the `Workbooks.Open Filename:="string"` and the `Set wb = ActiveWorkbook`, and it worked. So I then replaced the `string` and tested `Workbooks.Open Filename:=myFile`, and that worked. Perplexed, I removed these lines and tested the original `Set wb = Workbooks.Open(myFile)` and it suddenly worked. I don't know what gives, but these lines work perfectly fine now. – MTJ May 25 '17 at 17:51
Had the same problem. And this solution still not fixing mine. Anyone? – panoet Aug 02 '17 at 01:38

VBA Workbook.Open(File) returns Nothing

2 Answers2

Linked