Webquery [querytable] long url

[LightShow]

I am trying to create vba that will create a querytable (URL) and then run
the specified query (future use: loop through an array of url's). However,
the url is extremely long and excel keeps giving me an error. Is there any
way around this besides using tinyurl.com <-- I cannot use this service.

 

[Don Guillett]

example?

 

[LightShow]

http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}

 

[Don Guillett]

Proprietary?

--
Don Guillett
SalesAid Software
[email protected]

http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}

I am trying to create vba that will create a querytable (URL) and then
run
the specified query (future use: loop through an array of url's).
However,
the url is extremely long and excel keeps giving me an error. Is there
any
way around this besides using tinyurl.com <-- I cannot use this service.

 

[LightShow]

Here is my code...


=Get_Query_Data("Sheet1",B7)
B7 =
http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}




Public Function Get_Query_Data(a_strSheet As String, a_strConnection As
String)
Dim intErrorCount As Integer, intResponse As Integer
On Error GoTo GetQueryData_Error
intErrorCount = 0
Worksheets(a_strSheet).Activate

GetQueryData_Retry:
With ActiveSheet.QueryTables.Add(Connection:=a_strConnection, _
Destination:=Range("A1"))
.RefreshStyle = xlOverwriteCells
.Refresh (False)
End With
Data_Obtained:
Exit Function

GetQueryData_Error:
If Err.Number = 1004 Then 'This error occurs if the Web site cannot be
found
intErrorCount = intErrorCount + 1
If intErrorCount > 500 Then
intResponse = MsgBox("Error Count Exceeded", vbRetryCancel)
If intResponse = vbCancel Then
Exit Function
End If
End If
Resume GetQueryData_Retry
End If
End Function



'The code just stops due to the max number of retries.

Proprietary?

--
Don Guillett
SalesAid Software
[email protected]

http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}

I am trying to create vba that will create a querytable (URL) and then
run
the specified query (future use: loop through an array of url's).
However,
the url is extremely long and excel keeps giving me an error. Is there
any
way around this besides using tinyurl.com <-- I cannot use this service.

 

[Don Guillett]

I guess I would have to see the workbook. Perhaps you are building up too
many external names that need to be deleted.
Or, perhaps using the same data sheet for each and copying the data to where
it needs to go.
--
Don Guillett
SalesAid Software
[email protected]

Here is my code...


=Get_Query_Data("Sheet1",B7)
B7 =
http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}




Public Function Get_Query_Data(a_strSheet As String, a_strConnection As
String)
Dim intErrorCount As Integer, intResponse As Integer
On Error GoTo GetQueryData_Error
intErrorCount = 0
Worksheets(a_strSheet).Activate

GetQueryData_Retry:
With ActiveSheet.QueryTables.Add(Connection:=a_strConnection, _
Destination:=Range("A1"))
.RefreshStyle = xlOverwriteCells
.Refresh (False)
End With
Data_Obtained:
Exit Function

GetQueryData_Error:
If Err.Number = 1004 Then 'This error occurs if the Web site cannot
be
found
intErrorCount = intErrorCount + 1
If intErrorCount > 500 Then
intResponse = MsgBox("Error Count Exceeded", vbRetryCancel)
If intResponse = vbCancel Then
Exit Function
End If
End If
Resume GetQueryData_Retry
End If
End Function



'The code just stops due to the max number of retries.

Proprietary?

--
Don Guillett
SalesAid Software
[email protected]

http://[subdomain].[domain].com/[co...Level=Team,restrictValues={{Team,190080867}}}

:

I am trying to create vba that will create a querytable (URL) and then
run
the specified query (future use: loop through an array of url's).
However,
the url is extremely long and excel keeps giving me an error. Is
there
any
way around this besides using tinyurl.com <-- I cannot use this
service.