Macro for Multiple links

Question

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

There are about 300+ string in 300+ raw, mean last (row) string of the txt file, like TikerName.txt — user176705
– user176705, Commented Oct 26, 2016 at 19:48

YowE3K · Accepted Answer · 2016-10-26 19:51:54Z

0

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

answered Oct 26, 2016 at 19:51

YowE3K

24k7 gold badges28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user176705 Over a year ago

showing not responding :( 300+ link access simultaneously from excel in same site, should it be ok??

YowE3K Over a year ago

@user176705 - I'm not sure I understand your comment. I can only guess that you mean that your question wasn't meant to be "How do I loop/run the code According to a list in .txt file, until the list is ends up??" but "How do I get sufficient resources so that Excel can perform 300 queries simultaneously". If so, I suggest you create a new question as that one is wildly different to what you asked.

user176705 Over a year ago

Thanks for your nice understanding my friend. I will make a new question soon.

Collectives™ on Stack Overflow

Macro for Multiple links

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related