webquery?

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Talk about timing

Hey NateO,

You replied just in time, I was about to contact you for some help.
As for my macro, most of it came from other posts.

I ran my macro on a different PC today and it didn't work.
Reason: SendKey {TAB}, my code states from the Address bar do 1 TAB and yaddih yaddah...
Well guess what, the IE on this PC has an extra "Search bar" below the Address bar, therefor my 1 TAB lands in the wrong spot!
Instead of landing in the webpage "input box", it lands in the Yahoo search box!
And I thought my code was solid....

I'll try out your macro and see what gives, thanks for you advice and be on the lookout for more Q&A's

Cheers,
Sprucy
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Re: Talk about timing

Hello,
Sprucy said:
Reason: SendKey {TAB}, my code states from the Address bar do 1 TAB and yaddih yaddah...
Well guess what, the IE on this PC has an extra "Search bar" below the Address bar, therefor my 1 TAB lands in the wrong spot!
Yep, would've failed on my PCs too then, I too have an extra tool bar.

Generally speaking, the Sendkeys Method is a nasty hack, sometimes it seems necessary and is functional, but it should be used a last resort.

You're better off learning how to use IE or the Webbrowser Control, in an Object Oriented manner, for reliable, dynamic control. I've posted examples of this a number of times, here, so you might want to search the site.

I hope the example helps. :)
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Can you Help?

Hey NateO,

Your code works great but I can't seem to getting going with my settings...

I have a private intranet page so I can't post-it, sorry.

What do I need to change from the "HorseWebQuery" to make this work.

My "search" value is in A1, sheet2 (only 1 search needed)
Can I specify which table I want copied into excel and how do I know what the table specs are? Do I need "View Code" info from the webpage?

Based on the search, there will be 3 outcomes:
1) 15 Columns by Variable Rows, about 600 to 1200, varies daily.
2) No Records found
3) Another type of error message.

I want my data to go in B9 if 1 is met. If scenario 2 or 3 occurs, I want a msgbox to say ... try later / verify date (cell A1) ... "OK" button, and Stop Macro at this point.

I'll provide more info, let me know what you need to help me out.

Cheers,
Sprucy.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hello,

I'll provide some hi-level advice and let's see if it helps you to help yourself. Here's an overview of what I do when I develop something like this, it can be more an art than a science:

NPO said:
I generally follow the same steps, start with a similar procedure, check out the source, adjust, remove any error traps, make IE visible, then lay out the goal set, rewrite, research, rewrite, test, etc...
{snip}
Now using the HorseWeb query, here's some specific's:

NPO said:
I got all of the site's relevant info from the source, including the form's name, the textbox's name and the JavaScript function. Yep, navigating on that JavaScript calls the function.

The line that has that validation function isn't what tipped my hand, it's the following source lines:

Form:
Code:
 <form name="HorseSearchForm" method="post" action="/workoutHorseSearchResult.do;jsessionid=34022977321134075201094?action=list">
Textbox:
Code:
<input type="text" name="name" maxlength="50" size="22" value="">
What's a little tricky the first time you do this is figuring out which table you want, because they're not labeled and I'm not going to count by reading the source. So, I just loop through all of the tables, scanning some text and using a counter. You don't see this in the final product, because I delete it once I know which table I'm going for... So this code, the way I do it, develops in stages.

How did I know the IE Objects? Well, you could early bind, and have a go with the Object Browser...

In this case I did a lot of searching, reading and testing, (not on Excel, I got that part down for this task...) as I normally do when writing something I'm not as familiar with. Including my example that I linked to in the thread earlier... In fact, my first step was to copy that code into a code module...

That's how I do this/did this, and it varies; it's a fluid, dynamic process.

There is a general reference, and it's to be found at the usual source, MSDN:

http://msdn.microsoft.com/workshop/browser/webbrowser/reflist_vb.asp
{snip}
And, here's an example of how one loops through Tables in an HTML Document:

http://www.mrexcel.com/board2/viewtopic.php?p=357149#357149

Note the 'For Each Table In cTables' loop... I think the collection is 0-based...

And my last piece of hi-level advice:

NPO said:
What I would add, is that when you're automating IE, I find it easier to make IE visible during the drafting stages to actually watch how IE is responding to your code.

Then, when you know it's up and running, let it run in the hidden background.
{snip}
I do this for two reasons: 1) It allows you to watch your automation 2) If you crash, you can easily terminate the new IE instance.

