1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

opening file from CD in excel

Discussion in 'Business Applications' started by TECnoid, Jan 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. TECnoid

    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?
     
  2. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    Joined:
    Nov 28, 2005
    Messages:
    63,227
    First Name:
    Chuck
    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.
     
  3. gyclone

    gyclone

    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!
     
  4. TECnoid

    TECnoid Thread Starter

    Joined:
    Jan 17, 2011
    Messages:
    2
    Many thanks!
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/975260

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice