Macro to enter url directly into web query

[joand]

I have about 60 URLs in Column D (starting in D2 ending in D61) and I need to enter each one into a web query (macro is shown below for querying a single url). The variable is "firstemployeename.html" and the values could be found on column D. I need a single sheet for each url.

Could someone guide me through?

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.mysite.com/admin/firstemployeename.html", Destination _
:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=18
End Sub

 

[ravishankar]

Hi
try the following codes.

Sub employee()
Dim a As Integer, x As Integer
Dim b As String
x = Worksheets("sheet1").Cells(Rows.Count, 4).End(xlUp).Row
For a = 2 To x
b = Worksheets("sheet1").Cells(a, 4)
Sheets.add.Name = b
With ActiveSheet.QueryTables.add(Connection:= _
"URL;http://www.mysite.com/admin/" & b, Destination _
:=Worksheets(b).Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next a
End Sub

Run the macro. it will synthesise url from list of employees in col D of sheet 1 and adds a sheet for each and populates them with the results of the query. I have not tested it.
Ravi

 

[r.baldwin7960]

I have a similar problem. I'm distributing a tracking form for my employees, whom each have their own personal site for the data used in the sheet.

On sheet1 cell D13, the user inputs their web address, how can I get the web query on sheet2 to pull the url from that cell?

 

[r.baldwin7960]

On sheet1 cell D13,.....

Should be "On sheet1 range D13:I13"

(silly merged cells...)

 

[ravishankar]

Hi
try the following codes

Sub data_from_web()
Dim a As Integer
Dim b As String
b = Worksheets("sheet1").Cells(13, 4)
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://" & b, Destination _
:=Worksheets("Sheet2").Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next a
End Sub

This will pull data from the website shown in D13 and pastes to sheet2
Ravi

 

[r.baldwin7960]

Will this continue to work if I set it to a button on Sheet1? Also, the query page is actually named "Data". I won't get a chance to test it for another 13 hours or so. (It's 11:30pm where I am).

Thanks again for the help!

 

[ravishankar]

Hi
Attaching macro to button causes no problems. you need to change sheet2 to "Data" for queried info to be pasted.
Ravi