Have a go with the concepts and examples I've mentioned and see what you come up with. My help to you is going to be very limited as I can't see what you're looking at, or test... :)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
I received an interesting PM about the code I've posted to this thread basically asking:
  • "What happens if I bungle this up with a typo? As is, I am prompted with a error msgbox and the querying stops…"
And this is true, written as is. The PM then had the audacity to explain to me that all I need to do is test for an error, to which I says "Er, no, don’t do that…"

What you want to do, in this case, is test the document's text for the string 'No horse was found with the specified name', which is returned on bunk queries. So I'd rewrite the code as such:<font face=Courier New><SPAN style="color:#007F00">'-----------------------------------------------------------------------</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> horseWebQuery()<SPAN style="color:#00007F">Dim</SPAN> ie<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, Table<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><SPAN style="color:#00007F">Dim</SPAN> tblRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>, tblCell<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN><SPAN style="color:#00007F">Dim</SPAN> strArr()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">String</SPAN>, i<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, j<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN>, f<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Long</SPAN><SPAN style="color:#00007F">Dim</SPAN> tmpArr()<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Variant</SPAN>, tmpColl<SPAN style="color:#00007F">As</SPAN> Collection<SPAN style="color:#00007F">Dim</SPAN> qrySht<SPAN style="color:#00007F">As</SPAN> Worksheet<SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN><SPAN style="color:#00007F">CBool</SPAN>(Len(ThisWorkbook.Worksheets( _
    Sheet1.Index).Range("a2").Value))<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">With</SPAN> Application
    .StatusBar = "Please Wait: Retrieving Web Query Results..."
    .ScreenUpdating =<SPAN style="color:#00007F">False</SPAN>
    .DisplayAlerts =<SPAN style="color:#00007F">False</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">Resume</SPAN><SPAN style="color:#00007F">Next</SPAN>
ThisWorkbook.Worksheets("Horse Errors").Delete<SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> 0<SPAN style="color:#00007F">Set</SPAN> qrySht = ThisWorkbook.Worksheets(Sheet1.Index)<SPAN style="color:#00007F">With</SPAN> qrySht
    <SPAN style="color:#00007F">Let</SPAN> tmpArr = .Range(.Range("a2"), _
        .Range("A65536").End(xlUp).Item(2)).Value
    .Range("B1:F65536").ClearContents
    .Range("B1:F1").Value = Array( _
        "Horse Name", "Born", "Sex", "Sire", "Dam")<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><SPAN style="color:#00007F">On</SPAN> Error<SPAN style="color:#00007F">GoTo</SPAN> errHandler<SPAN style="color:#00007F">Set</SPAN> ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://www.drf.com/workoutHorseSearch.do"<SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">While</SPAN> ie.busy: DoEvents:<SPAN style="color:#00007F">Loop</SPAN><SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">While</SPAN> ie.ReadyState<> 4: DoEvents:<SPAN style="color:#00007F">Loop</SPAN><SPAN style="color:#00007F">Set</SPAN> tmpColl =<SPAN style="color:#00007F">New</SPAN> Collection<SPAN style="color:#00007F">For</SPAN> f =<SPAN style="color:#00007F">LBound</SPAN>(tmpArr, 1)<SPAN style="color:#00007F">To</SPAN><SPAN style="color:#00007F">UBound</SPAN>(tmpArr, 1)
    <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">CBool</SPAN>(Len(tmpArr(f, 1)))<SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Let</SPAN> i = 0:  <SPAN style="color:#00007F">Let</SPAN> j = 0
        <SPAN style="color:#00007F">With</SPAN> ie
            .document.Forms("HorseSearchForm").Name.Value = tmpArr(f, 1)
            .navigate "JavaScript:if (fnValidate()) document.HorseSearch<SPAN style="color:#00007F">For</SPAN>m.submit();"
            <SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">While</SPAN> .busy: DoEvents:<SPAN style="color:#00007F">Loop</SPAN>
            <SPAN style="color:#00007F">Do</SPAN><SPAN style="color:#00007F">While</SPAN> .ReadyState<> 4: DoEvents:<SPAN style="color:#00007F">Loop</SPAN>
            
            <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN><SPAN style="color:#00007F">CBool</SPAN>(InStrB(1, .document.body.innerText, _
                "No horse was found with the specified name"))<SPAN style="color:#00007F">Then</SPAN>
                
                <SPAN style="color:#00007F">Set</SPAN> Table = .document.all.tags("table").Item(13)
                
                <SPAN style="color:#00007F">ReDim</SPAN> strArr(1<SPAN style="color:#00007F">To</SPAN> Table.Rows.Length - 2, 1<SPAN style="color:#00007F">To</SPAN> 5)
                
                <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> tblRow<SPAN style="color:#00007F">In</SPAN> Table.Rows
                    <SPAN style="color:#00007F">Let</SPAN> j = 1:  <SPAN style="color:#00007F">Let</SPAN> i = i + 1
                    <SPAN style="color:#00007F">If</SPAN> i > 2<SPAN style="color:#00007F">Then</SPAN>
                        <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> tblCell<SPAN style="color:#00007F">In</SPAN> tblRow.Cells
                            strArr(i - 2, j) = tblCell.innerText
                            <SPAN style="color:#00007F">Let</SPAN> j = j + 1
                        <SPAN style="color:#00007F">Next</SPAN> tblCell
                    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> tblRow
                
                qrySht.Range("b65536").End(xlUp).Item( _
                    2, 1).Resize(UBound(strArr, 1), 5).Value = strArr
                Erase strArr
                
            Else: tmpColl.Add "A" & f + 1 & ": " & tmpArr(f, 1)
            
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
            
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Next</SPAN><SPAN style="color:#00007F">With</SPAN> qrySht
    .Columns("B:F").AutoFit
    .Columns("C:C").TextToColumns Destination:=.Range("C1"), _
        DataType:=xlDelimited<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">CBool</SPAN>(tmpColl.Count)<SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">ReDim</SPAN> strArr(1<SPAN style="color:#00007F">To</SPAN> tmpColl.Count, 1<SPAN style="color:#00007F">To</SPAN> 1)
    ThisWorkbook.Worksheets.Add(after:=qrySht).Name = _
        "Horse Errors"
    For i = 1<SPAN style="color:#00007F">To</SPAN> tmpColl.Count
        strArr(i, 1) = tmpColl(i)
    <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">With</SPAN> ThisWorkbook.Worksheets("Horse Errors")
        .Range("A2").Resize(UBound(strArr, 1)).Value = strArr
        <SPAN style="color:#00007F">With</SPAN> .Range("A1")
            .Value = "Query Errors"
            .Font.Bold =<SPAN style="color:#00007F">True</SPAN>
            .Font.Underline = xlUnderlineStyleSingle
            .HorizontalAlignment = xlCenter
            .EntireColumn.AutoFit
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
    Application.Goto qrySht.Range("a1")<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>

