Close Window
Free Ebook
Get Your Free Excel
ebook!
Top 15 Excel Tutorials from TeachExcel.com
Instant Access!
E-mail:

Vba To Control Web Page And Scrape Data

Self-taught Excel VBA noob here.
I have scowered the internet far and wide to get to the point I'm at right now - which is stuck.

Through VBA I can launch Internet Explorer, load a web site, enter my search string into a field on the screen, click the web site's "Submit" button, and post the data of the resultant data table into an Excel Worksheet using a Web Query.

Now I need to select the next page on the web site and have been unable to do so.
Here's the web site: / /> Enter the first two letters of your last name in the search field and hit enter.
A page will load with the results. At the bottom right of the screen you'll see the hyperlinks to the other pages of the table. What is the code in VB to select those pages?

I've tried this, and it doesn't work:
URL = "/ & cycle & " "
IntExplApp.Navigate URL
Do While IntExplApp.Busy
Loop

Also, if there's an easier way of collecting the table data than a web query I would love to know.

Thanks in advance!
Chris


Free Excel Courses



Similar Excel Tutorials


You can have multiple tabs of data on a single UserForm and this allows you to, effectively, have multiple pages of ...

This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...

How to include different information, headers, or footers, on the first page when printing an Excel spreadsheet. S ...

How to print pages in Excel with different headers for odd and even pages. Steps Go to the View tab and click the P ...


Helpful Excel Macros


- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web

- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a

- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat

- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi

Similar Topics







Hello,

I'd like to be able to copy data from a list of websites into Excel. It would take too long to navigate to do an Excel web query for each site, so is it possible to write a macro that could do that?

Thank you!

ML


Hi,

Im working with a large data spreadsheet that has Yes and No answers in different columns. There are blanks within the selection too.
I need to calculate only the Yes.
I have tried using the filters in the Pivot Table field list and they are not working.
So now i'm trying to add a COUNTIF formula in the Calculated Fields section.
The formula I'm using is =Countif('Time limit extension'="yes") and i get the answer "too few."
What should I be using in the formula to get the calculated field to work? I have search the whole forum and I haven't found an answer that can help me.

Please HELP!!!


Hi Excel experts,
I am working on a worksheet, and would like to have an entire row transferred to another worksheet based upon a certain cell's value.

Having searched through the many solved threads on this site, I attempted to customize some of the code to my worksheet. Not being proficient in Macro/VBA, I failed. I figured I would ask for help, and see if anyone can provide what I am looking for.

Basically, I am attempting to construct a "work in process" file, and would like all completed work to be deleted from the work in process sheet, and transferred to a sheet that contains a running list of all work completed.

Attached is an example of what I am working on. I would like Column Q (Status) to determine if a row can be moved to a sheet that contains the list of all work completed, and eliminated from the original sheet. Once Status is updated to "Complete", it would be helpful to be able to run a macro/click a button that would perform the transfer.

Can anyone assist?

Thanks!


Hello,

Firstly i haven't used excel to a great extent since my college days. So i'm having to re-learn 99.9% of everything i once new...

I am volunteering for a non profit organization and trying to create a reservation system for the rooms that they have - kind of like hotel software, but in excel (i did a similiar thing in college but for plane seats)

Please find it attached.

What i need it to do:

Copy all of the info from the main page to the guest lit (a new row each time).

From the guest list to the Gannt chart - i did a few tutorials on dynamic gantt charts using conditional formatting but cannot get them to work when based on data on a different worksheet.

Finaly is there a way to check for availabilty on any given date?

if just someone could point me in the right direction, i would be appreciate it so much words can't express!!!

Best Regards,

Jamie

P.S for the calender drop down on the main page, i'm using 'microsoft time & date picker 6.0)

/ - pop ups on this site

/ - same file, better website

I'm trying to use VBA to go to a website that requires a User Name, Password, and a Submit Button.

So far I can get everything to work besides the Submit part. The code runs without errors, but doesn't actually "hit" the submit button on the webpage.

For posting, I removed my actual user name and password and and used the generic " User Name " and " Password " highlighted in blue.

