hi i need some help with looping web querys

fordprefect225

New Member
Joined
Jul 26, 2012
Messages
29
take a number from colum A (001-650) paste it into a cell and then run a webquery

the trouble i have is it won't loop

this is the code

Code:
Sub Macro2()
Dim VAL As String
 Do
 
 ActiveCell.Offset(1, 0).Select
 Range("E2").PasteSpecial
 
 Range("E1").Copy
    Range("G1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("G8:H8").ClearContents
    Range("G8").Font.Italic = True
    Range("G7").Select
    VAL = Range("G1").Value
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & VAL, Destination:=Range("$G$7"))
    .Name = "008.shtml"
        .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,18,19,20"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
       End With
      
     
 
    Loop Until IsEmpty(ActiveCell.Offset(0, 1))
      
End Sub
 

Forum statistics

Threads
1,081,458
Messages
5,358,808
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top