errHandler:
    ie.Quit:<SPAN style="color:#00007F">Set</SPAN> ie =<SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> qrySht =<SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> tmpColl =<SPAN style="color:#00007F">Nothing</SPAN>
    
    <SPAN style="color:#00007F">With</SPAN> Application
        .StatusBar =<SPAN style="color:#00007F">False</SPAN>
        .ScreenUpdating =<SPAN style="color:#00007F">True</SPAN>
        .DisplayAlerts =<SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
    
    <SPAN style="color:#00007F">If</SPAN> Err.Number<> 0<SPAN style="color:#00007F">Then</SPAN>
        MsgBox "Error: " & String$(2, vbLf) & _
            Err.Number & String$(2, vbLf) & _
            Err.Description
        Err.Clear
    <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#007F00">'-----------------------------------------------------------------------</SPAN></FONT>


And, now the following is the query results Worksheet, with the query names in Column A:
HorseWebQuery2.xls
ABCDEF
1Horse NamesHorse NameBornSexSireDam
2FolkloreFolkloreMFolgsamFokkela
3FoobarFolklore3/20/1993MNative ProspectorFantazia
4Point GivenFolklore (GB)2/5/1995MFairy KingFalsoola (GB)
5Bungle in the JungleFolklore (JPN)3/8/1999MDance in the Dark (JPN)Andes Lady
6SensationFolklore2/26/2003FTiznowContrive
7Point Given4/26/2000GApolloKenney Lady
8Point Given3/27/1998HThunder GulchTurko's Turn
9Sensation (GB)4/3/1993MSoviet StarOutstandingly
10Sensation5/9/1994GKey to the MintAzuma Brook (JPN)
11Sensation (ITY)1/4/2002FLate Parade (IRE)Padme
12Sensation1/24/2003FDixie UnionRyn
Sheet1


And, because cells A3 and A5 have invalid queries, a new error worksheet 'Horse Errors' is created, and looks like:
HorseWebQuery2.xls
ABCD
1Query Errors
2A3: Foobar
3A5: Bungle in the Jungle
Horse Errors