I highlighted another section in green that I took from a previous post hoping it would solve my problem. The link is: /


Here is the code I am using:

Sub GoToWebSiteAndPlayAroundNew()

Dim appIE As Object ' InternetExplorer.Application
Dim URL As String


Set appIE = CreateObject("InternetExplorer.Application")
URL = " / "


With appIE
.navigate URL
.Visible = True

Do While .busy: DoEvents: Loop
Do While .ReadyState 4: DoEvents: Loop

.document.getelementbyid("fUserName").Value = " UserName "
.document.getelementbyid("fPassword").Value = " Password "

End With


On Error Resume Next
x = 0
For Each mitem In IE.document.all
mitem.Value = "x"
x = x + 1
Next

x = 0
For Each mitem In IE.document.all
If x = "Submit" Then
mitem.Click
Exit For
End If

Next

End Sub


Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!


Hello, I am pretty new to excel programming/messing around so I'll refer to those who are much wiser than I am. I am trying to set up a form for work, in this form I want to have a page with a bunch of cells pre-sized. What I want to do is when I insert a picture from a job site, that when this picture gets inserted, it will automatically resize to fit within the cell. I don't have time to manually resize dozens of pictures and if I could get this to work it would be awesome! I tried to search for an answer using the search function, but I kept getting a fatal error about allowable memory? Thanks in advance for you help!


Hi everyone, this is my first post here. I consider myself a more advanced Excel user and can navigate my way around fairly well. I've written a spreadsheet and am wondering if there is a way to permit data entry in a cell, contingent upon another cell being populated.

In simple terms, you can't enter data in cell X, until cell "Y" has been populated with something.

Can anyone help with this, because I can't figure it out....


I automating a task at work that will save some time. Seems simple enough but for some reason the code I have to cause IE to wait unil ready doesnt seem to work in all cases.

Im using :

'wait until the page loads
Do While ie.Busy Or ie.readyState READYSTATE_COMPLETE
DoEvents
Loop

This doesnt seem to work well enough, since actions are occuring that should NOT occur until the browser is ready.

Any other ideas as to how to make IE wait until the page is completely ready? I wondered about possibly checking the .innerHTML to verify that a unique page element is present.


Thanks,

Scott


How can I stretch a spreadsheet to fit the page? If I try to make it one page by one page, it changes nothing. If I change the percentage, it becomes two wide...I am just trying to change the height so that it can be read on an 8.5 x 11 piece of paper.


I added a print button to my userform

Code:

Private Sub btn1_Click()
UserForm1.PrintForm
End Sub


This prints out the userform as I would like, however it autoprints to the default printer and doesn't allow any printing options so I can't select to "print to one page". So as of now it is only printing out half my form and cutting off the rest.

Any suggestions?


I've been using Excel for years and had very few issues. However, I recently went into a spreadsheet to update it and was unable to select and enter data into an individual cell. When I click on a cell and try to enter #s nothing happens (my num lock is on). Then when I try to click into another cell it just highlights that cell, along with any other that I move my cursor over. Once I click on one cell I can't stop the highlighting from happening. I can't even click on anything in the toolbar. I am extremely confused, can anyone help?


Hi All,

Random question i have a large Excel Workbook (which is protected) and has over five sheets on it - however one sheet has randomly decided not to scroll... yes i know sounds random! If i use the cursor and down arrows the selection just disappears off the screen.
The page will scroll if i filter by one field, but not if i select (All) for all filters.
All other worksheets scroll fine.
Any suggestions?

Thanks in advance.
Cheers Kaite


Hi All,

I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.
Code:

Sub DoBrowse1()
 Dim ie As Object
 Set ie = CreateObject("Internetexplorer.Application")
 ie.Visible = True
 ie.Navigate "google.com"
End Sub


Any suggestions..


Hey there,

I have been tasked with introducing userforms into an excel sheet and tbh I'm quite amazed that excel has this capability of adding userforms to excel sheets.

