Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in a Macro
Hi all,
Here is a small macro to pull data into from web. Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://incometaxindiaefiling.gov.in/...n/tanpage.jsp? tan=0", _ Destination:=Range("A1")) .Name = "tanpage.jsp?tan=1" .FieldNames = True .PreserveFormatting = True .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "9" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .Refresh BackgroundQuery:=True End With End Sub It is working perfectly for downloading data. My Problems is there are n number of such pages, which need to be downloaded. How can I use loops or something else so that parameter 0 in first line is increased automatically by one till in reaches n and Destination range by 20 cells (i.e., A21, A41 and so on). New destination range would be here equal to (n*20)+1 Regards CA Kanwaljit Singh Dhunna India |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in a Macro
from tan=0", to tan=" & loopcount, " wrote: Hi all, Here is a small macro to pull data into from web. Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://incometaxindiaefiling.gov.in/...n/tanpage.jsp? tan=0", _ Destination:=Range("A1")) .Name = "tanpage.jsp?tan=1" .FieldNames = True .PreserveFormatting = True .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "9" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .Refresh BackgroundQuery:=True End With End Sub It is working perfectly for downloading data. My Problems is there are n number of such pages, which need to be downloaded. How can I use loops or something else so that parameter 0 in first line is increased automatically by one till in reaches n and Destination range by 20 cells (i.e., A21, A41 and so on). New destination range would be here equal to (n*20)+1 Regards CA Kanwaljit Singh Dhunna India |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in a Macro
from tan=0", to tan=" & loopcount, " wrote: Hi all, Here is a small macro to pull data into from web. Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://incometaxindiaefiling.gov.in/...n/tanpage.jsp? tan=0", _ Destination:=Range("A1")) .Name = "tanpage.jsp?tan=1" .FieldNames = True .PreserveFormatting = True .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "9" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .Refresh BackgroundQuery:=True End With End Sub It is working perfectly for downloading data. My Problems is there are n number of such pages, which need to be downloaded. How can I use loops or something else so that parameter 0 in first line is increased automatically by one till in reaches n and Destination range by 20 cells (i.e., A21, A41 and so on). New destination range would be here equal to (n*20)+1 Regards CA Kanwaljit Singh Dhunna India |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in a Macro
from tan=0", to tan=" & loopcount, " wrote: Hi all, Here is a small macro to pull data into from web. Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://incometaxindiaefiling.gov.in/...n/tanpage.jsp? tan=0", _ Destination:=Range("A1")) .Name = "tanpage.jsp?tan=1" .FieldNames = True .PreserveFormatting = True .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "9" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .Refresh BackgroundQuery:=True End With End Sub It is working perfectly for downloading data. My Problems is there are n number of such pages, which need to be downloaded. How can I use loops or something else so that parameter 0 in first line is increased automatically by one till in reaches n and Destination range by 20 cells (i.e., A21, A41 and so on). New destination range would be here equal to (n*20)+1 Regards CA Kanwaljit Singh Dhunna India |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do until loop with use of another macro in loop | Excel Programming | |||
macro loop | Excel Discussion (Misc queries) | |||
VBA Macro Loop | Excel Programming | |||
I Need Help with my loop macro | Excel Programming |