referencing external worksheet (named) in sharedrive

[Joe]

Hi,

I have an excel file ("calculations.xls") in my sharedrive folder
"S:me\myfolder\mysubfolder". I intend someone to run a macro on this
file, and then the results from this are used for another macro in
another file ("database"). Now, for the second macro, I want to
reference a sheet named "results" in "calculations.xls" . How do I do
it ?

In John Walkovich's book, I see things like 'C:\My documents\[budget
2004]Sheet1'

and for networks, things like:

'\\DataServer\files\[budget 2005]Sheet1'!

But I'm using:

1. named worksheets in an external reference
2. this external reference may or may not be open at the time it is
referred to, and
3. It is on the network (sharedrive)

Do you think you can help?

Thanks in advance,

Joe.

 

[Tom Ogilvy]

Create a link to it manually and then close the file on the network share.
i.e.

Go to t6he users computer, Open both workbooks, then click in a cell in the
activework book and put in an equal sign. Then go to the Calculations.xls
workbook to sheet "results" and click in a cell and hit enter. Now close
calculations.xls

then look at the formula in the original cell.

This will give you the format. Now you can build that formula with your
macro.

Before building it, check if Calculations.xls is open

On Error Resume Next
set bk = Workbooks("Calculations.xls")
On Error goto 0
if bk is nothing then
' Calculations.xls" is not open

else
' Calculations.xls is open

End if


If every computer sees the workbook as being in
"'S:\Me\MyFolder\MySubFolder[Calculatons.xls]Results'!A1" then maybe you
only need to check if the file is open or not.