Copy and paste non-HTML web page contents into Excel sheet using VBA

Hi there, I'm trying to get data from a web page into Excel using VBA code. The data is not in HTML format. Initially the page shows up with one line of table data, which expands upon clicking a button. This seems to be done via a script.

The URL method works fine for "https://www.holidaymoney.com.au/nab/daily-exchange-rates.html" which has tabular data in HTML.

Help would be greatl appreciated!

    
    Dim ws As Worksheet
    Dim web_cba As String
    web_cba = "https://www.commbank.com.au/personal/international/foreign-exchange-rates.html"
    ThisWorkbook.Worksheets.Add
    Set ws = ActiveSheet
    
    With ws.QueryTables.Add(Connection:="URL;" & web_cba, Destination:=ws.Range("A1"))
        .Name = "foreign-exchange-rates_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TablesOnlyFromHTML = True
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "7"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Open in new window


Alternative methods (WinHttpRequest and MSXML2.ServerXMLHTTP) both time out and provide no data at responseText.

    Dim Req As New WinHttpRequest
    Dim RT As String, HasData As Boolean
    Dim Doc As New HTMLDocument
    Req.Open "GET", "https://en.wikipedia.org/wiki/Main_Page"
    Req.send 'TIMES OUT HERE
    RT = Req.responseText
    Doc.Clear
    CallByName Doc, "Write", VbMethod, RT
    MsgBox Doc.body.innerText

Open in new window


and

    Dim obj As MSXML2.ServerXMLHTTP
    Dim strData As String
    Dim errCount As Integer
    
    ' create an xmlhttp object - you will need to reference to the MS XML HTTP library, any version will do
    ' but I'm using Microsoft XML, v6.0 (c:\windows\system32\msxml6.dll)
    Set obj = New MSXML2.ServerXMLHTTP
    
    ' Get the url - I set the last param to Async=true so that it returns right away then lets me wait in
    ' code rather than trust it, but on an internal network "false" might be better for you.
    obj.Open "GET", "http://www.google.com", True
    obj.send ' this line actually does the HTTP GET
    
    ' Wait for a completion up to 10 seconds
    errCount = 0
    While obj.readyState < 4 And errCount < 10
        DoEvents
        obj.waitForResponse 1 ' this is an up-to-one-second delay
        errCount = errCount + 1
    Wend
    
    strData = obj.responseText
    
    If obj.readyState = 4 Then ' I do these on two
        If obj.Status = 200 Then ' different lines to avoid certain error cases
            strData = obj.responseText
        End If
    End If
    
    obj.abort  ' in real code I use some on error resume next, so at this point it is possible I have a failed
               ' get and so best to abort it before I try again
    Debug.Print strData

Open in new window

turtlejamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EirmanChief Operations ManagerCommented:
You could try an entirely different approach ....

SnagIt is a screen capture program that scans long webpages (or regions of webpages) by scrolling.
It will perform OCR and give you a CSV file output which you can import into Excel/Access.
https://www.techsmith.com/snagit.html
0
turtlejamAuthor Commented:
Wow I'll give it a go thanks. There's still the problem of the initial requirement to cluck on a link so the page contents expands. The URL remains the same during this process.
0
turtlejamAuthor Commented:
So snag it is not an option. I don't know how to use it, it's not free so the org won't pay for it (yes it's cheap but then there's the question of licensing and so on). There has got to be a way scraping the data off the screen: essentially copying and pasting the page. Is snag it really the only option?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

EirmanChief Operations ManagerCommented:
I got this from your link using a free OCR service

Give me a link you are having difficulty with
CaptureX.xls
0
turtlejamAuthor Commented:
Hi Eirman, really appreciate your help! I'll check out the spreadsheet you sent with the OCR.

The link I'm having difficulty with is "https://www.commbank.com.au/personal/international/foreign-exchange-rates.html"

Could you please take a peek at the first code snippet I provided? That code itself runs but retrieves nothing. It works for HTML sites but not for scripts.

Thanking you again,
turtlejam
0
EirmanChief Operations ManagerCommented:
I'm not really a coder  ...... In fact you way ahead of me, so I can't help you there.
If you want help with your code, click on the "Request Attention" and a moderator call in other experts.
My Capture/Scan/Convert method is just a laterally thought out alternative solution.
======================================================================
I could make an excel sheet for a single currency in your second link in less than 2 minutes.
Each sheet would have 7 columns and about 23 rows.
No software purchase required  ..... There is scrolling screen-capture freeware available.
SnagIt would better as it does the scanning+OCR

