VBA Help needed

[cwalner]

Hey All,

I am hoping there is somebody on this forum experienced enough with VBA (Access) to help me.

What I am trying to do is this:

Write VBA code so that I can press a button in access that will access a web site, press on a link that generates an Excel file, and then save that file to a specific location (which the database already has set up to import as a linked table).

I can currently access the website and press the button for the link, but then I encounter a problem.

The link opens up a dialog box that offers to Open (O), Save (S) or Cancel (C). I want to use the VBA code to simulating chosing Save (S) then entering the path and file name to save the file to a specific location.

I am using Office 2007, and the access database is 2007, but I wish to save the files as Excel 2003 (.xls rather than .xlsx)

Any tips that you can offer would be greatly appreciated.

 

[GodMark2]

Can you eliminate the middleman? Why click a button to load a web page and have the human select a link when you could just have the button download from where the link leads directly?

The Inet control should be able to to this for you.

 

[cwalner]

Not quite working.

I get runtime errr '429': ActiveXcomponent can't create object

on the line:
Set HTTP = New Inet

I double checked and the library is added. The code was copied verbatim from the site you linked to. Not sure why it is hanging up on creating the new Inet object.

 

[bobhope2112]

Using VBA to do this will ultimately lead to disappointment. I think GodMark2 is on the right track, but I would probably try to work around the inet control as well.

Is this link that you're clicking on something I can take a look at, or does it require a login? Depending on the url of that link, it might be possible to write a short program that just retrieves the file identified by that url, without any of the other complications.

 

[cwalner]

Using VBA to do this will ultimately lead to disappointment. I think GodMark2 is on the right track, but I would probably try to work around the inet control as well.

Is this link that you're clicking on something I can take a look at, or does it require a login? Depending on the url of that link, it might be possible to write a short program that just retrieves the file identified by that url, without any of the other complications.

unfortunately, its part of my company intranet, and requires a login.

one concern about getting the file without interaction is that the URL is constant, despite the file actually changing depending upon parameters. I don't know how the website accomplishes this, but I need to pull 5 files from the site every day. Each file has a distinct URL from the other 4, but stays the same each day, despite using a different data parameter entered in previously.

 

[GodMark2]

unfortunately, its part of my company intranet, and requires a login.

one concern about getting the file without interaction is that the URL is constant, despite the file actually changing depending upon parameters. I don't know how the website accomplishes this, but I need to pull 5 files from the site every day. Each file has a distinct URL from the other 4, but stays the same each day, despite using a different data parameter entered in previously.

If the server's storing session information to do that, the Inet control probably wouldn't be able to help anyway.

As to the error, it's most likely a bad install of some component that the msinet.ocx uses. It gets installed by a lot of different programs, and some of their uninstallers are a bit aggressive. Reinstalling MSOffice should fix that error.