By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,654 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,654 IT Pros & Developers. It's quick & easy.

How to use input box data?

P: 2
I have recorded a macro that will use a web query to obtain the latest price of the microsoft (msft) stock. I want to use this macro to get data on other stocks. My thought is to use an input box that asks me to type in the ticker name. The macro will then use this input to obtain the latest price of whatever ticker i type in. I can't figure out how to put the input box data into the macro. In the macro below, I want to put X wherever the web query uses the ticker msft. My plan is to add a bunch of web queries to the macro so when I type in the ticker name into the input box, the macro will return financial sheets, rations, etc:

Sub Getquote()
'
' Getquote Macro
' Macro recorded 1/8/2011 by CLAY D
'
' Keyboard Shortcut: Ctrl+q
'

X = InputBox("enter ticker", "ticker name")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=msft", Destination:=Range("F27"))
.Name = "q?s=msft_1"
.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 = """table1"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Jan 8 '11 #1
Share this Question
Share on Google+
1 Reply


P: 46
I am not familiar with the web site you are querying so don't know the difference between the two msft references that I see in your code, however to include X this is how you would do it:

"URL;http://finance.yahoo.com/q?s=msft", Destination:=Range("F27"))
.Name = "q?s=msft_1"

"URL;http://finance.yahoo.com/q?s=" & X, Destination:=Range("F27"))
.Name = "q?s=" & X " & "_1"
Jan 13 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.