Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

URL Riddle

  Asked By: Jose    Date: Nov 21    Category: MS Office    Views: 797
  

I have written 2 macroes--macro2 and macro3 (below).

Macro2 works fine, but I need to manipulate the web address to
increment team numbers, hence macro 3.

After manipulating the web address, macro 3 errors out="error 1004:
The address of this site is not valid".

After studying this extensively, the web address in macro3 which is
"not valid" appears to be identical to the web address in macro2,
which works fine.

Can somebody tell me why macro3 doesn't work? Thank you. Code below:
..........................................................
Sub Macro2()
'
'ok so far, does all except the team number incrementation
'

'
With
ActiveSheet.QueryTables.Add(Connection:="URL;http://www.covers.com/pageLoader/pa\
geLoader.aspx?page=/data/ncb/teams/pastresults/2006-2007/team2174.html&t=0"
_
, Destination:=ActiveCell)
.Name = "team2174.html&t=0"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Rows("1:208").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
End Sub
................................................................
Sub Macro3()
'
' with the team number incrementation
TmNumb = 2174
Dim b As String

Do Until TmNumb = 2551
TmNumb = TmNumb + 1
a =
"http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/ncb/teams/pastresul\
ts/2006-2007/team"
b = TmNumb
c = ".html&t=0"
d = a & b & c
ActiveCell = d

e = "team" & b & c
With ActiveSheet.QueryTables.Add(Connection:="URL;d" _
, Destination:=ActiveCell)
.Name = e
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveCell.Rows("1:208").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Selection.Value = TmNumb
Selection.Offset(1, -1).Select
Sheets("Sheet1").Select
Loop

End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Bryson Smith     Answered On: Nov 21

Here's your answer:



Sub Macro3()

...

With ActiveSheet.QueryTables.Add(Connection:="URL;d" _

, Destination:=ActiveCell)



SHOULD BE



Sub Macro3()

...

With ActiveSheet.QueryTables.Add(Connection:="URL;" & d _

^^^^^

, Destination:=ActiveCell)

 
Didn't find what you were looking for? Find more on URL Riddle Or get search suggestion and latest updates.




Tagged: