HTML Object Libray: Essentially Creating a Script to Pull Personal Finances From Online Bank Account

[pawest]

Hello VBA and HTML Object Library coders,
I have a cool personal project (at least I think it's cool). I am really into tracking my personal finances and I am using GnuCash to manage all my money and bank accounts as if it were a real business. If interested, GnuCash is free and available at Free Accounting Software | GnuCash ... there is a ton of support behind this gnu project and I've learned more from reading their accounting manuals than I did in my college courses!

Anyways, back to the project. I'm essentially trying to create a script via VBA (don't know any scripting languages) to pull my .QIF banking transaction history every week and I'll manually assign it to the general ledger. With the code that I currently wrote, I'm able to login to my 5/3 account, navigate to the export history window, but I get stuck at trying to choose the right account and enter the dates.

Here are three sites I'm using as references for my code:
VBA Express : Excel - Log In to an Internet Site
Reading web data with VBA from excel using Microsoft HTML Object Library and Microsoft Internet Controls | Menelaos Bakopoulos
InternetExplorer Object

Here's the code I have so far:

'Make sure Microsoft HTML Object Library and Internet Controls are selected from references

Sub IE_login()
    Dim ie As InternetExplorer
    Dim C
    Dim ULogin As Boolean, ieForm
    Dim myPass As String, myUser As String
    Dim historyDate As Date
     
    myUser = "XX loginusername XX"
    myPass = "XX mypassword XX"
    
    historyDate = Now
    historyDate = Format(historyDate, "mm/dd/yyyy")
    
    'Attempting to open IE and login
    Set ie = New InternetExplorer
    ie.Visible = True
    ie.navigate "https://www.53.com/site"
     
    'Loop until ie page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
     
    'Look for password Form by finding test "Password"
    For Each ieForm In ie.Document.Forms
        If InStr(ieForm.innerText, "Password") <> 0 Then
            ULogin = True
             'enter details
            ieForm(0).Value = myUser
            ieForm(1).Value = myPass
             'login
            ieForm.submit
            Exit For
        Else
        End If
    Next
    

    'Wait 7 seconds for good pracitce
    Application.Wait DateAdd("s", 7, Now)
    
    'Loop until online banking page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    
    'Attempt to navigate to Export files site
    ie.navigate "https://www.53.com/servlet/efsonline/accounts-export.html"
      
    'Wait 7 seconds for good pracitce
    Application.Wait DateAdd("s", 7, Now)
    
    'Loop until online banking page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    
    ' *************************************************
    ' *************************************************
    ' *****    The rest of the code is where     ******
    ' *****       I beging to have issues        ******
    ' *****    I don't know what to do with      ******
    ' *****      the textfields and dates        ******
    ' *************************************************
    ' *************************************************

    'Look for history From by finding test "Account"
   For Each ieForm In ie.Document.Forms
        If InStr(ieForm.innerText, "Account") <> 0 Then
            
            For Each ieForm.Selectedlist In ieForm
             
                ULogin = True
           
                If ieForm(0).Value = _
                "XX checking account XX" Or _
                "XX savings account XX" Then

                    ieForm(1).Value = historyDate - Day(7)
                    ieForm(2).Value = historyDate
            
                ieForm.submit
                
                    ' then I would have more code here to process a file download
            
                End If
            
            Next ieForm.Selectedlist
    
        End If
    Next  
End Sub

To see what the export history online account interface looks like, go to a google image search and search for "ib‑sc‑internet‑banking‑1.jpg" ... I'll try to insert the picture here:

I was having issues pasting the source code from the website. I can post this later if that would be helpful to figuring out this project (which it seems like it would be). Thanks for your consideration to help!

 

[pawest]

Norie, very impressive yet again. I am able to get the code to process all the way through to downloading the QIF file! However, IE creates a popup to save to the file (expected). How do I get it to "save"?

I presume you know what I'm talking about, but here's what the screen looks like:

If possible, I'd like to be able to click on the Save arrow and click the "Save As" link and save it to my Accounting folder:

Here's the code I have so far that's working but not capable of clicking through the popup menus:

    ' create reference to page's document
    Set doc = ie.document
    
    ' grab reference to account dropdown
    Set cmbaccountselected = doc.getElementsByTagName("select")(0)
        
    cmbaccountselected.Value = "XX account number XX"
    
    Set txtStartDate = doc.forms(0).all("Export.StartDate")
    
    txtStartDate.Value = "08/01/2012"
    
    Set txtEndDate = doc.forms(0).all("Export.EndDate")
    
    txtEndDate.Value = "08/24/2012"
    
    'set and click the next button
    Set lnkNext = doc.forms(0).getElementsByTagName("A")(3)

    lnkNext.Click
    
    'Export History page should load...
    'Wait 7 seconds for good practice
    Application.Wait DateAdd("s", 7, Now)
    
    'Loop until online banking page is fully loaded
    Do Until ie.ReadyState = READYSTATE_COMPLETE
    Loop
    
    Set lnkNext = doc.getElementsByTagName("A")(18)

    lnkNext.Click

 

[Norie]

The pop-up(s) can't be controlled in the same way as the webpage.

You should be able to use the Windows API for this stage.

 

[pawest]

I tried to do use the windows api to complete my project. Here's the code I found and tried to modify:

Option Explicit  Private Declare Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long  Dim Ret As Long  Sub Sample()     Dim strURL As String     Dim strPath As String      '~~> URL of the Path     strURL = "http://spreadsheetpage.com/downloads/xl/king-james-bible.zip"     '~~> Destination for the file     strPath = "E:\Users\Siddharth Rout\Desktop\king-james-bible.zip"      Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)      If Ret = 0 Then         MsgBox "File successfully downloaded"     Else         MsgBox "Unable to download the file"     End If End Sub</pre>

Unfortunately, this part at the top:

Private Declare Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Is red... so I'm assuming I haven't imported the windows URLDOWNLOADTOFILE api...
how do I go about doing that??
If that's not the problem, do you have any other suggestions
</pre>

 

[Norie]

That isn't the sort of Windows API I meant.

If you could use that then you wouldn't need any of the other code, all you would need to know is the full URL of the file you want to download.

 

[pawest]

Good to hear.

Still wondering why I can't get the "Private Declare function... " section to work...
I believe I need to find the URLDownloadToFile api?

 

[Norie]

Not if you have Windows installed.

 

[pawest]

I wish I was proficient at linux and could do without a windows partition! lol

I have windows installed... not sure why I can't get it working...

 

[pawest]

I've made minor progress since my last post... here's what I'm running into:

This code...

Option Explicit
Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Dim Ret As Long

Sub Sample()

Dim strURL As String
Dim strPath As String

'~~> URL of the Path
strURL = "http://spreadsheetpage.com/downloads/xl/king-james-bible.zip"

'~~> Destination for the file
strPath = "E:\Users\Siddharth Rout\Desktop\king-james-bible.zip"

Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

If Ret = 0 Then
    MsgBox "File successfully downloaded"
Else
    MsgBox "Unable to download the file"
End If

End Sub

...returns a message that states:

"Compile error: the code in this project must be updated for use in 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute."

How do I handle this?

 

[pawest]

wow that was easy enough... all i had to do was write PtrSafe before Declare like this:

Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, _
ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

hopefully, I can get this script running consistently and efficiently!

 

[Norie]

I don't think this approach is going to work for your purpose.

You don't know the name or URL of the file to download.

It might not even have a URL as it might not be a file stored in a particular location, it could be created on the fly.