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

Topic

New Reply

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.

Reply | Quote

Replies

Mark

OK lets see what I can help you with here.

When you say: <<>> 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: <<>> 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: <<>> 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: <<>> 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.

<<>>

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

Reply | Quote

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

Here is the set up for the entire

Reply | Quote

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 … ?

Reply | Quote

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,

Reply | Quote

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.

Mind you 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

Reply | Quote

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 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.

Reply | Quote