Change Web Query

Jun 9, 2006

The spreadsheet I have designed retrieves stats and data from individual player pages and stores them in an excel workbook.

I have used 20 seperate web queries, to import the data for each of my players (from an online cricket simulation game). This works fine, but my problem is that I want to make it usable for other players.

Player pages are titled playerinfo.pl?playerid=1020, through to playerinfo.pl?playerid=1039.

Ideally Id like the user to enter his lowest playerid into a cell, and then create the web queries for that manager's players.

I'm not sure if this is a possible scenario, or if I need to use a repetition loop in VBA.

Im using Excel 2002.

View 4 Replies


ADVERTISEMENT

Macro To Change The Query

Mar 12, 2007

I have a Excel database query which of which i import into Sheet 1. On a daily basis I need to edit this query and change a critea field to yersterdays date. Is there a way in which I could run a macro to change this query for yesterdays date without having to manually go into the query?

I have tried to run the macro, however I can only run this if I have a specific date in my code e.g. 11/03/2007 and not a formula to show yesterdays date.

View 9 Replies View Related

Change Case And More Checks In Single Query

Jun 7, 2013

Need a quick macro for the following in sheet 1

Convert data to proper for columns- E, Z and ADConvert data to upper for columns- A, I, X and ACHighlight Special characters with cell colour as Red for (!@#$%^&*:”<>+_’;\/?`~-)Find & replace % in the current sheet with blank.ran.

View 9 Replies View Related

List - Query- The Value Into The D Column Should Change Automatically

Dec 11, 2007

I want whenever i select any dept in the B column say ID or Design... the value into the D column should change automatically...

say if i select in B5 as ID then D5 value should show me India

******** ******************** ************************************************************************>Microsoft Excel - resource_sheet_11_Dec_07_V2.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDE1Project(Client - Name- Length)Dept.Resource 1-Oct2 3Template 4 5New Course Sample Template ID 6New Course Sample Template ID 7New Course Sample Template DesignDesign Pool 8New Course Sample Template BuildBuild Pool Project Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Change A Access Query Criteria Using A VB Code

Oct 14, 2008

I looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?

My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query

I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet

Sub Change_Criteria_And_Run_Query()

Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")

mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)

>>>>>The above line of code I have problems to get right!

mydbase.DoCmd.RunMacro "MyQuery"

End Sub

View 9 Replies View Related

Auto Change Data Range When Query Is Updated?

Dec 9, 2013

What I have is a database of measurements taken, where X-axis is distance across a surface and Y-axis is measuring deformation to that surface. Each measurement is actually a group of data points from one sweep across the surface on a certain day, resulting in a line plot for that group. What I'm trying to do is create a "checksheet" so that you can overlay plots from multiple dates on the same grid to compare how the surface has changed with each measurement. I've got the checksheet part working properly, and I can toggle things on/off as I like as long as my data doesn't change.

The rub is that this data is gathered via query, and each measurement doesn't have the same # of data points. If I update the query to look at a different date range, the plots are now off because the old data range doesn't match the new data.

Is there any way of changing the data range when a query gets updated, or a way to tie it to a function?

View 6 Replies View Related

Stale Pivot Item Values After Query Change

Nov 2, 2005

I have a spreadsheet with a pivot table that is based on a table within an SQL Server database. Every month a stored procedure is run creating a new table. Using the pivot table wizard I can modify the underlying query to access the new table. The pivot table functions properly with the new data.

I also have a macro that I created that will cycle through all the combinations of page field pivot item values, setting the page field currentpage, do some calculations, and save the results.

This is bombing because it seems that some old date values from the previous month's table are still in the pivot items and when my macro attempts to set the page field's currentpage to one of these values it generates Run-time error '1004' Unable to set the _Default property of the PivotItem class.

I have tried putting a pivotcache.refresh in but that doesn't seem to help.

View 3 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this
WEB
1
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12

Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View 9 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View 2 Replies View Related

MS Query - Parameters (parameters On A Query But Use A Wildcard To Return All Instances)

Nov 17, 2009

Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.

View 4 Replies View Related

Web Query

Mar 24, 2009

i import a table from the internet through web query and i reformat the cells to suit my liking, but when i refresh the data the cell formatting goes back to how it started. how do i make it so it always keeps that same cell formatting?

View 11 Replies View Related

Query

Jul 19, 2006

Is it possible to open microsoft query without opening Excel or Query Wizerd.

View 3 Replies View Related

Web Query A Pdf?

Feb 17, 2007

Is it possible to use the Web Query function in Excel on a PDF file?

View 2 Replies View Related

Excel 2007 :: Change All Matching Value Based On Number Change In Column?

Feb 19, 2014

I have a situation that I cannot get my hands on. I have a set of Project Numbers in my Column A. (Will Try to post an example).

I need that everytime I change a number in my Column B that it will change all the numbers, in my column B, based on the matching Text in my Column A.

Demo1.xlsm

View 14 Replies View Related

Worksheet Change To Function To Add Text To Cell Which Initially Triggered Change

