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.

 

[joelnichols]

When I use a macro to pull the info it looks like so:

      Range("B5").Select
    ActiveSheet.Paste
    Range("B4").Select

Note the source of the info is blank as its copied from IE. So a line of code that would make IE the source of "copy" would be the key I think.

As this has now taken up 2 days worth of effort I would gladly resort to e-mail or phone calls to resolve. I have free long distance in the U.S.

 

[Legacy 98055]

Hi Joe.

There are a variety of ways to scrape data from the web. Unfortunately, the code is going to vary because each webpage is unique and many webmasters are doing they're best to make it more and more difficult to allow users to gather data automatically (ie. using script). A few tips to help you out. First of all use Object.Quit to close internet explorer. Setting the visible property to false will not close ie but leave a hidden instance running. See process IEXPLORE.EXE in task manager to prove this. To make it easier to code, use early binding. This will provide you with intellisense tips. This is optional.

Set a reference to "Microsoft Internet Controls"

Replace these two lines:

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

With:

Dim ie As New InternetExplorer


I prefer to use Microsoft's WinHTTP library and then parse the HTML. You may also access the source HTML using IE but it is not neccesary. I edited your code. Perhaps itwill work for you. Don't forget to set a reference to Microsoft Internet Controls.

Sub myWebOpenPW()

Dim ie As New InternetExplorer

'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"))


'Select All and then Copy to the clipboard
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DODEFAULT
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DODEFAULT

Range("B5").Select
ActiveSheet.Paste
'use pastespecial if you only need the text
Range("B4").Select


End Sub

Tom

 

[joelnichols]

Edited:

Erased another post that made me look dense

 

[joelnichols]

Read a few other posts and found one that led me to the set reference problem. Thanks for your help. It works perfect now.

Also I found IE.quit to close the browser. Thanks again for your time and help.

 

[joelnichols]

Tom you mentioned parsing the HTML and I didnt know what you meant until I looked thru all my worksheets. I have a ton of hidden HTML in there. How do you parse it? Or does that have to be done manually?

Also could this be causing a new problem i'm having..like making a macro run 3 times in a row. When my timer activates it logs me in 3 times and copies the page 3 times.

 

[joelnichols]

Bump

Here's my new problem heh. I have 4 macro that I made that are attached to buttons. When i run each macro from the button they run perfect. BUT, when i run the macros from a auto timer they each run 2 or 3 times in a row before shutting off. Any ideas why this happens?

And how do I get rid of dozens of hiddenHTML() in my project sheets.
If i'm not clear please let me know what I need to add.
Thanks in advance

 

[Legacy 98055]

Parsing HTML using the DOM. The same thing Excel does when using a web query. Sometimes the web query does not provide what I want so I parse the HTML myself if I can and place the values I need directly into cells. Try PasteSpecial with the code I provided you with earlier and then refer to the cells containing the info you want from another worksheet.

 

[joelnichols]

So whats the deal with all the HTMLhidden files in my project sheets? There are now over 120. Looks like every time I run the macro to pull the info it adds another HTML to my project page. Example:

I run the macro for sheet 1 called "Bank". Under the project explorer in the general area of sheet1 "bank" there are dozens of entries Like these

  Private Sub HTMLCheckbox41_Click()

End Sub

Private Sub HTMLHidden40_Click()

End Sub

Are these just showing HTML that is currently on that page?

And anyone with an idea as to why the macros run once from a button but 2 or 3 times from a timer please holla