Webquery [querytable] long url
 

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.

Webquery [querytable] long url
 

example?

--
Don Guillett
SalesAid Software

"LightShow" wrote in message
...
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.

Webquery [querytable] long url
 

http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D

"LightShow" wrote:

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.

Webquery [querytable] long url
 

Proprietary?

--
Don Guillett
SalesAid Software

"LightShow" wrote in message
...
http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D

"LightShow" wrote:

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.

Webquery [querytable] long url
 

Here is my code...


=Get_Query_Data("Sheet1",B7)
B7 =
http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D




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_strConne ction, _
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.



"Don Guillett" wrote:

Proprietary?

--
Don Guillett
SalesAid Software

"LightShow" wrote in message
...
http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D

"LightShow" wrote:

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.

Webquery [querytable] long url
 

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

"LightShow" wrote in message
...
Here is my code...


=Get_Query_Data("Sheet1",B7)
B7 =
http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D




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_strConne ction, _
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.



"Don Guillett" wrote:

Proprietary?

--
Don Guillett
SalesAid Software

"LightShow" wrote in message
...
http://[subdomain].[domain].com/[companyname]/reports/top-dash/teamscorecardmonthly/CCSStatsMonthly?reportCoordinates[1]=static%7BdimensionName%3Dperson%2CgroupLevel%3DPe rson%2CrestrictLevel%3DTeam%2CrestrictValues%3D%7B %7BTeam%2C190080867%7D%7D%7D

"LightShow" wrote:

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.