Solved

Automate File Download from Internet Explorer using VBA

Posted on 2013-05-15
12
1,722 Views
Last Modified: 2013-09-22
Hello - I am using existing VBA in Access 2007 to download a file from IE.  This code has been working perfectly for a few years.  Recently, the website changed their source and now the file is not always in the same location.  It is now in a table and I can't figure out how to get the tag for the proper file.

The file is no longer always associated with the same tag; some days it is (1) and some days it is (0) and some days it is (2) and so on.  I always need the csv file.

Below is the section of the code that had been working but needs changed.  I am not able to provide the login to the site as the information is confidential.  However, I have included attachments with what the webpage looks like and the source.  Hopefully, this provides enough information.  I have highlighted the file I need.

I am a beginner in VBA and didn't write this original code, so I need a lot of help writing the code.

Thanks!

    Set hdoc = IE.Document.frames(1).Document '.frames(1).Document '.Table(0).Document '.body.Document.frames(0).Document.Table(0).Document
    Set linkCol = hdoc.all.tags("a")
    tm = 0
    str = "RateSheetGrid_View_0"
TrySecondOption:
    For Each link In linkCol
        If InStr(link.ID, str) > 0 Then
            'debug.print link.href
            rng = Right(link.href, InStr(link.href, "javascript:PopUpFileViewer(") + 8)
            'debug.print rng
            rng = Left(rng, InStr(rng, ");") - 1)
            'debug.print rng
            urlXls = "https://www.stmpartners.com/Private/ResourceCenter/FileViewer.asp?FileID=" & rng
            GoTo DnldFile
        End If
    Next

Open in new window

IE-Automation-WebPage.PDF
IE-Automation-Source.pdf
0
Comment
Question by:rsburge
  • 7
  • 2
12 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39173630
Okay, I'm not sure if this will work.  I'm just winging it.  But if it doesn't, maybe it will give you some ideas.
   
    Dim tableRows As Object
    Dim tRow As Object

    Set hdoc = IE.Document.frames(1).Document 
    Set tableRows = hdoc.all.tags("tr")
    For Each tRow in tableRows
        If tRow.InnerHtml Like "*Corr Rate Sheet - csv*" Then
            Set link = tRow.tags("a")
            'debug.print link.href
            rng = Right(link.href, InStr(link.href, "javascript:PopUpFileViewer(") + 8)
            'debug.print rng
            rng = Left(rng, InStr(rng, ");") - 1)
            'debug.print rng
            urlXls = "https://www.stmpartners.com/Private/ResourceCenter/FileViewer.asp?FileID=" & rng
            GoTo DnldFile
        End If
    Next

Open in new window

0
 

Author Comment

by:rsburge
ID: 39174414
Thank you!  I will see if I can make this work.  I will post back later today with the results of my testing.
0
 

Author Comment

by:rsburge
ID: 39188982
Hi.  I have tried all week to make some variation of this work, but no go.

I can get the exact text I need but still can't figure out how to either click the corresponding link or get the href from the corresponding link.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39189766
Can you post the results of the debug.print commands?
0
 

Author Comment

by:rsburge
ID: 39189797
It actually can't locate any href, so I changed that to innerhtml and it prints the source code.

The site isn't showing anything in the table this late at night, so I will have to get the data for you tomorrow once the page is updated.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 39246051
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Comment

by:rsburge
ID: 39246052
I'm still trying to work this question.  I am very busy as I am the only person in the office right now who can work on this.  The problem is I can only get the requested information during certain times of the day and lately, the market has been changing so frequently, I haven't been able to get stable information.  I would really prefer you not close out this question.

Thank you!
0
 

Author Comment

by:rsburge
ID: 39493205
I apologize for the long delay.  I am still trying to make this work and am testing again this week.  We had a major project due and I was not able to work on this for the last weveral weeks.
0
 

Assisted Solution

by:rsburge
rsburge earned 0 total points
ID: 39501896
I was finally able to get this to work by changing this line...
From this -
       Set link = tRow.tags("a")

To this -
       Set link = tRow.all.tags("a")
0
 

Author Closing Comment

by:rsburge
ID: 39512750
I included my own comment as the code won't work properly without it.  

I do not want any points for the answer, just to include that it is necessary to change that line for the code to work.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now