opening file from CD in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

TECnoid

Thread Starter
Joined
Jan 17, 2011
Messages
2
Looking to create a CD containing an excel file which will open other PDF files also contained on the CD in separate folders.
Using the hyperlink the drive letter is part of the link which would not work if the CD ROM is not the same letter on the user computer.
Anyone have any help for this situation?
 

cwwozniak

Chuck
Moderator
Joined
Nov 28, 2005
Messages
68,748
Hi and welcome to TSG.

I was able to do what you want using MS-Excel 2003 on a computer running Windows XP. The behavior may be different for other versions of Excel or Windows. The default behavior of Excel is to use relative locations for linked files. The starting location for the relative path is where the Excel file is being saved. If the PDF files are on another drive than the Excel file, the path will start with the drive letter.

There may be a quick solution if all of the linked PDF files are in one folder on the drive or in sub-folders inside of one main folder. Use "Save as..." to save a copy of the Excel file to this top level folder. All of the links should automtically readjust to show just the correct relative path without the drive letter. Do not just copy the Excel file to the other location.

You should then be able to burn a CD of the contents of that top level folder folder to CD.
 
Joined
Jan 16, 2011
Messages
119
Assuming you can use a macro-enabled workbook, this is pretty easy to accomplish (I just had to do the exact same type of thing for a friend).

Ideally, the PDF's should all be in one folder that is in the same folder as the Excel file, but you can adjust for multiple folders, as well. The idea here is that, each time the Workbook is opened, Excel will rewrite all the links, based on the current location of the Excel file, using the "ThisWorkbook.Path" object. The real beauty is that you won't have to worry about your users moving the files off of the CD, to another drive, etc. As long as the location of the PDF's stays relative to the location of the Excel file, the links should always work. The drawbacks are that the users must have macros enabled and, if this is a really enormous project, the macro could take a couple of minutes.

Note: This code should go under "ThisWorkbook" in the Excel Objects lists in the VB Editor.

Code:
Private Sub Workbook_Open()

ThisWorkbook.Sheets("Sheet1").Activate
    
    Range("a6").Activate ' 1st cell in column containing file names
    Do Until ActiveCell.Value = ""
        
        ' get file name
        Dim fl As String
        fl = ActiveCell.Value
        
        ActiveSheet.Hyperlinks.Add Anchor:=Range(ActiveCell.Address), _
        Address:=ThisWorkbook.Path & "\PDF_Folder\" & fl, _
        TextToDisplay:=fl

    
        ActiveCell.Offset(1, 0).Select
    
    Loop

End Sub
I hope that helps!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top