FollowHyperlink

[chungtinhlak]

Every morning, I have to go out to a site and save a bunch of Excel files (18 of them). The links are really long so I used FollowHyperlink, these links are not a direct links to the file, it's encrypted. In another worth, they don't end in .xls.

Example of link:
https://cogentprod.web.company_name.com:443/cbi1/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=view&ui.object=defaultOutput%28%2fcontent%2ffolder%5b%40name%3d%27787%20Producti on%20System%20Reports%20and%20Metrics%27%5d%2ffolder%5b%40name%3d%27Shop%20 Floor%20Information%27%5d%2ffolder%5b%40name%3d%27Special%20Report%20Views% 27%5d%2ffolder%5b%40name%3d%27QA%27%5d%2ffolder%5b%40name%3d%27Scheduled%20 Report%27%5d%2freportView%5b%40name%3d%27EPD%20Backlog%20Report%20Morning%2 7%5d%29&ui.name=EPD%20Backlog%20Report%20Morning&ui.format=singleXLS"]https://cogentprod.web.company_name.com:443/cbi1/cgi-bin/cognosisapi.dll?b_action=cognosViewer&ui.action=view&ui.object=defaultOutput(%2fcontent%2ffolder%5b%40name%3d%27787%20Production %20System%20Reports%20and%20Metrics%27%5d%2ffolder%5b%40name%3d%27Shop%20Fl oor%20Information%27%5d%2ffolder%5b%40name%3d%27Special%20Report%20Views%27 %5d%2ffolder%5b%40name%3d%27QA%27%5d%2ffolder%5b%40name%3d%27Scheduled%20Re port%27%5d%2freportView%5b%40name%3d%27EPD%20Backlog%20Report%20Morning%27% 5d)&ui.name=EPD%20Backlog%20Report%20Morning&ui.format=singleXLS

I was able to use followhyperlink but a messagebox pop up asking me to save, open or cancel. How do I automatically save it everytime in a certain directory with the same name everytime?

thanks

[Oorang]

I've had pretty good luck with this (make sure you change the file name and URL):
Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub Example1()
DownloadFile "http://www.google.com", "c:\Test\goog.txt"
Shell "notepad.exe ""c:\Test\goog.txt""", vbMaximizedFocus
End Sub

Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
'Thanks Mentalis:)
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

[chungtinhlak]

Oorang,

thanks for your help, this would work except for the website that does this requires and WSSO logon, this there a way to input this information in automatically.

[Oorang]

Ah well that is a little more tricky:) Do you know the type? Is it using your windows logon?

Also just as a general point: Automating logons is such a bad practice that it is almost always a violation of IT policy in a corporate environment. The reason is that to automate a logon you have to store your credentials somewhere either in the code itself or a file or wherevever. Now you are responsible for keeping said data secure. And now you time-saving aplication is the weakest link and because that logon is probably used in multiple systems, it's compromise can cascade into other areas. This is, in a word, bad. If you want to automare the download process I suggest that you at minimum have an account create with no rights at all, but the minimum needed to read (not event write) that one location. And as a CYA I would make sure that you are clear on your intended usage when you ask for it, that way if it comes up later you can say you did full disclosure.

[omnibuster]

Oorang

If i need download WebSite -to Excel - ActiveWorkBook??
Is this possible make changes in this code like this:
Option Explicit
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Public Sub Example1()
DownloadFile "http://www.google.com", "ActiveWorkbook.Sheet1...."

End Sub

Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
'Thanks Mentalis:)
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function

[Oorang]

Sorry I don't think it is. You'd have to download the file then open it with Workbooks.Open