using VBA to copy xls file from online source

Hi everyone,

I need to copy an excel downloadable file from an online source and was wondering if anyone can guide me on this. The file I need is online as an xls file. Clicking on the link prompts a download/save. I am using VBA through Excel on this.

Mary

You may want to look into the Microsoft Internet Transfer Control. I have it on my system as an Insertable ActiveX Control, but I have never used it, and do not know much about the Control. I can at least provide a little background information about the Control, and you can look into it further if you think it suits your needs.

The Internet Transfer Control allows you to implement 2 of the most widely used Protocols on the Internet, HyperText Transfer Protocol (HTTP) and File Transfer Protocol (FTP).

With HTTP, this Control allows you to connect to World Wide Web Servers to retrieve HTML Documents. With the FTP Protocol, you can log on to FTP Servers to download/upload files. The UserName and Password Properties allow you to also log on to private Servers that require authentication. You can also connect to public FTP Servers and download files.

Common FTP commands, such as CD and GET, are supported via the Execute Method of this Control. It seems as though it may have the programmatic interface to accomplish what you are looking for, but only you'll know that once you look at it. Good luck and let me know how you make out.

I'm getting the old you don't have a licence message. Can you remember how to get around that.

I'm getting the old you don't have a licence message. Can you remember how to get around that.

Thanks for looking at this. I've actually found a very simple way of doing this.

If anybody has a nicer method I'll be glad of any suggestions but this one seems to work fine.

Thanks for looking at this. I've actually found a very simple way of doing this.

Expand|Select|Wrap|Line Numbers

1. Dim wb As Workbook
2.  
3.     Set wb = Workbooks.Open("http://www.apxgroup.com/marketdata/gasuk/xls/ukwdbm_sapsmp_data.xls")
4.     wb.SaveCopyAs "C:\Path\ukwdbm_sapsmp_data.xls"
5.     Workbooks.Close
6.  

If anybody has a nicer method I'll be glad of any suggestions but this one seems to work fine.

I don't think you can get much simpler and cleaner than that, Mary. Sorry for going off on a tangent, I was under the assumption that you wanted to automate the process of downloading a file from an online source onto your HD. I have this unique tendency to over-complicate things. See you around.

I don't think you can get much simpler and cleaner than that, Mary. Sorry for going off on a tangent, I was under the assumption that you wanted to automate the process of downloading a file from an online source onto your HD. I have this unique tendency to over-complicate things. See you around.

Not a problem. I probably didn't explain myself very well. Newbie error :)