R
R Krishna
Hi
I am trying to collect some data from the web.
I have to collect it from these sites
http://www.eci.gov.in/elec91/states/S13/const01.htm
http://www.eci.gov.in/elec96/staticrep/ls1296/S13/const02.htm
http://www.eci.gov.in/ge98/states/S13/const01.htm
http://www.eci.gov.in/ge1999/pollupd/pc/states/s13/const01.htm
each of these links take you the first Constituency, there are 48
Constituency, so I have a sheet called "names" which looks like this all the
way to row 48, since all these place have the first part common,
http://www.eci.gov.in/ I have put what comes between this and ".htm" in to 4
Col, each Col is for a year.
Col A Col B
(GEN99) (GEN98)
ge1999/pollupd/pc/states/s13/const01 ge98/states/S13/const01
ge1999/pollupd/pc/states/s13/const02 ge98/states/S13/const02
ge1999/pollupd/pc/states/s13/const03 ge98/states/S13/const03
Col C ColD
(GEN96) (GEN91)
elec96/staticrep/ls1296/S13/const01 elec91/states/S13/const01
elec96/staticrep/ls1296/S13/const02 elec91/states/S13/const02
elec96/staticrep/ls1296/S13/const03 elec91/states/S13/const03
I have a incomplete and messy macro to try to do the job
but I have 2 problems
1.when I run the macro the Data retrived is placed insert to the A coloum
when this pushes the existing data to Col D, What I want is to append to the
existing Data, in the empty rows below.
2.I have not figured out how to make the macro loop through coloums, ie to
pick inputs from ColA "GEN99" then ColB"GEN98" and so on...
the macro is self is what I have hashed together from this help group.
Sub Maharashtra98()
Dim ctno, lstno, r,
Dim lastcol as Long, Activetcol as Long, nextrow as Long
lstcol = Cells(StartRow, Columns.Count).End(xlToLeft).Column
r = 1
nextrow = 1
With Sheets("Names")
lstno = .Range("A1").CurrentRegion.Rows.Count
End With
' New sheet for new data
With ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = Trim(CStr(Worksheets("Names").Range("A" &
r).Value))
End With
'for loop to get the results from ColA, all Rows
For r = 2 To lstno
Dim oQT As QueryTable
ctno = Worksheets("Names").Range("A" & r).Value
'Const
sURL = "URL;http://www.eci.gov.in/" & ctno & ".htm"
'Create a new worksheet for the results
With AvtiveSheet
'Sheets(Trim(CStr(Worksheets("Names").Range("A" & r).Value)))
'ActiveWorkbook.Worksheets.Add
'to find the last row in the sheet with data to append new data
nextrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
if nextrow > 5
nxtrow = nextrow + 2
endif
'Create the Web Query, with the URL
Set oQT = .QueryTables.Add(sURL, .Range(nextrow)
End With
With oQT
'Retrieve the entire page
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh False
End With
Next r
'http://www.eci.gov.in/ge1999/pollupd/pc/states/s13/const01.htm
'http://www.eci.gov.in/elec91/states/S13/const01.htm
'http://www.eci.gov.in/elec96/staticrep/ls1296/S13/const02.htm
'http://www.eci.gov.in/ge98/states/S13/const01.htm
' For loop to go for all Col in sheet "Names" not done
End Sub
If the macro does not work I will have to visit 192 web pages to manualy
copy all the data, Please help
TIA
Ravi
I am trying to collect some data from the web.
I have to collect it from these sites
http://www.eci.gov.in/elec91/states/S13/const01.htm
http://www.eci.gov.in/elec96/staticrep/ls1296/S13/const02.htm
http://www.eci.gov.in/ge98/states/S13/const01.htm
http://www.eci.gov.in/ge1999/pollupd/pc/states/s13/const01.htm
each of these links take you the first Constituency, there are 48
Constituency, so I have a sheet called "names" which looks like this all the
way to row 48, since all these place have the first part common,
http://www.eci.gov.in/ I have put what comes between this and ".htm" in to 4
Col, each Col is for a year.
Col A Col B
(GEN99) (GEN98)
ge1999/pollupd/pc/states/s13/const01 ge98/states/S13/const01
ge1999/pollupd/pc/states/s13/const02 ge98/states/S13/const02
ge1999/pollupd/pc/states/s13/const03 ge98/states/S13/const03
Col C ColD
(GEN96) (GEN91)
elec96/staticrep/ls1296/S13/const01 elec91/states/S13/const01
elec96/staticrep/ls1296/S13/const02 elec91/states/S13/const02
elec96/staticrep/ls1296/S13/const03 elec91/states/S13/const03
I have a incomplete and messy macro to try to do the job
but I have 2 problems
1.when I run the macro the Data retrived is placed insert to the A coloum
when this pushes the existing data to Col D, What I want is to append to the
existing Data, in the empty rows below.
2.I have not figured out how to make the macro loop through coloums, ie to
pick inputs from ColA "GEN99" then ColB"GEN98" and so on...
the macro is self is what I have hashed together from this help group.
Sub Maharashtra98()
Dim ctno, lstno, r,
Dim lastcol as Long, Activetcol as Long, nextrow as Long
lstcol = Cells(StartRow, Columns.Count).End(xlToLeft).Column
r = 1
nextrow = 1
With Sheets("Names")
lstno = .Range("A1").CurrentRegion.Rows.Count
End With
' New sheet for new data
With ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = Trim(CStr(Worksheets("Names").Range("A" &
r).Value))
End With
'for loop to get the results from ColA, all Rows
For r = 2 To lstno
Dim oQT As QueryTable
ctno = Worksheets("Names").Range("A" & r).Value
'Const
sURL = "URL;http://www.eci.gov.in/" & ctno & ".htm"
'Create a new worksheet for the results
With AvtiveSheet
'Sheets(Trim(CStr(Worksheets("Names").Range("A" & r).Value)))
'ActiveWorkbook.Worksheets.Add
'to find the last row in the sheet with data to append new data
nextrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
if nextrow > 5
nxtrow = nextrow + 2
endif
'Create the Web Query, with the URL
Set oQT = .QueryTables.Add(sURL, .Range(nextrow)
End With
With oQT
'Retrieve the entire page
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.Refresh False
End With
Next r
'http://www.eci.gov.in/ge1999/pollupd/pc/states/s13/const01.htm
'http://www.eci.gov.in/elec91/states/S13/const01.htm
'http://www.eci.gov.in/elec96/staticrep/ls1296/S13/const02.htm
'http://www.eci.gov.in/ge98/states/S13/const01.htm
' For loop to go for all Col in sheet "Names" not done
End Sub
If the macro does not work I will have to visit 192 web pages to manualy
copy all the data, Please help
TIA
Ravi