Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy / Paste from web page
I'm having problems with the formatting of a web query inside of a macro.
Maybe this is best posted in the programming group, so I apologize if that is the case. When I go to a web page -- http://finance.yahoo.com/q/is?s=HD&annual -- and select all (CTRL-A) and then 'Paste Special' as text into A1 I get everything in column A, which is what I want. When I create my external web query I choose to have no formatting but the results are still broken out in several columns. How can I write a macro to simply go to a website select all and copy that site and then paste special as text into my excel sheet? Is that possible? Have a good day, Ted |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy / Paste from web page
I went to your urlimported into excelrecorded a macro while doing
dataimport external dataedit queryselecting tableselecting option to use html and got this Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2/11/2008 by Donald B. Guillett Range("A4:B4").Select With Selection.QueryTable .Connection = "URL;http://finance.yahoo.com/q/is?s=HD&annual" .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingAll .WebTables = "15" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub '======== Now, since your query is established you may now just use Sub refreshdata() With ActiveSheet.QueryTables(1) .Connection = "URL;http://finance.yahoo.com/q/is?s=HD&annual" .Refresh BackgroundQuery:=False End With End Sub ======= PERIOD ENDING 28-Jan-07 29-Jan-06 30-Jan-05 Total Revenue 90,837,000 81,511,000 73,094,000 Cost of Revenue 61,054,000 54,191,000 48,664,000 Gross Profit 29,783,000 27,320,000 24,430,000 If there are rows/columns you don't want, simply hide them If you ask, OFF list, I can send you a workbook -- Don Guillett Microsoft MVP Excel SalesAid Software "Ted Metro" wrote in message ... I'm having problems with the formatting of a web query inside of a macro. Maybe this is best posted in the programming group, so I apologize if that is the case. When I go to a web page -- http://finance.yahoo.com/q/is?s=HD&annual -- and select all (CTRL-A) and then 'Paste Special' as text into A1 I get everything in column A, which is what I want. When I create my external web query I choose to have no formatting but the results are still broken out in several columns. How can I write a macro to simply go to a website select all and copy that site and then paste special as text into my excel sheet? Is that possible? Have a good day, Ted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing pivot table along with a copy and paste for each page fie | Excel Discussion (Misc queries) | |||
How to create a macro which performs a copy-and-paste task every 5mins after data is refreshed from web page? | Excel Worksheet Functions | |||
copy a page with hidden rows and only paste what is showing | Excel Discussion (Misc queries) | |||
Reference an identical cell on a different page using copy/paste? | Excel Worksheet Functions | |||
How do I copy page setup from one worksheet & paste into new shee. | Excel Discussion (Misc queries) |