Free Microsoft Excel 2013 Quick Reference

Copy/Pasting from Web Page

I am a novice when it comes to excel, but I have a project due at work where I need to search out correct addresses and copy and paste them into the cells where the incorrect info already exists.
Here is my problem, I have been working on this no problem for about a week now and today all of a sudden when I copy the text from my search result on google and try to paste it into the cell, it pastes the google link, instead of the text I copied. It does this for the entire row but will not do it for row after row, I might be able to get the next 5 or 6 rows to accepet the correct text, and if I try to just type the info in, it appears as a clickable link.
I cannot figure out what I did, if anything or how to fix it so that I can get this work done. Thanks!


Post your answer or comment

comments powered by Disqus
Usually when I want data from a web page that uses Courier (fixed width)
font, I do a copy, paste special as text and use the Data|Text to Columns
command to parse it out. Sometimes, though Excel does this "on its own"; ie,
it makes an attempt to parse it out for me when I do the paste special as
text. Sometimes that's ok, but sometimes it's not -- sometimes I really do
want to parse it out using Data|Text to Columns because there's a column
break I want to change/adjust. I can't figure out why sometimes it attempts
the parsing and sometimes it does not. It usually starts to do its own
parsing after I've been playing around with copying and paste specialing a
bunch of stuff -- but I use new worksheets, where there's no "left over"
formatting from previous pastes or anything. Then, when I quite completely
out of Excel and restart it and do a copy, paste special as text, it doesn't
attempt to parse (it's like quitting "resets" it). Does anyone understand
what I mean and/or why this happens? Thanks.

I have copied and pasted a table from a web page into a new workbook. The table contains 2 embedded command buttons. I don't seem to be able to select these buttons to delete them: they will not select when I right-click them, they do not have assigned names in the Name Box, nor can I select them if I click the Select Objects button in the Drawing toolbar.

Any ideas how I can select these buttons and/or delete them?

Regards
Steve Auto Merged Post Until 24 Hrs Passes;

Managed to answer my own post, although I still don't understand why these objects are not selectable in the UI:


	VB:
	
 DelBtn() 
     
    Dim obj As Shape 
     
    For Each obj In ActiveWorkbook.Worksheets(1).Shapes 
        obj.Delete 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a folder full of uniformed web pages (name and design) and I was wondering if it's possible to create a macro that will open up each page, copy specific data from each page, and paste it into an Excel 2002 spreadsheet. Unfortunately, my skills in VBA are very limited at best and I'm not sure if this is doable or is it something I'll have to go into each page and copy the data. So, am I crazy or is this possible?

Hy guys.

What I need to do is extract data from web page, the easiest way is with web query. But when I stated to learn how to do it, most help is with 'fixed' web pages. Here I have a page, where you enter a company's name and it gives you results (as a phone book). From that page (with results) I need to copy data to my excel worksheet.
I want to do a macro, where you enter a company's name into inputbox, click the button and internet explorer page opens with resulting page. So far I made it ok. But for web query I don't know.

Can anyone help me?
My code is bellow (since it is not an american page, for the ones that will test the code, you should try a search with company's name "ESISTEMI")

Much thanks in advance, Borut


	VB:
	
 
Sub Button1_Click() 
    Dim ie As Object 
    Dim osh As Worksheet 
    Set osh = ActiveSheet 
    vprasalnik 
    Application.ScreenUpdating = False 
    Set ie = CreateObject("InternetExplorer.application") 
    On Error Resume Next 
    ie.Navigate "[URL]http://www.bizi.si/[/URL]" 
    ie.Visible = True 
    Do Until ie.Document.ReadyState = "complete" 
        DoEvents 
    Loop 
    ie.Document.all.Item("ctl00$ContentPlaceHolderLeft$ucSearchCommon$tbSearchWhat").Value = firma 
    ie.Document.getElementById("ctl00_ContentPlaceHolderLeft_ucSearchCommon_btnSearch").Click 
    Do Until ie.Document.ReadyState = "complete" 
        DoEvents 
    Loop 
     
     '--> here i need web query code
     
End Sub 
 
Sub vprasalnik() 
    thesentence = InputBox("Type Text Please", "Using the VBA Input Box") 
    firma = thesentence 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to obtain text (search results) from a webpage into excel.

Using Get External Data works fine for the one page, but i would like a script that then selects the "next" button on the relevant web page and perfroms the download again etc etc

Any hints?

cheers

Is this possible?
I have read numerous threads on disabling copy paste functions for entire spreadsheets, however, i need some cells to be able to copy and paste and some not.

Can i disable copy paste from a row?

Also sheet protection does not work as i need the user to enter data which will vary at every instance.

Any help is appreciated.
Thanks

Whenever I copy /paste from a PDF file it turns up all helter skelter in
excel. It soesn't retain the row/column structure of the PDF when pasted in
excel. Perhaps I need to buy a special program. I am using excel 2003, is the
excel 2007 any better?

Previously I have been copy pasting from excel to wordpad then to microsoft
word to get text only in word. If you try to paste straight to word it will
give me a table with my information on it. I just want the text not the chart
lines. Is there a faster way?

I'd like to copy/paste from different spreadsheets. The place it will be pasted is specific.

Sub Family1()
Range("A2").Value
Sheets("Black Market").Select
Range("C3:C35").Copy
Sheets("Profiles").Select
Range("B2").PasteSpecial
Sheets("Real Estate").Select
Range("E3:E30").Copy
Sheets("Profiles").PasteSpecial
End Sub

Is there a better way of capturing data from web pages directly into Excel,
other than web queries? I find I can only capture certain sections of web
pages using web queries, not necessarily the data/tables that I am interested
in.

hello all.

Looking for a function to auto fill (copy paste) from the row above and skip rows that have data in them until the last non empty row and cant seem to find the right one.

Basicly would like to high light an entire column then start a macro that will copy the cell from above(row) to cell below(row until next cell that has data, continue down (skip rows with data) until next empty cell and copy the row above, loop that process until the end of the column or last row that has data in it.

There are a lot of great posts but cant seem to find that will do this.

Before Macro After Macro
apple apple
apple apple
apple
carrot carrot
carrot
car car
car car
car
car
car
car
car car

end loop at last non empty row

Thanks
Joe.....

Hello everyone,

I am working on community web site as a moderator. For reporting purpose i want to extract some data ( not whole web page only particular fields) from web page to excel sheet. I don't know how to do it. I have tried web query option from excel tool bar but that is pulling complete web page which i don't want.

Please suggest some other option.

I have pasted a web page that contains a table into an Excel sheet. The web page uses a dropdown menu to filter the records displayed in the table (Ex: All records; Open records; Closed records; etc.) I would like to validate the info being pasted into the sheet to ensure that the correct filter was used, but I cannot figure out how to grab the value that is displayed in the listbox when it is pasted into the worksheet.

Pseudo code:
If "Open Records" was selected on the dropdown menu, then copy all the records from the table from the temporary sheet to the master sheet.
If "Open Records" was NOT the active selection in the dropdown menu, display error message stating that it was an invalid import (i.e. wrong dropdown menu selection - should have been "Open Records") and exit sub.

Just as a simple proof of concept I have been trying to display the value of the listbox selection in a message box (as shown below):


	VB:
	
MsgBox ActiveSheet.shapes("Control 26").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I try this I get an error message stating that the "Object doesn't support this property or method" (Error #438)

By way of background, when I select the dropdown menu/listbox in "Design Mode", "Control 26" appears in the Name Box (to the left of the formula bar) and =EMBED("Forms.HTML:Select.1","") appears in the actual formula bar. Under object properties, the (Name) of the dropdown menu that I want to get the value from is "HTMLSelect1". When I recorded a macro to select the dropdown menu/listbox (just to see how its referenced in vba) this is what I got:


	VB:
	
 Macro1() 
    ActiveSheet.shapes("Control 26").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


So, my question is how can I get the current selection of a pasted listbox to use as a variable or in an expression to validate my pasted table from the web page? I want to ensure that users only import "Open records". Thanks!

Hey gang, total amatuer when it comes to excel, so I would really appreciate any help you can lend.

Here's all I'd like to do~I'm a small business owner and I need to create an excel list of potential work contacts.

The contacts are listed on a web page like this

aerographics Los Angeles 310. 240. 3308

Now, on the web page if you click on aerographics it takes you to the website.

I'd like to copy and paste those same three entries from the webpage in to an excel sheet~and be able to click on aerographics and have it take me to the site.

I searched this site and couldn't find the problem replicated. I've tried paste special~it formats the entries I want as URL's, but when I click on them nothing happens.

I can type www.googl.com in to a cell and it instantly works as a link.

And I have about 500 of this type entry/copy and paste to do so making a hyperlink is impractical....

...I really appreciate anyone taking a second to lend a hand. I realize this is very elementary for most of you, but its quite puzzling to me.

Thanks so much,

Dana

When i copy number from a web page then paste them to a column i cannot use
the sum feature to get a total for the column. What can i do?

When I cut text from a web page, Excel then tries to connect again to that
web page to setup some sort of auto-update option.

This is very annoying, especially if all I want to do is copy & paste the
text/numerical contents from an (https) web page onto a sheet - I can see
this since the desktop firewall I'm using (Sygate) keeps complaining about
Excel attempting to connect to the web.....

* How do I disable that feature - the copy operation should be over & done
with after Ctrl+C

THIS Feature is not a "productivity" enhancement...

Steve

When i copy number from a web page then paste them to a column i cannot use
the sum feature to get a total for the column. What can i do?

I am trying to use VBA to manage invoice generation via an online service. The service is set up in such a way that the process cannot be made completely automatic, but I am trying to reduce it to as few manual steps as possible. Hence I am opening the Web Browser control in an Excel userform to streamline and guide the user's process.

The invoice is generated as a Tab-delimited TXT file. I don't want to save the file to disk, and selecting 'Open' opens the file as a web page, rather than in Notepad or Excel.

One way or another this data needs to be transferred to a hidden Excel worksheet for further processing. So I am trying to devise a way to copy the data from the web page and paste it onto the Excel worksheet.

Here is the relevant code from within the Web Browser's class module:


	VB:
	
 
    Application.Wait Now + TimeValue("00:00:01") 
    SendKeys "^a^c", False 
    Application.Wait Now + TimeValue("00:00:01") 
    ActiveWorkbook.Sheets("Sheet3").Paste Destination:=ActiveWorkbook.Sheets("Sheet3").Range("A1") 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The "^a^c" (control-A, control-C) part works correctly; if I interrupt the code at that point I can open Notepad and directly paste the invoice data.

But pasting into Excel fails in a way that has me baffled. Instead of the invoice it pastes the previous contents of the clipboard; for instance the invoice number that I copy and paste in an earlier step. I can even run the code as written above, see the invoice number pasted into Excel, and directly open Notepad, type Control-V, and have the actual invoice pasted into Notepad! It is as if Excel's paste function accesses different clipboard data from other applications.

So my brief question is: How do I get the data into Excel? The longer question is: Is there some step I have missed that specifies to Excel what clipboard data to paste?

I am trying to get correct date from information copied from html file from
WEB excel spreadsheet.
The text is:
MANDURAH (CD) Race 1, NEW YEAR SALE (GRADE P5) Friday 11.02.2005.
The correct date is 11 February 2005.
When use command Data>text to columns and later replace “.” with ”/” I am
getting date 2/11/2005 (2 November 2005) – with value 37196. I tried format
whole spreadsheet as text or particular cells before and after operation.
What is more strange sometimes I am getting correct date in other worksheets
but I never know which date I will get.
I have also problem with pasting other data from web pages when numbers are
converted to dates against my wish. How I can stop it?
For example
A table in browser looks like this
Pool Result Div
Quinella 3-6 20.50
Exacta 6-3 33.30
Trifecta 6-3-8 154.50
Quartet 6-3-8-4 955.40

And in Excel:

Pool Result Div
Quinella 3-Jun 20.5
Exacta 6-Mar 33.3
Trifecta 6/03/2008 154.5
Quartet 6-3-8-4 955.4
Thanks
Jacek

I have copied a table successfully from a web page but want to remove a
"button" that was on the original web page that has come with the table, as
it were.

The problem is that I don't seem to be able to SELECT the button when it is
in Excel - is there a way round this problem?

My workaround will be to recopy part of the material to a new worksheet but
it would be nice to find a neater method.

Thanks

Tim

I am trying to get correct date from information copied from html file from
WEB excel spreadsheet.
The text is:
MANDURAH (CD) Race 1, NEW YEAR SALE (GRADE P5) Friday 11.02.2005.
The correct date is 11 February 2005.
When use command Data>text to columns and later replace “.� with �/� I am
getting date 2/11/2005 (2 November 2005) – with value 37196. I tried format
whole spreadsheet as text or particular cells before and after operation.
What is more strange sometimes I am getting correct date in other worksheets
but I never know which date I will get.
I have also problem with pasting other data from web pages when numbers are
converted to dates against my wish. How I can stop it?
For example
A table in browser looks like this
Pool Result Div
Quinella 3-6 20.50
Exacta 6-3 33.30
Trifecta 6-3-8 154.50
Quartet 6-3-8-4 955.40

And in Excel:

Pool Result Div
Quinella 3-Jun 20.5
Exacta 6-Mar 33.3
Trifecta 6/03/2008 154.5
Quartet 6-3-8-4 955.4
Thanks
Jacek

I am trying to copy and paste a web page but it doesnt seem to work anyone have any ideas. did a search but didnt find anything.
it will open the site but just will not do the copy and paste function I am using XP pro and IE 7

Sub
GetRealDash()
    Dim IE As Object
     
     Sheets("RealDash").Select
     Range("A1:A1000") = ""
    Range("A1").Select
     
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://realdash/Dash_Viewer.aspx?DashID=5137" ' should work for any URL
        Do Until .ReadyState = 4: DoEvents:  Loop
        End With
         
        IE.ExecWB 17, 0 '// SelectAll
        IE.ExecWB 12, 2 '// Copy selection
        ActiveSheet.PasteSpecial Format:="Text", link:=False, DisplayAsIcon:=False
        Range("A1").Select
        
        IE.Quit
    End Sub


Hi guys i have a question basically i am downloading data from the web into the same excel sheet from two separate (sometimes more) web pages:
1. http://finance.yahoo.com/gainers?e=us
2. http://finance.yahoo.com/losers?e=us
i am using a macro to do this eg:

	VB:
	
 ActiveSheet.QueryTables.add(Connection:= _ 
    "URL;http://finance.yahoo.com/gainers?e=us", Destination:=Range("$A$1")) 
    .Name = "gainers?e=us_1" 
    .FieldNames = True 
    .RowNumbers = False 
    .FillAdjacentFormulas = False 
    .PreserveFormatting = True 
    .RefreshOnFileOpen = False 
    .BackgroundQuery = True 
    .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:=False 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
my question is after the first web page is downloaded what code would i need to find the last empty cell in column A (given that there are empty cells throughout the data in column A ) and how do i paste the next lot of raw web page data to that cell

Hi,

i've looked on the web and other excel groups but i can't find anything
that'll help. Hopefully someone here can guide me toward the light..

What i'm trying to achieve is this;

i have a long list in Excel of hyperlinks, and i'm trying to come up with a
macro that will follow the first link, take certain data from the page,
paste it into a new sheet in the same workbook, and then follow the next
link in the next cell and repeat the process.

This is one of the links;

http://www.oxfordshire.gov.uk/wps/po...id=273#7_M_52R

and i want to get the Address, Headteacher etc down to the DFES number
information, to appear in the new sheet, followed by the next link, and the
next and so on. At this point it's no problem if all the info is pasted into
just the one cell, but it'd be nice if each line (Address: Ruskin Road,
Banbury, OX16 9HY for example) had it's own cell.

All the links are different, but the page format remains the same
throughout, btw.

i've tried recording a macro, but it only works for the same site and i
can't work out how to keep repeating the process.

i'm using Excel 2002 SP3 in WinXp.

If anyone can help it would save me hours, and would be much appreciated!

Thanks in advance,
sh


No luck finding an answer? You could always try Google.