Solved

Automate File Download from Internet Explorer using VBA

Posted on 2013-05-15
12
1,889 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 
LVL 47

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 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