Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Worksheet Coding Help
Hello,
i put a command button on the first sheet of a template workbook and i is my intention to put data from a web query to another sheet in th same workbook when i press the command button created on sheet1. th code i have is as follows: With ThisWorkbook.Worksheets("Sheet2").QueryTables.Add( Connection:= _ "URL;http://chart.yahoo.com/table.csv?a=...amp;ignore=.csv", _ Destination:=Range("a6")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xloverwriteclearcells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 '.WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False .TextFileCommaDelimiter = True End With Range("A6:A7").Select Selection.TextToColumns Destination:=Range("A6") DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5 1), Array(6, 1), _ Array(7, 1)) Range("A7:G3").Select Selection.Columns.AutoFit i am getting the following error: this destinationrange is not on the same worksheet that the query tabl is being created on. how should i modify my code and/or approch to achive my goal? thank you for any help you can offer. ton -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA - Worksheet Coding Help
You need to qualify your ranges with a worksheet. Or
activate the second sheet so the implied "ActiveSheet" will be used for your ranges. I like to qualify them all, personally. dim shtSheet2 as Worksheet set shtSheet2 = Worksheets("Sheet2") Then refer to shtSheet2.Range("A6") -Brad -----Original Message----- Hello, i put a command button on the first sheet of a template workbook and it is my intention to put data from a web query to another sheet in the same workbook when i press the command button created on sheet1. the code i have is as follows: With ThisWorkbook.Worksheets("Sheet2").QueryTables.Add (Connection:= _ "URL;http://chart.yahoo.com/table.csv? a=...amp;ignore=.csv", _ Destination:=Range("a6")) .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = False .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xloverwriteclearcells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 '.WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingAll .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .Refresh BackgroundQuery:=False .TextFileCommaDelimiter = True End With Range("A6:A7").Select Selection.TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _ Array(7, 1)) Range("A7:G3").Select Selection.Columns.AutoFit i am getting the following error: this destinationrange is not on the same worksheet that the query table is being created on. how should i modify my code and/or approch to achive my goal? thank you for any help you can offer. tony --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I match numeric coding in a worksheet to words in a pdf fil | Excel Discussion (Misc queries) | |||
Customizing Excel Worksheet Menu Bar without VBA coding | Excel Discussion (Misc queries) | |||
Customizing Worksheet Menu Bar for a workbook without VBA coding | Excel Discussion (Misc queries) | |||
Correct coding entry of VBA macros in Excel worksheet | Excel Worksheet Functions | |||
Referring to Previous Worksheet - coding | Excel Worksheet Functions |