I hope this helps. :)
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Hey Nate,

Going crazy with this...

I'm getting "Run-time error 13" type mismatch.

With Sheet2
Let tmpArr = .Range("A1").Value
But when I place the mouse pointer over this line, I get Dec/12/2005 which is good...
****
I've changed a few thing below, but still no luck....
Code:
Sub NewHorseModel()
Dim ie As Object, Table As Object
Dim tblRow As Object, tblCell As Object
Dim strArr() As String, i As Long, j As Long, f As Long
Dim tmpArr() As Variant

If Not CBool(Len(Sheet2.Range("A1").Value)) Then _
    Exit Sub

Application.StatusBar = "Please Wait: Retrieving Web Query Results..."
Application.ScreenUpdating = False

With Sheet2
    Let tmpArr = .Range("A1").Value
End With

On Error GoTo errHandler

Set ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://private intranet..."
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

Sheet2.Range("A2:O2").Value = Array( _
    "#", "Date", "O", "D", "Dd", "A", "On", "P", "F", "G", "D/L", "B-T", "C")

'Sheet2.Range("A2:F65536").ClearContents

For f = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    If CBool(Len(tmpArr(f, 1))) Then
        Let i = 0:  Let j = 0
        With ie
            .document.Forms("Operational_date").Name.Value = tmpArr(f, 1)
            .navigate "JavaScript:if (fnValidate()) document.Operational_date.submit();"
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            
            Set Table = .document.all.tags("table").Item(3)
            
            ReDim strArr(1 To Table.Rows.Length, 1 To 5)
            
            For Each tblRow In Table.Rows
                Let j = 1:  Let i = i + 1
                If i > 2 Then
                    For Each tblCell In tblRow.Cells
                        strArr(i - 2, j) = tblCell.innerText
                        Let j = j + 1
                    Next tblCell
                End If
            Next tblRow
        
        End With
        
        Sheet2.Range("b65536").End(xlUp).Item(2, 1).Resize( _
            UBound(strArr, 1), 5).Value = strArr
    End If

Next

With Sheet2
    .Columns("A:O").AutoFit
    '.Columns("C:C").TextToColumns Destination:=.Range("C1"), _
        DataType:=xlDelimited
End With

errHandler:
    ie.Quit: Set ie = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then
        MsgBox "Error: " & String$(2, vbLf) & _
            Err.Number & String$(2, vbLf) & _
            Err.Description
        Err.Clear
    End If
End Sub
Quote:

Form:

YOUR CODE:
Code:
<form name="HorseSearchForm" method="post" action="/workoutHorseSearchResult.do;jsessionid=34022977321134075201094?action=list">
MY CODE
Code:
<form BOTID="0" METHOD="POST" ACTION="dcc.asp">
Textbox:

YOUR CODE
Code:
<input type="text" name="name" maxlength="50" size="22" value="">
MY CODE
Code:
<input TYPE="TEXT" NAME="Operational_date" VALUE=""
size="20"></font></td>
What am I doing wrong?

Cheers,
Sprucy


Edited by NateO: Disabled HTML and added code tags. You can't just slam a bunch of HTML in your posts, you collapsed the thread.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Hello,

Well, with all due respect, quite a bit...

tmpArr() is an array, you can't just pass a string or a cell's value to it.... You'll need to change the data type declaration. And don't loop through the array, just do it once (get rid of the 'For f' loop).

And you can't use this JavaScript from a different site, to automate your website:

Code:
.navigate "JavaScript:if (fnValidate()) document.Operational_date.submit();"
This is not a universal IE document thing, it's specific to the website the OP wants to interface with. You need to get rid of this.

Now, when I look at your header Array... Does your Web Document's table have 13 columns? Then you can't use an array that's 5 vectors wide, dimensioned right here:

Code:
ReDim strArr(1 To Table.Rows.Length, 1 To 5)
Also:

Code:
.document.Forms("Operational_date")
Is a bogus Form reference, I see nothing in the source code that you've posted to indicate there's a Form with that name. The reason this worked for the original site, is that the form was named, yours is not. You'll need to refer to it by item number, go back to my first link in this post for an example:

http://www.mrexcel.com/board2/viewtopic.php?t=139085

Which item number is beyond me, you'll need to test... I believe this collection is 0-based. Start with 0, not sure what BotId is...

Good hunting. :)
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
wow Nate,

thanks a lot for the code. That is really impressive. I really appreciate you doing this. :)

