+ Reply to Thread
Results 1 to 2 of 2

Code to look at external link and if not found, return error or blank

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    UK
    MS-Off Ver
    Excel 2007
    Posts
    19

    Code to look at external link and if not found, return error or blank

    Hi,

    I've got a spreadsheet that is built on external users filling in manifests for vessels and storing them in a day-of-month folder (so one day can have say 13 files, depending on how many ships sail). If a vessel doesn't sail, then a file is not saved, but in my spreadsheet it lists and links to the file as if it was there (my file has one tab per vessel which has data brought in from the vessel file - if it exists). When opening up the file I get presented with "there are a number of sources that have not been found" (for the vessels that have not been manifested), but what I want to do is to avoid that and have some code that looks to see if that vessel file exists, and if it does not, then return an error or blank. At the moment all of the 'last available version' of the vessel file is being used which distorts the figures.

    Can anyone help?

    Thanks!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Code to look at external link and if not found, return error or blank

    Without a sample, I can only give a general outline. I'll assume that your directory structure is something like .../Sailings/YYYYMMDD. So in your spreadsheet, you would have a formula that looks at Today's date and creates a directory string for the appropriate day.

    Then use VB to traverse the directory to get the file names (ship names?) and create the formulas based on what it finds. I'd have to know what cells you are looking for. Hopefully they will be in the same positions all the time.

    Put this code in an open workbook event.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. External Link - #REF Error
    By shawnr72 in forum Excel General
    Replies: 1
    Last Post: 08-05-2015, 03:38 PM
  2. Replies: 2
    Last Post: 09-29-2014, 01:14 AM
  3. [SOLVED] External link cannot be found resulted in many warnings messages
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-04-2014, 06:13 PM
  4. [SOLVED] Return blank if no exact match or blank is found
    By matrixpom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2013, 12:57 AM
  5. [SOLVED] External Link error
    By Vikesh Jain in forum Excel General
    Replies: 1
    Last Post: 06-16-2006, 03:00 AM
  6. [SOLVED] #N/A External Link Error
    By Steve C in forum Excel General
    Replies: 1
    Last Post: 03-07-2006, 06:50 PM
  7. [SOLVED] #N/A External Link Error
    By Steve C in forum Excel General
    Replies: 0
    Last Post: 03-07-2006, 01:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1