Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Web Query, from very long URL
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 didnt 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. Im not sure about the limit for characters in a single cell, but Im 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 cant tell for sure. Does anyone have any ideas about this? Thanks, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Web Query, from very long URL
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" wrote: 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 didnt 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. Im not sure about the limit for characters in a single cell, but Im 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 cant tell for sure. Does anyone have any ideas about this? Thanks, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Web Query, from very long URL
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--- -- RyGuy "Don Guillett" wrote: 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")) -- Don Guillett Microsoft MVP Excel SalesAid Software "ryguy7272" wrote in message ... 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 didnt 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. Im not sure about the limit for characters in a single cell, but Im 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 cant tell for sure. Does anyone have any ideas about this? Thanks, Ryan--- -- RyGuy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web Query - row is too long | Excel Programming | |||
Query text too long | Excel Programming | |||
How can I use a long URL in a web query? | Excel Discussion (Misc queries) | |||
How to enter a long address to new web query | Excel Discussion (Misc queries) | |||
MyODBC & Excel VBA - Too long query ? | Excel Programming |