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

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

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

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