user176705 user176705 - 1 month ago 9
HTML Question

Macro for Multiple links

How do I loop/run the code According to a list in .txt file, until the list is ends ??

How do I loop/run the code According to a list in .txt file, until the list is ends ??

Sub FundData()

Dim TikerName As String

TikerName = 'check & execute the names in list one after one in .txt file until it ends up

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://example.com/index.php?name=" & TikerName, Destination:=Range( _
"$A$1"))
'.CommandType = 0
.Name = "name=" & TikerName
.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 = """company"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = TikerName
Sheets.Add After:=ActiveSheet
ThisWorkbook.Save

End Sub

Answer

Create a structure something like this:

Sub FundData()

    Dim TikerName As String

    Open "C:\somepath\sometextfile.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, TikerName

        ' your main code goes here

    Loop
    Close #1
    ThisWorkbook.Save
End Sub