PDA

View Full Version : compile error



asdzxc
05-17-2012, 11:14 PM
Expected:list separator or )
original link : http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=w&ignore=.xlsx
In I2, to put d or w or m.
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=_
Worksheets("Sheet1").Range("I2").Value&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear:
On Error Goto 0
With Worksheets("Sheet1").QueryTables.Add(Connection:= _
"URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=_
Worksheets("Sheet1").Range("I2").Value&ignore=.xlsx" _
, Destination:=Worksheets("Sheet1").Range("$A$1"))
.Name = _
"MyData" .FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False

Kenneth Hobs
05-18-2012, 05:39 AM
Obviously, it is a string building syntax issue. Try breaking the string out to see if you are constructing it properly.

Dim s As String
s = "URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=" _
& Worksheets("Sheet1").Range("I2").Value & "ignore=.xlsx"
With Worksheets("Sheet1").QueryTables.Add(Connection:=s, _
Destination:=Worksheets("Sheet1").Range("$A$1"))

asdzxc
05-18-2012, 11:03 PM
Obviously, it is a string building syntax issue. Try breaking the string out to see if you are constructing it properly.

Dim s As String
s = "URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=08&b=13&c=2011&d=06&e=6&f=2013&g=" _
& Worksheets("Sheet1").Range("I2").Value & "ignore=.xlsx"
With Worksheets("Sheet1").QueryTables.Add(Connection:=s, _
Destination:=Worksheets("Sheet1").Range("$A$1"))
Thank you for your code.
In I2 , to put wether a, d or m, produce same result:
Dim s As String
On Error Resume Next
ThisWorkbook.Connections(1).Delete
Err.Clear:
On Error GoTo 0

s = "URL;http://ichart.finance.yahoo.com/table.csv?s=^dji&a=1&b=13&c=2012&d=06&e=6&f=2013&g=" _
& Worksheets("Sheet1").Range("I2").Value & "ignore=.xlsx"
With Worksheets("Sheet1").QueryTables.Add(Connection:=s, _
Destination:=Worksheets("Sheet1").Range("$A$1"))

.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = 0
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.Refresh BackgroundQuery:=False
End With
End Sub