I just ran the code on my list of several hundred horses (which took me hours to collect the data for), and it only took a matter of minutes with the code, hahaha. Also, the error-checking feature was a great idea, and your way of handling it, generating a separate error sheet is terrific. I just had to try it with a few bogus names, just to see how it works. It works great. This will surely come in handy for the next K-Derby. Excellent work. You are a true master programmer.

Many thanks again. :)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
You're welcome. :)

And thank you for the nice feedback, glad you tried it out and had some success.

Too bad it was a little late, but perhaps it will be of use next year, and hopefully it provides the board a nice working example of an approach to, what I consider, to be advanced, dynamic, Web Querying... I used more white-space than I normally would, in an attempt to break the code out a little... :)

Incidentally, use of white-space is also a bit of an art vs. science, I find. If you have too much, I find code hard to read, but just the right amount can help distinguish your intent by quite a bit, in my estimation...

And, keep in mind that the general technique of looping through dynamic, Web Document Tables and stacking arrays is a good one, along with the appraoch to error-trapping, etc... While site-specific forms, urls and other code semantics need to be maintained by site, and perhaps changed with changes to existing sites.

These procedures are a challenging to build, I find, but you can have some fun doing so, if you enjoy this type of work... :)
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
Still working on this one....

Hi Nate,

Alright, I really like your Horse query, but I still can’t get it going with my data.

My webpage is basic, you will find in the following order:

Table 1: which shows “Date”
Input box: where the date goes, *to be retrieve from cell A1, sheet3.Test
2 buttons: Submit & Reset
Table 2: the table I need to copy into excel, which has a Header (15 columns) that is always visible as a Template.

After pressing Submit, Table 2 will return about 600-800 rows of data.

Ex:
Table 2 (Before pressing Submit)
Date Time 3 4 5 6 7 …up to 15 columns. (No data below this line)

After:
Date Time 3 4 5 … 15
01JAN2006 1400 in out next
01JAN2006 1430 in out next

and so on for the next +/- 600 rows.

The code below is the closest I got to getting Table 2 into excel, but, this only retrieves the Template.
I need to submit the date from A1 into the Input box, then have Table 2 copied into excel.
Any ideas?

Basically, I need a cross from your Horse code and this one?
Like I’ve said before, I have the “View Source” info if you wish.

*************************

'Retrieve USD exchange rates using a Web Query
'By ?? got this from "www.mrexcel.com" forums
Sub GetRatesWithWebQuery()


Dim oBk As Workbook
Dim oQT As QueryTable


'Store the current settings of Excel's number formatting
Dim sDecimal As String
Dim sThousand As String
Dim bUseSystem As Boolean


'Create a new workbook
Set oBk = Workbooks.Add


'Create a query table to download USD rates
With oBk.Worksheets(1)
Set oQT = .QueryTables.Add( _
Connection:="URL;http://www.x-rates.com/d/USD/table.html", _
Destination:=.Range("A1"))
'Set oQT = .QueryTables.Add( _
Connection:="URL;http://my.private.webpage", _
Destination:=.Range("A2"))
End With


'Set the QueryTable's properties
With oQT
.Name = "USD"


'State that we're selecting a specific table
.WebSelectionType = xlSpecifiedTables


'Import the 5th table on the page
.WebTables = "5"
'.WebTables = "2" ' I need to import Table 2


'Ignore the web page's formatting
.WebFormatting = xlWebFormattingNone


'Do not try to recognise dates
.WebDisableDateRecognition = True


'Don't automatically refresh the query each time the file is opened
.RefreshOnFileOpen = False


'Waiting for the query to complete before continuing
.BackgroundQuery = True


'Save the query data with the workbook
.SaveData = True


'Adjust column widths to autofit new data
.AdjustColumnWidth = True
End With


With Application
'Remember Excel's current number format settings
sDecimal = .DecimalSeparator
sThousand = .ThousandsSeparator
bUseSystem = .UseSystemSeparators


'Set Excel's separators to match those of the web site
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True


'Ignore any errors raised by the query failing
On Error Resume Next


'Perform the query, waiting for it to complete
oQT.Refresh BackgroundQuery:=False


'Reset Excel's number format settings
.DecimalSeparator = sDecimal
.ThousandsSeparator = sThousand
.UseSystemSeparators = bUseSystem
End With


End Sub

***********

Cheers,
Sprucy
 

Forum statistics

Threads
1,081,556
Messages
5,359,555
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top