Dynamic variables in .WebTables

[andym]

Dear All,

I wish to turn the line of code .WebTables = "4,8,12,16,20,24,28"
into something more dynamic.

In the above case I am pulling down 7 tables from the one web page. As
you can see they are every 4th table starting from table 4.

Sometimes, however, the number of tables change. If there are 10 tables
then I would be pulling down the following...

.WebTables = "4,8,12,16,20,24,28,32,36,40"

I want to have my code start off with an input box that I can state the
number of tables I need. I then need this variable to help create an
array that dynamically sets the .WebTables ... similar to .WebTables =
arrTables ... if this is possible to do...

Could anybody either confirm if this could be done, offer an
alternative, or guide me in anyway?

Regards,

andym

 

[andym]

I have answered my own question after much agony!!

For those wishing to know, I have done the following .. with certain
exact details missing...

Sub HTMLTables()

Dim xcode As String
Dim iThings As Integer
Dim iYear As Integer
Dim iMonth As Integer
Dim iDay As String
Dim arrTables() As Variant
Dim nTable As Integer
Dim j As Integer
Dim testString As String

xcode = InputBox("Please Enter X Code")
iThings = InputBox("Please Enter The Number of Things")
iYear = InputBox("Year")
iMonth = InputBox("Month")
iDay = InputBox("Day")

j = 1

ReDim arrTables(1 To iThings)
For i = 1 To iThings
nTable = j * 4
arrTables(i) = nTable
j = j + 1

Next i

testString = ""

i = 2

testString = testString & arrTables(1)
For k = 2 To iThings
testString = testString & "," & arrTables(i)

i = i + 1

Next k

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.webpage/" & iYear & "/" & iMonth & "/" & iDay &
"/" & xcode & ".html", Destination:=Range("A1" _
))
.Name = xcode
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = testString
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = True
.WebDisableDateRecognition = True
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

Through an array I created a string which carries the correct number of
elements realting to the input iThings, and each of those elements have
been multipled by 4 to give me the correct table numbers required.

The code may be rough, but it now works.

Again, if anybody else has a cleaner or better suggestion, then by all
means please publish it.

Regards,

andym

 

[Ron Coderre]

You might want to use something a bit more concise:

intTblCount = InputBox(Prompt:="How many web tables?", Title:="Set Table
Count")
strWebTables = "4"
For intCtr = 2 To intTblCount
strWebTables = strWebTables & "," & intCtr * 4
Next intCtr


Does that help?

***********
Regards,
Ron

 

[andym]

Ron,

thanks ... that is much more concise ... most appreciated.

Regards,

andym....