Web query read the web address from a cell?

[clipro]

Hi everybody,

I have the following task

1. in sheet 1, column A starting A1 I have addresses of web pages. all of them are one and the same except the last digits. Sth like this

www.datapage.com/source1
www.datapage.com/source2
...
...
www.datapage.com/source16
www.datapage.com/source17
etc

2. in sheet 2 I have to import the data from these web sites as it follows
from www.datapage.com/source1 starting from cell A1
from www.datapage.com/source2 starting from cell A101
from www.datapage.com/source3 starting from cell A201
etc.

The question: I am trying to write a Macro that tooks the address from sheet 1 cell A1 and import the data through web query using the address in the cell and import it in sheet 2 starting cell A1. Then loop and took the address from cell A1 in Sheet 1 and put it into sheet 2 cell A101 etc.

I hope that my explanation is clear.

Thank You in advance!

 

[clipro]

hi again,

I really try doo something but I cannot force it to read from the cells. The Macro still is using just the text

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/2/2008 by user
'

'
Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "http://stratsimir.exsisto.com/names/"
Range("A14").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://stratsimir.exsisto.com/names", Destination:=Sheets("Sheet2"). _
Range("A1"))
.Name = "names"
.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:=False
End With
Range("A15").Select
End Sub

PLEASE SOMEBODY HELP WITH THIS MACRO

 

[Jonmo1]

Perhaps

With ActiveSheet.QueryTables.Add(Connection:=Sheets("Sheet1").Range("A1").Value, _
Destination:=Sheets("Sheet2").Range("A1"))

 

[clipro]

Thank You for the reply jonmo1,


I've tried the code but I made some mistake because I receive a Run time error "The destination range is not o the same worksheet that the Query table is being created on". That happens when I started the code when the active sheet is Sheet 1

When the active Sheet is Sheet 2 the RTE is 1004 defined error

Where I am wrong?

 

[Jonmo1]

make sure your addresses in Sheet1 start with http://

your example showed them starting with www.
www.datapage.com/source1
www.datapage.com/source2
...

Instead, put
http://www.datapage.com/Source1"
etc..

 

[clipro]

yes it starts with http://

to avoid misunderstanding I posted my code again. Please see it:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/2/2008 by user
'

'
Selection.Copy
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "http://stratsimir.exsisto.com/names/"
Range("A14").Select
With ActiveSheet.QueryTables.Add(Connection:=Sheets("Sheet1").Range("A1").Value, _
Destination:=Sheets("Sheet2").Range("A1"))
.Name = "names"
.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:=False
End With
Range("A15").Select
End Sub


BTW I am not feel very comfortable with VBA so I suppose that the mistake is in my code

Thanks for the patient

 

[Jonmo1]

With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet1").Range("A1").Value, Destination:=Range("A1"))

 

[clipro]

Thank you for the quick reply jonmo1,

for my regret I have the same error "The destination range is not o the same worksheet that the Query table is being created on".

Are you sure that everything else in the code is OK?

 

[clipro]

IT WORKS

just I have to start it when I am in sheet 2

Thank You very much jonmo1!!!!

 

[Jonmo1]

Change
With ActiveSheet

to

With Sheets("Sheet2")