[SOLVED] Problem with Querytable destination, using Web Query with multiple URLs

Hi All,

I am trying to use a web query to convert a chemical abstracts number (CAS) to SMILES string using the translator at NCI. To do this, a URL is formatted like so: "http://cactus.nci.nih.gov/chemical/structure/100-00-5/smiles", where 100-00-5 is a CAS number. Going to that link will return the SMILES string for that chemical (1-chloro-4-nitrobenzene) which is C1=C([N+]([O-])=O)C=CC(=C1)Cl.

Starting at D3 and going down the column, I have several hundred unique URLs which I concatenated from the CAS number and are formatted to return a SMILES string. I want to return the SMILES for each chemical in the adjacent cells located in Col E. When I tested the following code (just for the chemicals in D3:D5), it returns the SMILES code but places them in E3, F3, G3 rather than filling the adjacent cells down Column E.

I've searched around for a solution, but am fumbling around in the dark. This is my first attempt at VBA as you can tell. Any help is very much appreciated.

Sub CactusQuerytoSmiles()
Dim URL As Range

For Each URL In Range("D3:D5").Cells
   With ActiveSheet.QueryTables.Add(Connection:= _
      "URL; " & URL, Destination:=Range("E3"))

.Name = "SmilesQuery"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next
End Sub