Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
referencing external worksheet (named) in sharedrive
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
referencing external worksheet (named) in sharedrive
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. -- Regards, Tom Ogilvy "Joe" wrote in message oups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a named array | Excel Discussion (Misc queries) | |||
Referencing External Data | Excel Discussion (Misc queries) | |||
referencing a 3D Named Range | Excel Worksheet Functions | |||
Referencing Named Ranges | Excel Worksheet Functions | |||
External Cell Referencing in VBA? | Excel Programming |