Web query vba

Hi everyone,

I am struggling with using a web query in excel and I hope someone can help resolve these issues.

First of all here is the code I have so far and it works in pieces but not together:

Option Explicit

Sub Cochrane_Database()

Dim ActNm       As String
Dim c           As Range
Dim CochraneWeb As String
 
 Range(Range("I2"), Range("A65536").End(xlUp).Offset(0, 8)) = _
    "=CONCATENATE(""""""URL;http://www.mrw.interscience.wiley.com/cochrane/clsysrev/articles/"",RC[-8],""/abstract.html"""""")"

    For Each c In Range("I2:I" & Range("A" & Rows.Count).End(xlUp).Row)

    c = CochraneWeb

            ' Create temp sheet to import web page to
                With ActiveWorkbook.Sheets
                    .Add after:=Worksheets(Worksheets.Count)
                End With

                ActNm = ActiveSheet.Name
                On Error Resume Next

                ActiveSheet.Name = "Temp"

            ' Import web query from Cochrane Website
                With ActiveSheet.QueryTables.Add(Connection:= _
                    CochraneWeb _
                    , Destination:=Range("$A$1"))
                    .Name = "abstract"
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .BackgroundQuery = True
                    .RefreshStyle = xlInsertDeleteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .WebSelectionType = xlEntirePage
                    .WebFormatting = xlWebFormattingNone
                    .WebPreFormattedTextToColumns = True
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=True
                End With
             
                Range("A1").Offset(10, 0).Copy
                Sheets("Cochrane database").Select
                ActiveCell.Offset(0, -5).PasteSpecial Paste:=xlPasteValues

                Sheets("Temp").Select

                ActiveCell.Offset(11, 0).Range("A1").Copy
                Sheets("Cochrane database").Select
                ActiveCell.Offset(0, 3).PasteSpecial Paste:=xlPasteValues

            ' Stop the pop-ups
                Application.DisplayAlerts = False

            ' Delete the Temp sheet
                Sheets("Temp").Delete

            ' Start the pop-ups again
                Application.DisplayAlerts = True

    Next
End Sub

The first part of the code creates URLs in Column I. Then I try to open each URL and import a string of data from the same cell on the temporary worksheet back to the database.

The problems I am facing are:

1) The loop works, but erases the URLs. I don't understand why???

2) I can't get the URL to be sent to the web query. (I don't have any experience with this before)

3) The vba does not wait for the web query to load the web page and continues to the copy/paste portion. Of course it copies and pastes blank cell. Is there a way to tell it to wait until the web query is completely loaded before continuing with the next line of code?

Thanks,

abousetta