VBA help from someone?

[selenay]

I'm hoping that someone on my f-list may be able to help out with this.

There are some Excel spreadsheets on a password protected website that I need to download and save each week. I have a username and password but I will not be the person who has to run this procedure each week. I'm hoping that there is some way, using VBA, that I can open and save (or just extract without opening...) the spreadsheet as part of a macro.

I was using the Workbook.Open method a couple of days ago and passing in a password, which worked perfectly. However, this morning I've run the code without changing anything and it's downloading blank workbooks even though I have checked on the site and the workbooks are nicely populated.

I am assuming that something has been changed on the server that is preventing what was working on Wednesday.

Does anyone have any bright ideas or am I going to have to install an FTP program (like WGet) on my target users' computers in order to get this file? In which case, it's probably going to be easier to get them to log into the website manually and download because persuading IT to install software is almost entirely impossible. I want to make this thing as simple as possible! One button to rule it all is my mantra with this project.

Any help that anyone can give would be much appreciated.

I wish there were a VBA community on LJ *sigh*

Comments

[gmul.livejournal.com]

There may be a flaw in this cunning plan but you could probably get your VBA to shell out to the built-in ftp program, whether it's to a pre-existing batch file or one that your VBA builds especially each time. Then you should be able to grab the spreadsheets to a location of your choice... Are they zipped up or just plain XLS?

[wimble.livejournal.com]

Err, have you tried just telling it to open a file, and putting the file path as an ftp path?

For example, if I start word, open a file, and paste ftp://ftp.mirrorservice.org/pub/readme.txt into the filename field, I promptly get a "Username or Anonymous" dialogue box. I pick anonymous, and everything's fine.

Obviously, this needs fine tuning a bit, if anonymous access isn't allowed, and you don't want the end user to be presented with that dialogue. ftp://user:password@host/path/file is a fairly common construction.

[selenay]

Thanks for the suggestion. I've been trying it for the last little while but the server apparently doesn't want me to have FTP access - doesn't matter whether I chose anonymous access or input the username/password combo. I can only assume that, although they're happy for us to have manual login access, the username/password doesn't cover FTP access. I'll continue looking - gmul may have an idea - but if I don't have FTP access...

Guess the users may have to put up with a tiny bit of manual intervention. Grrr.

[wimble.livejournal.com]

Oh. I see. I think.

It's a file you access by navigating through a website. Not a file on an FTP server. Your reference to an FTP program threw me.

In that case, you may still be able to use the URL of the file, complete with username:password in the URL.

This will only work if you've got authentication in the web server, rather than in the application. ie. do you get a popup box from your broswer itself, or is it a generic web page which happens to have a form in it, and you're only allowed onward once you submit the form correctly?

If it's the latter, you need at least two communications with the server: the first supplies the credentials, and the server responds with a cookie, or similar. And then for the second one, you supply the cookie, and the server responds with the data file you requested.

In that case, I can write a short bit of Perl to do it, but I can see there being issues over that :) If you can code it in VBA (I've no idea!), then it has to do exactly what I've outlined in the previous paragraph.

[selenay]

I think the confusion is that a colleague's first suggestion was "we could try to FTP the files..." and everything went downhill from there. Grr.

Authentication is from a generic web page with a form in (https) so I'm assuming that we're talking cookies. And I have absolutely no idea how to write anything in VBA to deal with this. As VBA is the only tool we're allowed...

I'm currently figuring that, as I have a deadline for end of the week for all this work, it's going to be easier if I just include instructions on manually downloading the file each week rather than trying to be really clever with this thing. Frustrating, yes, but possibly less likely to fuck up totally when I pass it on to whoever gets to run this thing regularly. I certainly don't want to force them to go through the IT helpdesk to get any additional programs installed to handle the download because that's just completely pointless *sigh*

Thank you for your help, though. At least now I can articulate (in teeny short words) to my boss why I can't write something to automatically pick up the files in the time allotted with the restrictions placed on tools and so on. Yay.