Excel VBA - WorkBook Coding Help

  • Thread starter Thread starter csus_tony
  • Start date Start date
C

csus_tony

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
 
I assume that Sheet2 is not the active sheet. All your unqualified range
references will be assumed by VBA to refer to the active sheet.

Possible solution: Fully qualify all range references with the
workbook/worksheet and get rid of the Select stuff.

Untested:

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
..Range("A6:A7").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").Columns.AutoFit
End With
 
Back
Top