Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query: Obtaining source link from a cell in a macro
Hi When I create a macro for a web query, the code looks like this: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.etc.com/mypage.htm" _ , Destination:=Range("C4")) Simple enough, but this macro is only good for one web page. How can I query another web page without changing the macro, where the new web address come from a particular cell on the worksheet? All ideas welcome Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=499085 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query: Obtaining source link from a cell in a macro
Try something like the following. It takes the address from cell
A1. With ActiveSheet.QueryTables.Add(Connection:= _ Range("A1").Text _ , Destination:=Range("C4")) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "tx12345" wrote in message ... Hi When I create a macro for a web query, the code looks like this: With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.etc.com/mypage.htm" _ , Destination:=Range("C4")) Simple enough, but this macro is only good for one web page. How can I query another web page without changing the macro, where the new web address come from a particular cell on the worksheet? All ideas welcome Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=499085 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query: Obtaining source link from a cell in a macro
Thnks for the reply. OK, here is something more specific, and I know this works: Code: -------------------- Sub getprice() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://quote.barchart.com/quote.asp?sym=V4Y0", Destination:=Range("C1")) .Name = "quote.asp?sym=V4Y0" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub Code: -------------------- But when I revise the code to read from A1: Code: -------------------- Sub getprice() With ActiveSheet.QueryTables.Add(Connection:= _ Range("A1").Text, Destination:=Range("C1")) .Name = "quote.asp?sym=V4Y0" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub -------------------- with A1 containing: URL;http://quote.barchart.com/quote.asp?sym=V4Y0 I get a debug error any ideas? Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=499085 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Web Query: Obtaining source link from a cell in a macro
Both worked fine for me.
-- Regards, Tom Ogilvy "tx12345" wrote in message ... Thnks for the reply. OK, here is something more specific, and I know this works: Code: -------------------- Sub getprice() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://quote.barchart.com/quote.asp?sym=V4Y0", Destination:=Range("C1")) .Name = "quote.asp?sym=V4Y0" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub Code: -------------------- But when I revise the code to read from A1: Code: -------------------- Sub getprice() With ActiveSheet.QueryTables.Add(Connection:= _ Range("A1").Text, Destination:=Range("C1")) .Name = "quote.asp?sym=V4Y0" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = False .RefreshPeriod = 0 .WebSelectionType = xlAllTables .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False End With End Sub -------------------- with A1 containing: URL;http://quote.barchart.com/quote.asp?sym=V4Y0 I get a debug error any ideas? Thx -- tx12345 ------------------------------------------------------------------------ tx12345's Profile: http://www.excelforum.com/member.php...o&userid=24776 View this thread: http://www.excelforum.com/showthread...hreadid=499085 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link a chart source to a cell reference | Charts and Charting in Excel | |||
Can Excel 2003 cell link carry with it the source cell format? | Excel Discussion (Misc queries) | |||
Web Query: Obtaining source link from a cell | Excel Worksheet Functions | |||
Using paste link infromation in cell to hyperlink to source cell? | Excel Worksheet Functions | |||
Unable ot Break link for Macro with no source | Excel Discussion (Misc queries) |