Quick Question about References to Closed Workbooks

[largeselection]

Hi,

So I'm encountering a weird issue so I'm wondering if someone can shed some light on it for me.

In a workbook, I can write a formula to reference a cell in a closed workbook like this:

='E:\Desktop\[Performance.xlsx]Data'!$B$158

The thing that is weird is that it only works some of the time...

So I get a #REF error, but if I open the workbook that it is referring to then I get the result of the formula. The weird thing is that I have 20 different workbooks which I reference and that formula works exactly as expected for 17 of them when they are closed. There are 3 that for some reason the workbook has to be open for the formula to work.

I would have thought it would be all or none. So either all of those formulas which reference all 20 worksbooks would #REF errors or none of them would be.

Any ideas why this would be happening?

I've checked the formula a bunch of times so I don't think it's incorrectly written (especially since it works correctly when the file is open).

Thanks for any ideas.

 

[GlennUK]

Are you sure that the ones giving the #REF errors have been saved as Excel workbooks? ( I've seen people save csv into a .xls extension )

 

[largeselection]

Hi,

Thanks for looking! Yes they are all excel workbooks. They are all generated by the same system so they are very uniform.

Thanks!

 

[GlennUK]

Are all the formulas that give #REF errors just direct links? Or are they embedded within Excel worksheet functions?

 

[largeselection]

Hi,

They are all just direct links and not enbedded in other functions. They all have the same format as:

='E:\Desktop\[Performance.xlsx]Data'!$B$158

Just instead of Performance.xlsx, it is Performance2.xlsx

 

[largeselection]

Now I'm noticing another weird thing...

For some reason my code is skipping row 26. There's no reason that I can see that would cause it to do that.

Basically the code runs through all the files in a folder and adds these direct link formulas to a sheet to pull data from all the files in the folder. It adds only to 1 row and then skips down to the next row. For some reason it does just the first part of Row 26 (puts the formula in cell A26) and then skips the rest of them and goes right to Row 27. There is no error handler that would cause that either.

Something funky is going on it seems

 

[largeselection]

More bizarre stuff. So I did an experiment where I ran it and then I didn't value out the formulas. So I went into one of the cells where I got the #REF error. I opened the file that corresponded and the #REF error evaluated correctly.

The weird part is that when I then closed the file and re-entered the cell and re-calculated, the cell STILL evaluated correctly and had the correct path and everything for the link. I compared that formula to the same formula that was originally in the cell (which threw the REF error) and they were 100% the same.

So why would opening and closing the reference file allow the formula to be calculated correctly?

It's not like with Indirect because for that the file always has to be open for it to work.

So why would this formula not need the file to be open, but sometimes need the file to be opened just for a minute?

 

[largeselection]

I ended up tweaking the code to use RDB's ADO connection method and that circumvented all the issues with the formulas.