Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
webquery and solver macros | Excel Discussion (Misc queries) | |||
Prevent error messagebox when refresing querytable from the web fails | Links and Linking in Excel | |||
how to make webquery based on cell | Excel Worksheet Functions | |||
Webquery fieldnaming... | Links and Linking in Excel | |||
Webquery | Excel Discussion (Misc queries) |