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.

macro to check for updated file

Discussion in 'Business Applications' started by pthom14, Oct 5, 2010.

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

    pthom14 Thread Starter

    Joined:
    Oct 5, 2010
    Messages:
    6
    Once a month, research releases a new downloadable excel file with stock ratings. I would like to have a macro in my stock analysis worksheet that will prompt me to download the new data when I open the workbook if a new file is available. The catch is that it is a large file, and I don't want it to download until the user asks it to update. I already have the macro written to download and process the file, but it doesn't check for a newer version.

    Part of the url for the file is: EC%5FUniverse%5F2010%5FSeptember%2Exls.
    I would like an alert to pop up when the file called EC%5FUniverse%5F2010%5FOctober%2Exls is available. Any advice?
     
  2. pthom14

    pthom14 Thread Starter

    Joined:
    Oct 5, 2010
    Messages:
    6
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    pthom14, welcome to the Forum.
    You should not need to try and find a Month Name embedded in the File's name. You should be able to use the File System Object to return the date the File was last Accessed or maybe the date created.
    You use it like this
    variable = FileDateTime(File location)
    Where File location is the Folder path to where the document file is located.

    You can use it in conjunction with the FileSearch object.
     
  4. pthom14

    pthom14 Thread Starter

    Joined:
    Oct 5, 2010
    Messages:
    6
    Thanks OBP. I'm not sure this applies to what I'm trying to do. I'm trying to test if the current month's file is available for download from the Internet yet or not. I would like the macro to return true if the file is available, false otherwise. Do you know of something similar for a website?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Will the older file still be there or does this one replace it?
    Is the rest of the URL the same in each case?
    If so you could store the URL for the current file and compare it to the one on the website.
     
  6. pthom14

    pthom14 Thread Starter

    Joined:
    Oct 5, 2010
    Messages:
    6
    The old file no longer exists and the rest of the URL does stay the same. I know what the URL will be. How do I tell if it is available without loading the whole file?
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I also found this which opens a web page, I would assume that if the web page does not exist you will get an error message, you could then use erro trapping to stop any attempted download.
    Application.Workbooks.Open ("http://www.geocities.com/johnske100/TheFINDfunction.html")
    This extrac is from this link
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=295
     
  10. Sponsor

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/954433