im new to vba and have been trying to make a macro that inputs the data value from column A into the website emma.msrb.org and take the data table from the vrdo tab on the site. I then need it to take only the most recent interest rate from the table and the date and want it to then put this data in column B&C. This macro also needs to loop through all of column a and repeat this until nothing is left.
This is what i sort of stole from other scripts and modded for mine, but its not really what i want.
Sub Get_Nyse_Data() Dim C As Range Dim strName As String Dim strConnectString Dim QT As QueryTable On Error Resume Next
For Each C In Selection strName = C.Value strConnectString = _ "URL;http://emma.msrb.org/SecurityView/SecurityDetailsVRDO.aspx?cusip=" & C.Value ThisWorkbook.Worksheets.Add ActiveSheet.Name = strName
With QT .Name = strName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With
' On the Workspace worksheet, 'clear all existing query tables For Each QT In ActiveSheet.QueryTables QT.Delete Next QT
' Define a new Web Query Set QT = ActiveSheet.QueryTables.Add _ (Connection:=strConnectString, Destination:=Range("B1")) Application.Run "BLPLinkReset"
' Refresh the Query QT.Refresh BackgroundQuery:=True
Next End Sub
This is what i sort of stole from other scripts and modded for mine, but its not really what i want.
Sub Get_Nyse_Data() Dim C As Range Dim strName As String Dim strConnectString Dim QT As QueryTable On Error Resume Next
For Each C In Selection strName = C.Value strConnectString = _ "URL;http://emma.msrb.org/SecurityView/SecurityDetailsVRDO.aspx?cusip=" & C.Value ThisWorkbook.Worksheets.Add ActiveSheet.Name = strName
With QT .Name = strName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlEntirePage .WebFormatting = xlWebFormattingNone .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With
' On the Workspace worksheet, 'clear all existing query tables For Each QT In ActiveSheet.QueryTables QT.Delete Next QT
' Define a new Web Query Set QT = ActiveSheet.QueryTables.Add _ (Connection:=strConnectString, Destination:=Range("B1")) Application.Run "BLPLinkReset"
' Refresh the Query QT.Refresh BackgroundQuery:=True
Next End Sub
