Range Query
Oct 26, 2008
I have a range containing the week no, date, person and have named the range PersonSearch.
I want to search for this information in another worksheet named Notify.
I'm having trouble with my code, is it because the data is mixed ?
My code is
,Dim FindP As Range,
,Set FindP = Worksheets("Data").Range("PersonSearch").Value,
,If FindP = "" Then End,
' Goes to the start of the information
, Sheets("Notify").Select,
,Range("A2").Select,
' Tests current cell against FindP information
,Do
, If ActiveCell.Value = FindP Then,
,Call GettingData,
,Exit Sub,
,Else: ActiveCell.Offset(1, 0).Select,
,End If,
,Loop Until ActiveCell.Value = "",
View 9 Replies
ADVERTISEMENT
Feb 27, 2009
I'm writing a couple of macros which require selection of a range. The user could either select the range before executing the macro or if range has not been pre-selected, an inputbox should pop-up through which the user can select required range.
View 9 Replies
View Related
Feb 17, 2014
I'm trying to develop a macro that will run through a list of names and cities and will put the output next to them. Here's how my data is laid out:
First Name
Last Name
City
Bob
Smith
New York
Joe
Blow
Seattle
To the right of each person is where I want the query output to go. I sort of pieced together a macro but I get stuck on the query part - I don't know how to query multiple rows. I thought of just running the query for each row, but that will probably be too labor intensive for the computer running a query each time.
[Code] .....
View 7 Replies
View Related
Jul 15, 2014
Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.
[Code].....
This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?
By using an array:
[Code].......
or
by using a range of cells:
View 8 Replies
View Related
Feb 27, 2012
I have a spreadsheet with several worksheets in it. The main sheet contains a calendar view that is fed from a separate sheet with holiday date ranges. This aspect works well and displays correctly in the main calendar view.
I am now adding in additional conditional formatting to each cell. As a first example i want the calendar to display bank holiday days by colouring the cell (lets say black). I am using conditional formatting only and have used the following formula;
IF(C6=Holidays,1,0)
Holidays is a named range, separate sheet (A2:A13).
C6 is the date value field (runs C6 through AN6 - perpetual calendar)
It evaluates the cell (i can see, TRUE,FALSE,FALSE,FALSE,...) and will only shade the cell if the date value field is equal to the first date in the Holidays named range. if this is not the case (FALSE,TRUE,FALSE,FALSE,...) the field value is set to "0".
how i can change this, entered as conditional format formula, to set the cell to "1" if any are evaluated as TRUE regardless of where they come in the name named range.
I want to additionally add in, from separate worksheets in the same workbook, face to face meetings and conference calls. I have assumed that i will use the same method to do this as Bank Holidays.
View 4 Replies
View Related
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
Jun 21, 2007
Is there a way to put a floating date range in the criteria much the same as can be done in excel ie greater than Today() but less than Today()+3? I don't want to keep going in and adjusting the query if I don't have to and if I don't keep the range tight there is too much data.
View 9 Replies
View Related
Jul 18, 2006
I am trying to count the occurences of combinations within a range defined by contiguous cell values in one column. My problem lies with setting the value of variables that are queried within the defined range. My macro should;
1)Set ComboValue1 & ComboValue2 values to A1 & B1 cells values respectively
2)define range to be searched by how many contiguous values there are in column D
3)search column E for 2 figure combination (defined by A1 & B1). If present increment counter by 1 and add to column C (to be visible in worksheet). If no match carry on
4)define new range by next set of contiguous values in column C
5) step 3
6)when all possible contiguous ranges defined and searched from column c, perform again on next combination down columns A & B with refreshed occurence counter
7)finish when all combinations are searched for
Sub CountComboOccurence()
Dim ListCounter As String
Dim ProgCounter As String
Dim Counter As Integer
Dim ComboValue1 As String
Dim ComboValue2 As String
Dim Rng As Range
Application. ScreenUpdating = False
ListCounter = 0
ProgCounter = 0
Counter = 0
ComboValue1 = 0.......................
View 2 Replies
View Related
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
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
Apr 29, 2014
I have been working on some ODBC queries in Excel 2010.
First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.
Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.
[Code] .....
Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.
View 6 Replies
View Related
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
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
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
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
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
Jul 19, 2006
Is it possible to open microsoft query without opening Excel or Query Wizerd.
View 3 Replies
View Related
Feb 17, 2007
Is it possible to use the Web Query function in Excel on a PDF file?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
