My Excel macro is referencing the information in a txt file and when I test using the following code on my local machine or a mapped network drive, it works fine. But I need the file on a non-mapped drive so anyone in the company can get to it. I'm struggling with how to reference that drive. The text file I'm look at will be located on the network in the same directory as the spreadsheet.

I've tried various things like only the file name, using an http address, using a server name substituted for the path below, but that obviously doesn't work. Looks like there is another piece of code I need to identify the path before I can reference the file.

    ProdFile = "C:\Documents and Settings\chp\My Documents\Expense Report\ExpenseReportVersion.txt"
    FileNum = FreeFile
    Open ProdFile For Input As FileNum
    ProdVersion = Input(3, FileNum)
    Close #FileNum

Any ideas would be appreciated. Thanks!

Place the text file in the same directory as your worksheet and change you file name

from

ProdFile = "C:\Documents and Settings\chp\My Documents\Expense Report\ExpenseReportVersion.txt"

to

ProdFile = ActiveWorkbook.Path & "\ExpenseReportVersion.txt"

You final code should be:

    ProdFile = ActiveWorkbook.Path & "\ExpenseReportVersion.txt"
    FileNum = FreeFile
    Open ProdFile For Input As FileNum
    ProdVersion = Input(3, FileNum)
    Close #FileNum

Try that...

Thanks, jfejsa!

I tried this, and it works perfectly when I'm opening the file from the server or a local machine and I thought I was home free.

However, the file is typically going to be accessed through a browser, and when I use this code and open it in the browser, ActiveWorkbook.Path resolves to a URL and the Open command fails; it seems to want a physical file address. I don't want to hard code the name of the server into the file because it creates havoc with our change control.

Is there another command I could use instead of Open? Or is there another routine I could use to convert the url to a physical address before I use the Open command?

Any additional help would be greatly appreciated!

Sorry mate, I don't know of any other way to open the file for INPUT.

If you only wanted to open ExpenseReportVersion.txt through a browser you could just create a URL link to it and use the file name and path to create a link. However, opening the file for input is another story. Sorry I can't be of any help.