Excel VBA can't open Workbook

Question

First: I'm using Excel 2007, but the code has to work for Excel 2003 as well.

My problem is the following: I need to access cells in a different workbook, which may be closed. The following code can be found all around the web:

Function Foo()
    Dim cell As Range
    Dim wbk As Workbook
    Set wbk = Workbooks.Open("correct absolute path")
    ' wbk is Nothing here so the next statement fails.
    Set cell = wbk.Worksheets("Sheet1").Range("A1")
    Foo = cell.Value
    wbk.Close
End Function

sadly, wbk is Nothing after the open statement (I'd love to give a better error message, but no idea how I'd do that; what I'd give for a real IDE and an useful language :/). The absolute path is correct and points to a valid excel xlsx file.

Also I assume the best way to do this, is to "cache" the workbook and not open/close it every time the function is called? Any possible problems with that (apart from having to handle the situation when the workbook is already open obviously)?

Image while stepping through: debugging info

You get no error message and the `Open` statement just returns Nothing? I've tried unsuccessfully to reproduce your problem. You're going to have to give us more detail and context. — Jean-François Corbett, Oct 08 '11 at 07:33
@Jean-FrançoisCorbett To say "no" error message is to say, that excel just continues execution. I looked a bit but can't find any `GetLastError()` or similar and the `On Error Goto` doesn't specify any exception object? The quoted stuff is basically the whole function, but I changed it to the complete function, maybe that helps. — Voo, Oct 08 '11 at 14:04
"? Err.Number" and "? Err.Description" in the Immediate window will get you info on runtime errors. You need to supply the error handler for On Error Goto. When you say "next statement fails" do you mean you get a runtime error there? If so, what? If not, is Cell also equal to nothing? Have you tried with a really simple path, e.g., to a file in My Documents? — Doug Glancy, Oct 08 '11 at 15:14
@DougGlancy I get a `#VALUE!` in the resulting excel cell and the execution stops as soon as it tries to execute the line (but that's to be expected, I'm basically trying to dereference a null pointer there). And yes I've tried `D:\test.xlsx` (so surely no problems with the rights and can't get much simpler) and my user folder. What is the immediate window? I put "? Err.Description" as a watch expression, but that obviously isn't what you meant and just tells me that it's a string containing "? Err.Description". — Voo, Oct 08 '11 at 15:41
Ok I just put a `MsgBox Err.Description` after the Open() statement and sadly it is empty and `Err.Number` is 0.. — Voo, Oct 08 '11 at 15:46
You get the immediate window with Ctrl-G. You can type anything that can be evaluated in it, press enter and get an answer. When you say "execution stops as soon as it tries to execute the line" do you get a messagebox with an error number? Are you sure wbk is nothing on the line after the open statement? Are you stepping through using F8? (I seem to be slipping into stream-of-consciousness here). — Doug Glancy, Oct 08 '11 at 15:58
@DougGlancy Yes I'm stepping through the code with F8 and no I don't get a messagebox or anything, it just stops executing and sets the cell's value to `#VALUE!`. I've added a screenshot of the state after executing the Open() statement to my post ([large version](http://i.stack.imgur.com/cnI7R.png)). I'm grateful for your help, VBA really is a strange language if one's used to stuff like c++, java, c#, python or basically anything else ;-) — Voo, Oct 08 '11 at 16:17
I'm stumped. I've tried putting On Error Resume Next in the calling routine, and a couple of other things, but can't duplicate your results. One clarification, your routine isn't setting A1 to #VALUE, as the code does nothing to set a cell's value, only to return it. Something in the formula in A1 is yielding #VALUE. Anyways, at this point I'd try copying only the relevant code into another workbook, where I think it will work, and then adding the rest of your code. You could also try Rob Bovey's Code Cleaner, but I don't think it will help. — Doug Glancy, Oct 08 '11 at 16:37
@DougGlancy Yeah as I understand it if an uncatched exception occurs #VALUE! is just the value that excel uses to show that. Thanks for your help still - so since it's probably some configuration error, I'll just reinstall office and see if that helps — Voo, Oct 09 '11 at 15:35
Actually the error is caused by UDF limitations. It's not allowed directly call `.Open` within UDF. Take a look [here](https://stackoverflow.com/a/23232311/2165759). — omegastripes, Jan 03 '19 at 16:42

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

I can reproduce this problem. It only happens to me when I attempt to paste this code into a user-defined function.

I believe this is by design (the quote is for XL 2003, but the same thing happens to me on XL 2010)

Using VBA keywords in custom functions

The number of VBA keywords you can use in custom functions is smaller than the number you can use in macros. Custom functions are not allowed to do anything other than return a value to a formula in a worksheet or to an expression used in another VBA macro or function. For example, custom functions cannot resize windows, edit a formula in a cell, or change the font, color, or pattern options for the text in a cell. If you include "action" code of this kind in a function procedure, the function returns the #VALUE! error.

http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

The only workaround I've found is to call this kind of code via a normal macro. Something like selecting the cells to apply it to, then looping over Selection or the like.

Thanks for that, yes that's what I was also doing, just didn't think it was important (well, I wasn't consciously thinking that I'd done something different there). — Voo, Nov 01 '11 at 18:47
Actually the error is caused by UDF limitations. It's not allowed directly call `.Open` within UDF. Take a look [here](https://stackoverflow.com/a/23232311/2165759). — omegastripes, Jan 03 '19 at 16:43

score 2 · Answer 2 · edited Aug 03 '16 at 09:22

You can use this (similar to what Bruno Leite proposed, but much simpler to write):

Dim excelApp As New Excel.Application
excelApp.Visible = False
Set WB = excelApp.Workbooks.Open(FileName, xlUpdateLinksNever, True)

As UDFs are called repeatedly, you should make sure to do an excelApp.Quit before exiting the function (and a WB.close(False) before) to avoid having countless Excel instances running on your box.

I spent some thoughts on it and came to the conclusion that you cannot mess around with the workbooks of the current instance of excel while executing a UDF. On the other hand, opening a second instance of excel will do the job without interference.

But... It is VERY slow in case you have to use the UDF repeatedly. — Javi, Dec 11 '17 at 09:40

score 1 · Answer 3 · answered Jul 20 '12 at 11:03

The workaround of putting my routine into a separate macro in the workbook module, and calling that macro from the Workbook_BeforeSave code, seems to have done the trick.

I've had a similar issue, but in my case it's a "Workbooks.Open(filename)" command at the start of a small routine embedded in Workbook_BeforeSave. VBA just skips right over the line of code as if it weren't there, it doesn't even report an Err.Code or Err.Description.

The only clue for me was that it's part of the Workbook_BeforeSave routine, and the limits with Functions above seem to indicate that could be a possible cause. So I dug around further to find more details.

It seems that Workbook_BeforeSave disables Excel from opening more files, and I guess there's a good reason for doing that, since the File > Open option is still visible in the File menu, but it can't be clicked. Strangely, the Open toolbar icon/button still works, and so whilst I can manually open the file from there, I wonder if it's because it's impossible to call this action from VBA code and that's why they allowed it?

score 1 · Answer 4 · answered Oct 07 '11 at 23:28

To get data from Workbook without is open, you can use this, with ADO connection.

To use in Excel 2007 change this

Microsoft.Jet.OLEDB.4.0

to

Provider=Microsoft.ACE.OLEDB.12.0

and

Extended Properties=\"Excel 8.0;HDR=Yes;\

to

Extended Properties=\"Excel 12.0;HDR=Yes;\

[]'s

score 0 · Answer 5 · answered May 24 '18 at 14:52

You can check the error in a proper way by using the following code:

filelocation = c:\whatever\file.xlsx

On Error GoTo Handler 'this is key as if the next row returns an error while opening the file it will jump to the Handler down there.
Set wkb2 = Workbooks.Open(filelocation, ReadOnly)

Handler:
MsgBox "File " & filelocation & " does not exist or cannot be reached, please review and try again"

I know that this does not answer the question (that's why I also landed in this thread, as I cannot open the file and can't understand why is that so)

Cheers, RV

score 0 · Answer 6 · answered Oct 09 '11 at 07:06

0

You don't have to "Set" a cell, It's part of the workbook class (as far as I know). Just use the following...

foo = wbk.Worksheets("Sheet1").Range("A1").Value

answered Oct 09 '11 at 07:06

TheFuzzyGiggler

929
1
7
14

Cell is not part of any class. It's a variable that was declared as a range, so it must be set. Maybe you're thinking of "cells" which is a property of the Worksheet object. Your suggestion does shorten the code, but doesn't address the issue. -1 – Doug Glancy Oct 09 '11 at 15:13
Yeah I could rewrite everything to fit into a single line - if I then started to name my variables with 1 characters only I'd possibly even get into the Perl programmer's club, but alas that's really not the overall goal ;) – Voo Oct 09 '11 at 15:32
You're right, and I left out the point of my answer. But going back and trying it again it works now. Hmm, Maybe post us your ("correct absolute path")? If possible. Because otherwise your code works fine.. – TheFuzzyGiggler Oct 10 '11 at 02:36

score 0 · Answer 7 · answered Feb 28 '12 at 18:01

I would suggest that you open you the new workbook upon opening the calling workbook, in the worbook_open event.

You then store the new workbook reference in a global variable.

Then the function called by your cell uses the said global variable instead of trying to open a new workbook. This way you go around the limitations.

PS : Of course global variable are to be avoided, some sort of container would be better than a direct global variable.

Excel VBA can't open Workbook

7 Answers7

Linked