I have recorded a Macro and its something like this:
Sub Macro1() ' ' Macro1 Macro ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://URL NAME-1.html", _ Destination:=Range("$A$1")) .Name = "1st_executive" .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 = "6" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub
This Macro would fetch details from URL mentioned and paste the data in "A1" on Sheet-1.
Requirement: I have over 3000 such URLs from which data needs to be downloaded. the URL links are present in a separate worksheet. How do i reference "Connection:=_" to dynamically get it from the work sheet and increment it by 1?
Also, how do i set the Destination:=Range to dynamically refer to last row + 1 rather than hardcoding it as $A$1 or $A$20, etc?
Any help in this regard is highly appreciated.