Pausing a macro...

[wildpianist]

Hey guys!

I use code to pause my code for 1 second using


Application.Wait Now() + TimeValue("00:00:01")


But is there anyway of pausing it for say half a second or even quarter of a second?

Thanks for your help ;)

[lucas]

I don't have a solution but how could a half of a second make that much difference?

[wildpianist]

I'm not actually sure it would, I'm using SendKeys to browse to a page on the web copying and pasting the data then pasting it back into excel, one of the pages I need to tab 25 times, I've tried to find out the actual address of that page but unfortunately its all javascript based and it won't help :(

I do know there is a wait bit at the end of SendKeys but I dont know if that pauses (x) amount of seconds/milliseconds until it moves on to the next line of code :)

[lucas]

is the data in a table on the web page?
The following looks at a .php page....should work for you too if your data is in a table.
Option Explicit
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.vbaexpress.com/kb/default.php", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "2" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

[wildpianist]

That would be great, but the button that loads the webpage goes to a link like javascript '11111' So thats why I use SendKeys, it might be worth contacting the guys who made the site and see if they can give me links to the database that it logs all the details on each night lol :)

[wildpianist]

Only problem is the page isn't in PHP. The link is something silly like (javascript '11111') its basically a reports viewer and uses Active X controls and I shudder to think what else. Thats why I use SendKeys to open the link in IE and tab through, it might be worth getting hold of the guys who wrote the stuff and ask them where the data is held and see if I can get it into Access or something :(

[lucas]

The address in this code is not just an html or php page either but it still retreives the table contents ok....did you try it?
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;https://login.yahoo.com/config/mail?.intl=us", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

It's entirely possible that I am out of school on this but I just think there must be a better way than sendkeys all the way. Maybe use the sendkeys until you get to the page and then retreive the table contents.

[Paul_Hossler]

You can call the Sleep API, but make sure that you have DoEvents before you call it just in case



Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'pass the amount of time (in milliseconds)
Sub TakeNap(timelength As Long)
Sleep (timelength)
End Sub


Paul

[grichey]

What is the below supposed to do? I copied into excel but didnt look like it did anything at all...



is the data in a table on the web page?
The following looks at a .php page....should work for you too if your data is in a table.
Option Explicit
Sub gethtmltable()
Dim objWeb As QueryTable

Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.vbaexpress.com/kb/default.php", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "2" ' Identify your HTML Table here
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

[lucas]

It returns the recent entries from this page:
http://vbaexpress.com/kb/default.php

But there are no recent entries so it just returns the header for the table which is "Recent Entries" to Cell A1

try the attached

If you change the 2 to a 3 in this line it will retrieve the most helpful entries table from that page.


.WebTables = "3" ' Identify your HTML Table here

[grichey]

ah, I gotcha. Thanks. If only I could get it do this through a bunch of pages now..