Using a Filename as a variable or reference

Posted by Mike on November 13, 2001 3:39 AM

Hello,

Here is my problem. I have one spreadsheet of data which is a downloaded file which will never change format. I am using the INDIRECT command to pass the Filename and Folder location of this data spreadsheet from cells on my new spreadsheet, in order to reference this data file in a formula.

The problem I am having is that it only works when the data file is open in Excel first. Is there any way to write a formula so that the data file does not need to be open? I have tried specifying the whole complete path location (eg. 'F:\JV\Model\[datafile.xls]sheetname'!cellrange) but this does not work either.

Thanks,

Mike

Posted by Damon Ostrander on November 13, 2001 9:18 AM

Hi Mike,

It is always necessary to open a file in order to read data from it.

That being said, it IS possible to have Excel open the file when the data are needed, then immediatly close the file.

Another option that warrants consideration if the file just contains data is to write the data to a text or data file rather than a .xls file. This way the file can be read using VBA I/O commands, which makes the file opening and closing invisible to the user, and usually very fast. The salient VBA I/O commands are Open, Input #, Get #, Print #, Write #, Close.

I hope this helps.

Damon