Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Downloading a File from the Web with VBA (2000 SR1)

    Hello,

    I am currently assisting with the development of an Excel Workbook for my Firm that will be using worksheets that can be imported into the Workbook on Command. The Challenge is to keep the most current version of the worksheet available to the user.

    My Idea is to post the worksheets to an Intranet Website for download. However, the users cannot be relied upon to check the website and download manualy, so I need to automate. I need to accomplish two objectives.

    1) The worksheets will be downloaded and stored on the Users computer, So I need to compare the files on the hard drive to the ones on the website to make sure that they are in sync.

    2) I need to automaticaly download the ones that are not in Sync to the directory where the sheets will be stored, overwriting the old version.

    If anyone can offer assistance, it will be much appreciated.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Downloading a File from the Web with VBA (2000 SR1)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Mark

    OK lets see what I can help you with here.

    When you say: <<< worksheets that can be imported into the Workbook on Command >>> do you mean that you will move the worksheet into the workbook, so now it will have worksheets.count + 1? How about simply linking to the worksheet instead of having to copy/move the worksheet into the workbook. This can be accomplished via worksheet functions, and you can control when and how they get updated via VBA.

    When you say: <<< The Challenge is to keep the most current version of the worksheet available to the user. >>> You mean there are multiple users working on this worksheet and then you want to have the last changed saved, or there is a select small group, maybe a single user who does that work?

    When you say: <<< My Idea is to post the worksheets to an Intranet Website for download. >>> You mean you can have a centralized location of the latest version. Could that be a simple volume on a shared server, actually I think it is the same thing, if you know the path to that location where the worksheet is. Then you can link to it.

    When you say: <<< However, the users cannot be relied upon to check the website and download manualy >>> You can have the VBA code test to see if the worksheet is there, and then either update automatically or ask the user to do so. This is easy when you use the FileSearch Object.

    <<< The worksheets will be downloaded and stored on the Users computer, So I need to compare the files on the hard drive to the ones on the website to make sure that they are in sync. >>>

    I suggest you always download the one on the server, unless users make their own changes to these. This way all you need is to check the date and then do something. If they are in sync then no hard is done but some time was wasted, and if they are not in sync, well after the download they will be.

    So as you see, two options reduced to one.

    I suggest you write your own formulas to link to that file, and then save the values and continue. This can be done via VBA.

    Hope this helps.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Downloading a File from the Web with VBA (2000 SR1)

    Thanks for your response, here is some more info to help clarify.

    Here is the set up for the entire

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Downloading a File from the Web with VBA (2000 SR1)

    Mark, even after your explanation, I'm confused. It seems like you want to time limit some files but not others, and the logic isn't clear

    What about a hidden datestamp in each version of the file, and an auto open macro which compares the hidden date to the system date, and locks the WB if more than 30 days have elapsed? The hidden datestamp can be updated via code when certain conditions are met, such as importing an updated sheet or add-in ... ?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Downloading a File from the Web with VBA (2000 SR1)

    Hi again,

    I think that I am clouding the water with my explanations.

    Basically, I need to know if I can download files from an Internet website using VBA without any response from the user.

    My Apologies for not explaining my dilemma very well.

    Thanks,

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Downloading a File from the Web with VBA (2000 SR1)

    Mark

    OK to simply download a file to a PC from a web site, I think you will need some functionality that is similar to what the HyperLink does, but the HyperLink opens the file but does not download it, so if opening it, and then sucking all the worksheets you need is OK, try it.

    If you still need to download, I guess an FTP protocol could do this.

    <font color=red>Mind you </font color=red> that with Hyperlinks, the workbook calling the hyper link gets hidden, and you have to make it come back again, by making MS-Excel think that it is not yet saved.

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Downloading a File from the Web with VBA (2000 SR1)

    Mark,

    I think your best solution would be to use FTP software, pulling all of the workbooks into the local machine. From there you can use VBA to select which workbooks on the local machine the user can open or sheets to display. I am currently using Primasoft's AutoFTP Pro on a dial up connection. It has a scheduler already built in that seems to work wonderfully - I shedule all of my maintenance download after midnight. I believe that AutoFTP Pro can also schedule a download only if the datestamp on the file has changed or if it is (never knew you could put four two letter words together and make sense) a more recent file (not entirely sure though).

    Another note however, unless you are using broadband, some workbooks can get very large and that can take some time. My solution, albeit a somewhat different problem, was to create one workbook for everyone. Key elements of this workbook are imported from text files and used in lookup tables, i.e. inventory prices, number of managers per location, etc. Using the lookup tables and the imported text file data I can control <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> what information is displayed at each location. The text/control files are what I upload and download from the server. I also receive text files on my end that contain daily sales, food cost, marketing results, etc.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Downloading a File from the Web with VBA (2000 SR1)

    If the file has a direct link, maybe you can simply use the workbook.Open method with it's link as the argument? It does work that way when opening webpages, dunno about files.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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