Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query
Hello,
Does any one knows why the following macro not running; sub test() Dim a a = Selection ' The is the selection in sheet ' where web address is given Sheets("Sheet2").Select Range("A1").Select ActiveCell.FormulaR1C1 = a With Selection.QueryTable.add(Connection:=a) WebSelectionType = xlAllTables WebFormatting = xlWebFormattingNone WebPreFormattedTextToColumns = True WebConsecutiveDelimitersAsOne = True WebSingleBlockTextImport = False WebDisableDateRecognition = False Refresh BackgroundQuery:=False End sub regardsImran |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query
With Selection.QueryTable.add(Connection:=a)
First, you can't add a query to a selected cell, you add it to the sheet and give it a destination cell. Also the connection string requires two parts, a type and an address. If the value in the cell is just an address, you will need to prefix it with "URL;". Also you are missing an End With. And you might want to clear the destination sheet to get rid of the last web query results. Sub Test() Dim a a = Selection Sheets("Sheet2").Select Range("A1").Select Cells.Clear 'get rid of old stuff from prior query With ActiveSheet.QueryTables.Add(Connection:="URL;" & a, Destination:=Selection) .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub -----Original Message----- Hello, Does any one knows why the following macro not running; sub test() Dim a a = Selection ' The is the selection in sheet ' where web address is given Sheets("Sheet2").Select Range("A1").Select ActiveCell.FormulaR1C1 = a With Selection.QueryTable.add(Connection:=a) .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End sub regardsImran . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |