Editing Recorded Macro - using Data From Web

[krishhi]

Hello,

I have recorded a macro and I need to loop the same macro with different Web address. I tried myself but I failed.

Please Check the excel file for what i wanted to do.

http://www.4shared.com/file/DPcUMGz9/Office_Project.html

Thanks,

Krrish

 

[JosV]

Hello Krishhi,

I've solved your problem:

Sub Macro2()

'
Dim Loper As String
Dim lr As Long

sr = Sheets("Sheet2").UsedRange.Rows.Count


For i = 1 To sr

lr = Sheets("Sheet1").UsedRange.Rows.Count
p = "URL;" & Sheets("Sheet2").Range("A" & i).Value
lr = lr + 4
Loper = "A" & lr
      
        
 With Sheets("Sheet1").QueryTables.Add(Connection:=p, Destination:=Range(Loper))
        
        
       ' .Name = "searchResults.jsp?thingname=&1=FL&2=+Miami&zip=&radius=60&ratedonly=1"
       
        .Name = Sheets("Sheet2").Range("d" & i).Value
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
        .WebTables = "8"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
        

    End With

Next
End Sub

What I did is just add the q value directly into the function and used a string variable to determine to wich cell he needs to copy to


Jos

 

[krishhi]

Excellent.

Thank you so much

 

[JosV]

Your welcome