New Web Query, from very long URL

[ryguy7272]

I am trying to import data from a very long URL; total number of characters
is 696. I recently found some code on this DG, but it didn’t allow me to do
what I wanted to do. The setup is as follows:

Sub setlink()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Set r3 = Sheet1.Range("A3")
Range("A6") = Sheet1.Hyperlinks.Add(r1, r2, r3.Value)
Call Macro1
End Sub

Sub Macro1()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Range("A6"), _
Destination:=Range("A10"))
.Name = "Main"
.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 = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

When I try to run the macro I get this message:
‘Run Time Error 1004. Application-defined or object-defined error.’

Then string is split up into 228 characters, 224 characters, and 244
characters, in three separate cells. I’m not sure about the limit for
characters in a single cell, but I’m guessing it is 255 characters, but it
may be as much as 1,024 characters (according to Excels ‘Specifications and
limits’). Anyway, I am guessing that the string is not being concatenated
properly, but I can’t tell for sure. Does anyone have any ideas about this?


Thanks,
Ryan---

 

[Don Guillett]

Without seeing it I would think something like. Best to see it

myurl=Sheet1.Range("A1") & Sheet1.Range("A2") & Sheet1.Range("A3")

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myurl & ", _
Destination:=Range("A10"))

 

[Joel]

Why are you ADDING????

from
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Set r3 = Sheet1.Range("A3")
Range("A6") = Sheet1.Hyperlinks.Add(r1, r2, r3.Value)

to
Set r1 = Sheet1.Range("A1")
Set r2 = Sheet1.Range("A2")
Set r3 = Sheet1.Range("A3")
Range("A6") = r1 & r2 & r3

 

[ryguy7272]

Good questions Joel. Wish I had a good answer for that. Basically, I don't
know; just failed to see the errors of my ways. When I saw your post I
realized my mistake.

Thanks!!!
Ryan---