Are you looking for one click solution for all currencies?
Exactly what output do you require?
0
turtlejamAuthor Commented:
Hi Eirman,

The output required is shown in the attached spreadsheet, similar to what you provided in CaptureX.xls. Any superfluous text is easily removed. Can your capture/scan/convert method provide this output? I need to consider the "free" approach first as the company won't agree to paid methods.  

Thank you,
turltejam
Commbank-Data.xlsx
0
EirmanChief Operations ManagerCommented:
I'm really puzzled now. Your sheet provides your solution.
What more do you need? Am I missing something?

I did a similar EURO sheet using a cut/paste method >>>>>

To select just the data I wanted, I clicked just to the left of the word Currency ...
Scrolled to the list .....
Hold down the Shift key and Click just to the right of Dong value
Right click over Blue highlighted text and Copy
Open a blank excel worksheet and Paste
Tidy up a few columns and add a little bit of colour.

OCR was unnecessary
Euro.xlsx
0
turtlejamAuthor Commented:
HI Eirman, the sheet I provided contains the data that I manually copied and pasted. I'm looking for a way to automate that process into a button click. The code I originally presented using the URL method is able to do this for HTML-based sheets but not for scripts that call in the data at runtime.
0
EirmanChief Operations ManagerCommented:
I understand now fully your requirments turtlejam.
At least my method of copying the webpage leaves out most the superfluous stuff.
0
turtlejamAuthor Commented:
Thanks Eirman,

Can you share the code of your method? If it can work with the specific commbank website in question, that would be awesome. The issue is not with the superfluous stuff but with the fact that I can't get ANY data out of the commbank website using the URL method. I don't mind which method I use (provided it's VBA based only and not requiring purchase of third party software). If you have the solution, can you please send me the code?

Thank you
turtlejam
0
EirmanChief Operations ManagerCommented:
As I said, I can't help you with the coding.
If you want help with your code, click on the "Request Attention" and a moderator will call in other experts.
(Or perhaps ask another question in the Excel zone)

I used a simple 30 second copy and paste method (described 4 posts ago)

I don't think I can be of any more help to you.
0
aikimarkCommented:
That page really doesn't like IE, especially older IE versions.    You might need to use Selenium for your browser automation.

If it works on the first URL, why not use it?
0
turtlejamAuthor Commented:
Hi Aikimark,

What do you mean, if it works on the first URL why not use it? The URL code works on a webpage that contains HTML. It does not work on the Commbank webpage. If you are able to make the URL method (or any other method) work on the Commbank page specifically, please do send it to me. The link is "https://www.commbank.com.au/personal/international/foreign-exchange-rates.html".


Our company approved browsers are IE 9 and Firefox 29.0.1 only. I can try to see if Selenium will install, but can you help me work out which download to use?
0
aikimarkCommented:
I was unable to get the second URL (javascript) to work either.  My question to you is why use this javascript site, when you know an HTML site?
0
turtlejamAuthor Commented:
Because they provide different information. I need the commbank rates that are provided only on the javascript page.
0
RobOwner (Aidellio)Commented:
Hi turtlejam,

Firstly it's not possible the way you are doing it because javascript needs to run in order to load the rates and VBA doesn't do that (or might with a lot of pain and effort).

So let's bypass that for a sec.  The commsec page loads two lots of data using javascript:

https://www.commbank.com.au/content/dam/commbank/xml/mcc/currency.masterCurrencyList.json?dt=1430349917247
https://www.commbank.com.au/content/dam/commbank/xml/mcc/currency.AUD.json?dt=1430349917247

The first is a list of countries and currencies in JSON format (more of that in a sec)
The second is the actual rates, also in JSON format.
At the end of each URL you will see a variable "dt" with a long number.  This is the date at which you want the rates (at a guess that's what it's used for).  This is the number of milliseconds since 1 Jan 1970, which is different to the VBA calculation of dates.  AT THIS STAGE TRY WITHOUT IT AS IT SEEMS TO WORK :)

