query code

[joelnichols]

I know this isn't a new topic but after 30 mins of reading thru old post I figured as simple as this is I would get the answer much faster with a new post

I have code that open IE and gets me to the page I want (PW protected). I just need to simply add the code to refresh the query that will be pulled after I get to that page of the site. I know you guys can get this in your sleep but it is over my head as usual.

 

[Asala42]

Can you get away with just:

ActiveWorkbook.RefreshAll

 

[joelnichols]

Ok got that part worked out thx. Is there a line of code to close the IE that my macro has opened?

 

[joelnichols]

Well I thought I had my problem fixed but it was only temporary. It seems the page that is session timed has it's name changed according to day/time format maybe? I have to create a new query link daily because the page link changes. Any idea how to get around that? The code I have gets me to the correct place to pull the query, but the query itself fails because it pulls from the previous page link.

Also can anyone tell me code to close the IE after the query is done?
thx

example of query 12/1

https://www3."sitename".com/tf/Trac?tx=TracPortfolio&cz=3120518&tidx=4176817416c8107886febe9521690443942m3$418334784108283918508970&scr=portfoli&start=true&parent=menu

and 12/2 query

https://www3."sitename".com/tf/Trac?tx=TracPortfolio&cz=3120518&tidx=734748d69de107edd448a8-7d8e9142542n5$216339479014150481799595&scr=portfoli&start=true&parent=menu

 

[joelnichols]

ADVERTISEMENT

Bump + new idea.

Even tho I can get to the right page in IE my query info is timed out. How can you make the macro target (or focus on) the currently open page and copy that? Then close that page

I don't know many VBA cmds but maybe something like

'Set IE as target' for example?

 

[joelnichols]

Found that "ie.visible = false" closes the ie. Still cant figure how to "target" or "focus" on ie to copy info.

Anyone?

 

[Richard Schollar]

ADVERTISEMENT

Joel

I have never played around with IE from within Excel I'm afraid so I am no expert and the following may not work at all.

try accessing the current URL by assigning the following to a string variable (here strURL), say:

strURL = IE.LocationURL

This isn't tested, but I would hope it would return the URL address of the current IE window to the variable.

Hope this may have proved useful.

Richard

 

[joelnichols]

Again this has me lost . Where would this URL be returned to?
I have the page with the info right there in the IE but I just cant figure how to capture it. It's as simple as copy and paste at this point.

 

[Richard Schollar]

Joel

Apologies - i think i misunderstood. i thought you wanted the address of the URL not the data contained within it. Can you use the macro recorder to record the data and see what the resultant code looks like?

Richard

 

[joelnichols]

Here is my current code. Please advise.

Sub myWebOpenPW()

Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
ie.Navigate "https://www.mysite.com"
Do
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do
Else
DoEvents
End If
Loop

Application.Wait (Now + TimeValue("0:00:3"))
'MsgBox "Done"
ie.Visible = True

SendKeys "{TAB}", True
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "{TAB}", True
SendKeys "name", True
SendKeys "{TAB}", True
SendKeys "password", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:1"))
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
Application.Wait (Now + TimeValue("0:00:1"))


Application.Wait (Now + TimeValue("0:00:10"))






End Sub

At this point I need to pull the info from the currently open page. I can do that 2 different ways now but it only works until the page timer expires and this info will be pulled everyday.