Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with macro to make it loop through coloums
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/staticr...13/const02.htm http://www.eci.gov.in/ge98/states/S13/const01.htm http://www.eci.gov.in/ge1999/pollupd...13/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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Loop | Excel Discussion (Misc queries) | |||
Loop to run macro each row | Excel Discussion (Misc queries) | |||
loop a macro | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) | |||
macro loop | Excel Discussion (Misc queries) |