Wait until query web page display ?

[danial]

Sawasdee,

I created script(macro) for import external data by "New Web Query..."
and I want to calculate data from qry data.
While excel getting data from web(do not display yet), script next step has been run.

How can I create script for wait it ?
I'm try with code

Application.wait

but it not work.

should you have any way for me.
thank a lot.

 

[acw]

Daniel

you need to loop until the readystate = 4

I don't know how you have got to the web, but this may set you in the right direction.

Dim IExp As Object
  Set IExp = CreateObject("internetexplorer.application")
  IExp.navigate "http:yourURL"
'make sure the screen is fully activated
  Do Until IExp.readystate = 4
    DoEvents
  Loop

Tony

 

[danial]

I import data from web query by code below

        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://10.20.206.84/Hrcharter/Job.asp?key=" & PPID, Destination:=Range("A1"))
            .Name = "JobKey"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True '?
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=True
        End With

<recored by macro>

I'm nut sure that I will apply from "IExp.readystate = 4" (how to)
or set something properties

 

[acw]

Hi

Doing it that way, try changing the refresh line to

.Refresh BackgroundQuery:=False

I think that will force the code to wait until the data is extracted.


Tony

 

[danial]

Arh!
I just copy from http://www.mrexcel.com/board2/viewtopic.php?t=234391&highlight=querytable
Many thanks for your advice

 

[MarkAndrews]

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime



???