Anyway, I have 2 columns of data in an excel sheet and I wish to add this to a userform so that the userform displays the 2 columns beside each other with headings, like a table. The user should then be able to select a particular row and insert it into the specified cell.

I would also like the user to select a row on the table and then be able to bring up another table depending on the row selected...basically so that the user can draw deeper into the information that they require.

I have an example excel sheet where I have 2 sheets. One sheet is the user entry sheet called User Entry Screen. the next sheet is the tables sheet where my tables are held. Once the user selects the cell shown in the example sheet, it should then bring up the user form. the user then, depending on which item clicked, then gets shown the next window with a table and info on it. then user should then be able to select an item and the cells on the user entry screen would then get populated.

Personally I think this is a really tricky challenge and any help with doing this would be extremely appreciated.

I'll post up further comments as I am trying to work my way through it!

Thanks,

Jag


Hi all,

I have written some code that when a button is selected will Refresh a Data Query Table and all of it's information. However, I seem to be getting issues, can some one point me in the right direction with the code?

Code:

 
.Sheets("Sheet 1").ListObject.QueryTable.Refresh BackgroundQuery:=False


When I recorded myslef doing this process it looked like this:

Code:

 
Sheets("Sheet 1").Select
Range("D70872").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False


Many thanks!


Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter


I have searched and read all the help files. I find the properties of
an object, I see how I can "lock", "size and move with cells" or "not
move with cells". No matter what I select, the object moves off the
screen, when the user, scrolls to the right of the spreadsheet.

Is there a way to lock the position, let's say, in the upper right
corner and have it stay there?

This would be quite useful for an EXIT button, that I have created,
that will close the program without saving (it's a read-only file.)

Thanks to all the wonderful people here that have been so helpful and
give us their valuable insight and time.

Jo




I would like to copy a small table from Word into one cell in an Excel
worksheet. The first column of the table is a list of numbers. I tried
converting the table into text with manual line breaks and tab stops to
divide columns and rows, but that didn't solve my problem.

Excel pastes the data into several rows. When I try to merge them, I
get a warning that the selection contains multiple data values, and
merging into one cell keeps the upper-left most data only.

What I tried that didn't work:
* Formatting the Excel cells as text before pasting the data.
* The various options for "Paste Special." The closest I got was
inserting the table as a Document Object, which could be a workaround,
I guess.
What I am saving for when all else fails:
* The obvious solution of copying row by row into one Excel cell.

The data in the table is information about my dad's medications. I
would like to have reference charts of how to identify the strength of
each tablet by its color and markings. I got the info from the
manufacturers' websites and entered it into tables in Word, which I
would like to copy into a more comprehensive file I am creating in
Excel. The first column of each table is the strength of the tablet,
entered as 1 mg., 2 mg., etc. The subsequent columns describe the
shape, color, and markings. There are 3 tables, each with about 4-5
rows.

Is there a way to copy each one - whether as a table or as text - into
a single Excel cell without losing data?

Many thanks.




Is there an Excel guru that can help with this - its related to "drop down menus"


I have 2 colombs of data.

AT the bottom of the first, I have created a drop down menu using the "data validation" feature in excel.

At the bottom of the second colomb, I have used an "IF" function that returns a result, which depends on what value is chosen from the drop down menu in colomb 1

The problem I have is that I want the TRUE result from the IF function to be another drop down menu, being the data in colomb 2.


How do you write an IF function where the TRUE result is a drop down menu??
I tried to create a drop down menu of colomb2 elsewhere in the spread sheet, and used that cells location as the TRUE value, but this didn't work either...

ANy suggestions??


I am using XL2007 and have a macro that refreshes microsoft query connections. The issue is the refreshes only happen if you step through the macro using the debugger. When you run the macro normally, everything else functions properly, but the data is not refreshed.

Any help is appreciated. Here is an excerpt of the code:


Workbooks.Open Filename:="C:\Profile.xls"
Sheets("SELECTION").Select
Range("F3").Value = SNR

' THIS PART ONLY WORKS IF YOU STEP THROUGH THE MACRO USING THE DEBUGGER...IF YOU RUN MACRO NORMALLY THE CONNECTIONS DO NOT REFRESH
ActiveWorkbook.Connections("Connection").Refresh
ActiveWorkbook.Connections("Connection14").Refresh
ActiveWorkbook.Connections("Query from C_Profile").Refresh
ActiveWorkbook.Connections("Query from C_Profile1").Refresh


I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
=(IF(D4+G1/1440>9:20,(D4+G1)/24,(D4+G1+15)/1440))
I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped


Hello,

I am new to using Excel and I am not very computer literate. I am looking for some help with a spreadsheet I am making. Here is the problem:

I have a column of 14 numbers in cells A1 to A14. If all of these values are less than 2%, I would like a "Pass" to display in cell B1. If any of these values are greater than 2% I would like a "Fail" to display in cell B1.

I have got a formula that works for a single cell but I can't figure out how to get it to apply to more than one cell. For example, I have entered =IF(A1<2,"Pass","Fail") in cell B1. Now if the value I enter in A1 is less than 2, it shows a "Pass", and if the value in A1 is greater than 2 it shows a "Fail" in cell B1. How can I get this to apply to all of the cells from A1 to A14?

If somebody could help me out that would be great.

Thanks,
Randy


I have cells in a column, some colored yellow, some not. I am trying to use
the SUMIF function to sum only the cells in the range that are colored. Can
this be done? Can I enter something in the "criteria" part of the formula
that can do this?

=SUMIF(D3:D13,"criteria",D3:D13)

I also tried to use the CELL function's color feature, but I couldn't get it
to work right. I don't know how to get Excel to recognize if a cell is
colored in a formula.
=CELL("color",cell)

It might just be that I don't know what this means in Help:

"color" --> 1 if the cell is formatted in color for negative values;
otherwise returns 0 (zero).

Can anyone help?



There sure is...Here's how:

From the Excel main menu:
<data><group and outline><settings>
Uncheck: Summary rows below detail

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"JMSprout" wrote:

> When you highlight rows, go to Data, then Group or Outline, then Group, it
> creates a nice plus/minus expand/collapse box for that group, only when the
> group is expanded the button to collapse it is at the bottom row of the
> group. Is there a way to get this button located at the top row of the group?




Related pages


excel data validation date formatest ist convertervba combobox control sourceblack scholes vbavba selection.pastespecialsalary sheet excel formatcreate a validation rule in exceldsoframerexcel vba array functionsupply demand graph excelvba excel modwhere is the autofill button in excel 2010test for normality in excelformula for fifoexcel vba date pickeractivedocument.mailmergeadjusted r squared excelfedex weight calculatorautocomplete feature in excelcumulative percentage excelcsv.gzexcel isna ifmicrosoft activex data objectscountif excel multiple criteriahow to enter consecutive numbers in excelhlookup ifdatediff excel vbacash denomination sheetexcel formula for gross marginmicrosoft date picker excel 2010vlookup mrexcelcreate folder vbasuperscript in excel formulamicrosoft excel has stopped working 2013computing golf handicapexcel named cellsexcel vba sleepexcel double vlookupprojection formula exceljulian date calculator exceldropdown in excel 2003how to calculate cumulative percentagecustom functions excelhow to merge multiple cells in excel without losing datanper functionexcel macro format cellvbcrlfcalculating variance excelwhat are tick marks in excelexcel vba monthformula for volume of a cylinder in gallonsmerge two cells into one in excelhow to make progress bar in excelremove duplicate rows excelfree barcode fonts for excelconversion from million to crorecalculating percentages excelerror 3706decile in excelproduction worksheet templatehow to unhide column a in excel 2007vba vbokcancelconvert dms to decimal degrees formulaconverting slope to degreesavery label template 5260excel print formulasexcel merge and center shortcutconcatenate in vba excelsubscripts in excelexcel application quitrequery formhow can i open xml file with excelhow to rotate a chart in wordcombine multiple spreadsheets into onehow to find midpoint between two numberssnp extensionbowling handicaproundup on excelformula for ranking in excelexcel vba unmerge cells