Link to home
Create AccountLog in
Avatar of rsburge
rsburgeFlag for United States of America

asked on

Automate File Download from Internet Explorer using VBA

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
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rsburge

ASKER

Thank you!  I will see if I can make this work.  I will post back later today with the results of my testing.
Avatar of rsburge

ASKER

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.
Can you post the results of the debug.print commands?
Avatar of rsburge

ASKER

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.
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
Avatar of rsburge

ASKER

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!
Avatar of rsburge

ASKER

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.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of rsburge

ASKER

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.