 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007MS Excel: Linking spreadsheet and Absolute and Relative Paths
Our staff uses several spreadsheets, many of which are linked to SEVERAL other spreadsheets. Of course, all of these documents are stored on the network. The problem we are having is the absolute path that the link uses. Some users will access these file via (e.g.) 'S:\excel documents', while others will use 'R:\excel documents' to connect. Each time a user connects there is a 50-50 chance they will have to update the links. ** What a hassle** Is there any way to use a relative path to link spreadsheets? Using a UNC path isn't possible due to security restrictions and changing user's mapped drives will be problematic at best. Is there a better/easier way to approach this? Thanks! Frank I'm curious.....what "security restrictions" would prevent you from using the UNC instead of the mapped drive? *********** Regards, Ron XL2002, WinXP > Our staff uses several spreadsheets, many of which are linked to > SEVERAL other spreadsheets. [quoted text clipped - 15 lines] > > Frank Frank G wrote... ... >The problem we are having is the absolute path that the link uses. >Some users will access these file via (e.g.) 'S:\excel documents', [quoted text clipped - 7 lines] > >Is there a better/easier way to approach this? If you really believe you can't use UNC pathnames (this is one of their main intended uses), then there's always consistent drive mapping policy. It's unlikely all your users have every letter mapped to a drive, so it's not unlikely you could standardize on a commonly unused drive letter, maybe Q or V. Excel provides NO relative path capabilities whatsoever. Maybe that'll change eventually, but Excel 2007 doesn't provide such functionality, so you're going to have to wait until at least the next major version release, and that's unlikely to be earlier than 2009. If you want to handle this now, your ONLY choices are UNC pathnames, rigidly consistent drive mapping, or an autoexecuting macro that runs when the workbooks open that changes drive mapping based on the location of a common marker file. The last would involve the using the ChangeLink method of the Workbook class, basically automating what your users have to do now. Thanks for the advice everyone. The 'security' reasons I was referring too are..... the files sit in a directory only accessible by 3 users, due to confidentially reasons. During year-end all of the linked spreadsheets (except the confidential ones) are temporarily moved to ANOTHER location on the server so that 10+ users have access to it. Once year-end is over, the files are moved back. Sounds a little silly, I know. Anyway... if anyone has other ideas, please let me know. Thanks again! > Frank G wrote... > ... [quoted text clipped - 26 lines] > method of the Workbook class, basically automating what your users have > to do now. Frank G wrote... ... >The 'security' reasons I was referring too are..... the files sit in a >directory only accessible by 3 users, due to confidentially reasons. Denote that directory A. >During year-end all of the linked spreadsheets (except the confidential >ones) are temporarily moved to ANOTHER location on the server so that >10+ users have access to it. Once year-end is over, the files are moved >back. Denote that directory B. Moved or copied? If moved, would the year-end users of the files in B be making any changes to the files originally from A? Would the primary users of the files in A be making any changes to the files while they're in B? In either case pathnames would be a fairly minor concern compared to coordinated usage. If no users would be making changes to the files while they're in B, then you could make COPIES of the files from A in B and furthermore convert ALL formulas to values. Then none of these files would have any links between them in B. If your open to file system trickery, and if your file server is running Windows NT4 or later, any Unix/Linux/BSD variant or Mac OS X or later, you could use a port of the ln command (comes as part of Unix/Linux/BSD and Mac OS X; for Windows, you could download and install the GnuWin32 CoreUtils package, available at http://gnuwin32.sourceforge.net/downlinks/coreutils.php which provides a ln command for Windows). This command can create so-called hard links in multiple directories in the same logical disk partition to the same file in that disk partition. To explain, directories are a special type of file stored on disk. They contain filenames, attributes for those files and POINTERS to the location of the files on disk. Hard links allow file entries in different directories to point to the same location on disk. If your year-end users only need READ-ONLY access to the files in A, and if they have READ-ONLY or greater access to the file server volume (aka share) that contains A, then during year-end processing you could create hard links to these files in another directory in that volume, and your year-end users could access the files in that alternative directory. Further, you could grant year-end users READ-ONLY permission to the files during year-end processing then remove all permission for the rest of the year. You wouldn't even need to remove the hard links; revoking all permissions for these files from the year-end-only users would effectively make the files invisible to them (though still possible to confirm their existence, but not able to read, modify, rename or delete). Harlan Grove wrote... ... >If your year-end users only need READ-ONLY access to the files in A, >and if they have READ-ONLY or greater access to the file server volume >(aka share) that contains A, then during year-end processing you could >create hard links to these files in another directory in that volume, >and your year-end users could access the files in that alternative >directory. . . . ... Just checked. All users would need to have just READ-ONLY access to these files during year-end processing for hard links to work. This is due to how Excel saves workbooks, saving as a new, temporary file first, and if successful deleting the original file (with hard links, that only removes the entry from the directory from which the file was opened - if there were other links to the file, those links and the file on disk would still remain) and renaming the temporary file with the original file's name. Believe me ... from experience with users and networked templates to shared XL dB ... standardize the drive, map them to a common ID.  SignatureRegards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > Our staff uses several spreadsheets, many of which are linked to > SEVERAL other spreadsheets. [quoted text clipped - 15 lines] > > Frank
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.