JSON is JavaScript Object Notation, which is just a fancy way of sending data back and forth in an encapsulated way.  I will not claim to have done this in Excel but I can say that a quick search (I can't post a link to the site for copyright reasons) for "Parsing JSON with VBA" will result in a lot of results.  Essentially once you've included that code you would call a simple function such as parseJSON and pass the data you've received from the links above.  I suspect you'll be left with either an array or Dictionary object such that you can access the properties and rates.

Once you've got this far, post back and I'll be able to help you through this part.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
turtlejamAuthor Commented:
Wow, thank you Rob Jurd. I am going to give that a go and get back to you.
0
RobOwner (Aidellio)Commented:
Not a problem - i'm monitoring :)
0
turtlejamAuthor Commented:
Trying to post a response - not working
0
turtlejamAuthor Commented:
Hi Rob,

We did it! I couldn't find an easy JSON parsing method so did it slowly through VBA but it works perfectly. Code below!

My only questions is where did you get the JSON address from? Can't find the link in the source code or anywhere:

https://www.commbank.com.au/content/dam/commbank/xml/mcc/currency.AUD.json?dt=1430349917247
0
turtlejamAuthor Commented:
Code uploading doesn't seem to be working right now.

Rob, could you please tell me how you discovered the JSON web address? Still can't work it out :)

Thanks!
0
turtlejamAuthor Commented:
Hi Rob,

We did it! I couldn't find an easy JSON parsing method so did it slowly through VBA but it works perfectly. Code below!

My only questions is where did you get the JSON address from? "The commsec page loads two lots of data using javascript..." Can't find the link in the source code or anywhere:

"https://www.commbank.com.au/content/dam/commbank/xml/mcc/currency.AUD.json"

My code below for reference:
Sub get_CBA_data()

    Dim ws As Worksheet
    Dim web_cba As String
    Dim lastrow As Long
    Dim i As Long
    Dim tmpString As String
    ThisWorkbook.Worksheets.Add
    Set ws = ActiveSheet
    
    web_cba = "https://www.commbank.com.au/content/dam/commbank/xml/mcc/currency.AUD.json"
    
    With ws.QueryTables.Add(Connection:="URL;" & web_cba, Destination:=ws.Range("A1"))
        .Name = "foreign-exchange-rates_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TablesOnlyFromHTML = True
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "7"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    
    'code to parse the data into legible format
    ws.Range("A1").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="{"
    
    ws.Rows("1:1").Copy
    ws.Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    ws.Rows("1:1").ClearContents
    ws.Range("A3").ClearContents
    
    ws.Columns("A:A").TextToColumns Destination:=ws.Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False
    

    lastrow = ws.Range("A50000").End(xlUp).Row
    ws.Range("A3").Value = "Currency Title"
    ws.Range("B3").Value = "Currency Name"
    ws.Range("C3").Value = "Bank Buys IMTs"
    ws.Range("D3").Value = "Bank Buys Foreign Cheques"
    ws.Range("E3").Value = "Bank Buys Cash & Travel Cheques"
    ws.Range("F3").Value = "Bank Sells IMTs & Drafts"
    ws.Range("G3").Value = "Bank Sells Cash & Travel Chequeus"
    
    
    For i = 4 To lastrow
        ws.Range("A" & i).Value = Mid(ws.Range("A" & i).Value, 16, 3)
        tmpString = Replace(Mid(ws.Range("B" & i).Value, 15, 100), """", "")
        ws.Range("B" & i).Value = tmpString
        tmpString = Replace(Mid(ws.Range("C" & i).Value, 7, 100), """", "")
        ws.Range("C" & i).Value = tmpString
        tmpString = Replace(Mid(ws.Range("D" & i).Value, 19, 100), """", "")
        ws.Range("D" & i).Value = tmpString
        tmpString = Replace(Mid(ws.Range("E" & i).Value, 14, 100), """", "")
        ws.Range("E" & i).Value = tmpString
        tmpString = Replace(Mid(ws.Range("F" & i).Value, 7, 100), """", "")
        ws.Range("F" & i).Value = tmpString
        tmpString = Replace(Mid(ws.Range("G" & i).Value, 17, 100), """", "")
        tmpString = Replace(tmpString, "}", "")
        ws.Range("G" & i).Value = tmpString
        
    Next i
    
End Sub

Open in new window




turtlejam
0
turtlejamAuthor Commented:
Awesome response and approach to solving the problem!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
CSS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.