Jan 10, 2014

i have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.

The existing code i have is:

Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText As String
MyText = Environ("username")
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:TAOffshoreTAOffshoreTreasuryRecsGeneralCommit ID''s for control Sheet - Do not move or delete[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"

[code].....

I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value "" Then' code?

View 3 Replies View Related

Access Query

Dec 8, 2008

I'm trying to build a query which matches two tables which say has a number like A#### , I want the query to give me the A number's which do not belong in the second table but which do in the first table. i believe i need an SQL query to do this?

View 2 Replies View Related

Ranking Query

Apr 30, 2009

I have a spreadsheet which we use to rank scores for our golf society.

At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.

From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.

I have attached the file.

View 9 Replies View Related

Re-using Query Names

Oct 18, 2009

how I would be able to weed out and remove previous references to query names.

For example if I have an external data query named "qry1 by week", then remove the query, then create another one, how do I use the same query name again? Surely there's a reference to it somewhere, that I can eliminate?

View 10 Replies View Related

Parameter Query

Sep 16, 2007

Here it goes.

My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.

Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...

If possible, i just want a command button that calls the parameter prompt.

View 9 Replies View Related

Web Query With QueryTables

May 30, 2009

I am having problems getting to work a web query in a VBA macro. The goal is simply to get a value retrieved from a web query into an excel cell.

The below code works well with standard html pages (like replacing the url with www.google.com). However, the page I want to retreive (a query at geonames.org that retreives an altitude value for given lattitude/longitude values) does not contain any HTML headers, but just a number in plain text. In this case the below query does not returns anything. I tried different formating options for the query but up to nownothing works.

View 3 Replies View Related

Trying To Delete A Query

Oct 7, 2009

What my excel sheet is doing is importing a bunch of data from a query. I have all my queries linked to buttons through macros. There are going to be 20-30 buttons that are linked to a query, and each query will produce a different amount of data(in this case, account numbers). When pressing a button linked to a query(i also have one 'Clear' button that just deletes the data, thats all), i use a macro to delete the previous query so it can import the new query into the same cell. The problem I am having is that when a button is pushed and there is no query, I am getting an error message. Here is my original code(macro), and the underlined code is where i am getting the error

View 3 Replies View Related

How To Create Web Query

Dec 28, 2012

I need to create a web query.

I have lot of post codes which needs to be checked in a web page and the output column needs to be updated in the spreadsheet.

Link: - Find UK Postcodes Inside a Radius

for example: - The post code WD18 1TB will be there in Excel Sheet this has to be copied and pasted in the link in column

Step 2 : Click on map OR Place radius by location name or postcode

and Step 1 : Radius should be updated as 10 and then the Draw Radius needs to be clicked. Then the output needs to be copied (in this case the output is: AL2,HA1,HA2,HA3,HA4,HA5,HA6,HA7,HP3,UB9,WD1,WD17,WD18,WD19,WD2,WD23,WD24,WD25,WD3,WD4,WD5,WD6,WD7) in our spreadsheet in the next sheet.

View 1 Replies View Related

How To Get Hyperlink From Web Query

Jun 24, 2014

I have some thousand web queries in an excel sheet, and now I need to get the hyerlinks from each of the query into cells in another sheet. I need this as the users in addition to looking at the data I have pulled need to access the actual webpages to look at the original report - where there are a lot of images that is not effective to put into my excel report.

What I want is to get the "name" value from the properties of the web queries, and then paste the value in a cell in another sheet. I can't seem to find any answers to how to extract the actual parameter values from a web query - but it seems like it should be possible...?

View 5 Replies View Related

Web Query Macro

Mar 6, 2007

I'm using this macro in order to bring some data to two cells.. it's currently working great...

Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://magpie.boise.itc.hp.com:8080/gip/fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M" _
, Destination:=Range("A1"))
.Name = "fileStatus.jsp?transKey=GUS8301D&fileName=GUS8301DBN2AFH3M"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

However, as you can see from the text in bold i'm currently telling the url instead of bringing it from a cell with an url. I want the macro to be able to select the url from a cell.

I'm trying to use this solution, but i keep getting a Run-time erro 5:
Invalid procedure call or argument.

connstring = Range("GUS8301!B3").Value
With ActiveSheet.QueryTables.Add(Connection:=connstring, _
Destination:=Range("A1"))

View 9 Replies View Related

Creating A Query

Mar 29, 2007

I have a table that contains data for a region broken down into North South East and West areas.

New data is added to one spreadsheet (Region).

What I would like to do is have information for the North team automatically moved (or copied via a link) to the North sheet for printing off.

View 9 Replies View Related

MS Query With Parameters

May 22, 2007

Is there any way to use an Excel cell as a parameter for MS Query.

i.e. Select fields from table where field = "cell value"

I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.

View 9 Replies View Related

Count If Query

Jul 30, 2007

Is it possible to make an if statement in a cell that only adds the cells in a collum if they are not struck through.

I use this for when an order is complete and would like my total at the bottom of the column to change when I strike out an order in the list.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved