web query

[SFitz08]

I want to run a macro in excel that asks for a ticker symbol (ex. wag) I
store that into a variable and then run a webquery to take that input and
pull up the appropriate website. This works fine when the variable is at the
end of the web address. However, the variable that the user enters is
actually in the middle of the web address. I cant seem to fix this. Please
see code below. I am very very new to VB. I recorded a macro to get the code
shown below. Thank you!


Sub Macro3()

Dim tickersymbol As String

tickersymbol = InputBox("What is the ticker symbol?")

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=[& tickersymbol]&lstStatement=10YearSummary&stmtView=Ann" _
, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """INCS"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Don Guillett]

I suspect you have many other problems with this project such as adding
data, more than one symbol, building up external names, etc. But, to answer
your immediate question.

aspx?Symbol=[& tickersymbol]&

aspx?Symbol=" & tickersymbol & "&lstStatement

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I want to run a macro in excel that asks for a ticker symbol (ex. wag) I
store that into a variable and then run a webquery to take that input and
pull up the appropriate website. This works fine when the variable is at
the
end of the web address. However, the variable that the user enters is
actually in the middle of the web address. I cant seem to fix this. Please
see code below. I am very very new to VB. I recorded a macro to get the
code
shown below. Thank you!


Sub Macro3()

Dim tickersymbol As String

tickersymbol = InputBox("What is the ticker symbol?")

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=[&
tickersymbol]&lstStatement=10YearSummary&stmtView=Ann" _
, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """INCS"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[SFitz08]

Thank you very much! I am just doing this on my free time. I've been playing
the stock market for a while and I am trying to come up with an easier way
for me to research my stocks. I will most likely be adding more macros to my
excel sheet. Do you recommend a particular book that I should buy? Thanks
again!

I suspect you have many other problems with this project such as adding
data, more than one symbol, building up external names, etc. But, to answer
your immediate question.

aspx?Symbol=[& tickersymbol]&

aspx?Symbol=" & tickersymbol & "&lstStatement

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I want to run a macro in excel that asks for a ticker symbol (ex. wag) I
store that into a variable and then run a webquery to take that input and
pull up the appropriate website. This works fine when the variable is at
the
end of the web address. However, the variable that the user enters is
actually in the middle of the web address. I cant seem to fix this. Please
see code below. I am very very new to VB. I recorded a macro to get the
code
shown below. Thank you!


Sub Macro3()

Dim tickersymbol As String

tickersymbol = InputBox("What is the ticker symbol?")

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=[&
tickersymbol]&lstStatement=10YearSummary&stmtView=Ann" _
, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """INCS"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Don Guillett]

Go here>join>look in the files section for free files that may be of help. I
have some there under the author donaldb36

http://tech.groups.yahoo.com/group/xltraders/files/


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thank you very much! I am just doing this on my free time. I've been
playing
the stock market for a while and I am trying to come up with an easier way
for me to research my stocks. I will most likely be adding more macros to
my
excel sheet. Do you recommend a particular book that I should buy? Thanks
again!

I suspect you have many other problems with this project such as adding
data, more than one symbol, building up external names, etc. But, to
answer
your immediate question.

aspx?Symbol=[& tickersymbol]&

aspx?Symbol=" & tickersymbol & "&lstStatement

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I want to run a macro in excel that asks for a ticker symbol (ex. wag) I
store that into a variable and then run a webquery to take that input
and
pull up the appropriate website. This works fine when the variable is
at
the
end of the web address. However, the variable that the user enters is
actually in the middle of the web address. I cant seem to fix this.
Please
see code below. I am very very new to VB. I recorded a macro to get the
code
shown below. Thank you!


Sub Macro3()

Dim tickersymbol As String

tickersymbol = InputBox("What is the ticker symbol?")

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.aspx?Symbol=[&
tickersymbol]&lstStatement=10YearSummary&stmtView=Ann" _
, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """INCS"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub