I am "Getting External Data" from our main frame. I have a column in the data named item and only want to bring in "Items" with 5 charactors. There is not a Len filter in query and I have tried to use the "Like" with some wild cards with no success.
View 9 RepliesI am trying to create a macro that will import data from an external database; however, I want to be able to filter the data using input from the end user. The code will work fine if I do not use a variable (i.e. I type "31" to replace the second instance of "Reply" below).
Dim Reply As Integer
Reply = InputBox("Week Number", " Find Week")
ActiveWorkbook. Sheets("RawData").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=DataWarehouse;Description=DataWarehouse;UID=DataWarehouse;PWD=;APP=Microsoft® Query;WSID=WILLIAM;DATABASE=tmd" _
, Destination:=Sheets("RawData"). Range("A1"))
.CommandText = Array( _
"SELECT vOEEDownTime. …, " & Chr(13) & "" & Chr(10) & "FROM tmd.dbo.vO" _
, "EEDownTime vOEEDownTime" & Chr(13) & "" & Chr(10) & "WHERE (vOEEDownTime.downtimecode Is Not Null) And (vOEEDownTime.weekbucket = Reply)")
.Name = "Query from DataWarehouse"
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?
View 9 Replies View RelatedI have the following code for importing a query from Access to Excel.
Sub LoadAdditionalData()
Application.Run Range("AUTOSAVE.XLA!mcs02 .OnTime")
With ActiveSheet.QueryTables.Add(Connection:= Array(Array( _
"ODBC;DBQ=S:NBHD_RENEWALDatabaseNR DB_current.mdb;DefaultDir=S:NBHD_RENEWALDatabase;Driver={Driver do Microsoft Access (*.mdb)};" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _ ...............
I currently have filters set to 990 (finance code) and 3 (quarter) but I would like to be able to enter these parameters myself everytime the macro is run.
There is a way to use SQL sentences to do queries in a table in Sheet1 (A to F) to put the output in Sheet2 the values in columns A, B and E from table in Sheet1 when values in column B are equal to 5?
I mean, is possible to do this within the same workbook? or a equivalent VBA code?
I have created a pivot table in Excel 2010 that lists amounts used of particular items each month. Inside the pivot table value field settings i have changed the show values as field so that it displays the difference in usage from this month to last.
However if i now select a top 10 filter it only filters by the total number used as opposed to the difference in usage from one month to the next (which is the values displayed).
Is there a way that i can filter by top 10 by the actual values displayed in the pivot chart and not just the underlying data that creates it?
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.
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
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!
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.
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..
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 RelatedHow can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies View RelatedI have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
How to automatically filter can filter and blue-colored cells.
View 4 Replies View RelatedWriting code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
I just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
Can anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies View RelatedI m trying to use an Autofilter to filter my cells with a Number Filter of is greater of equal to 4 and is less than or equal to 5.
But as you can see I would like to customise is using a range of 2 values which i have specified in Cell P1 and Q1.
I manage to figure out how to reference to this cell, but Im not sure how can i put my ">=" and "<=" operators into my code so i can get it to work exactly how i want as shown in Code 1.
Code 1
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=">=4", _
Operator:=xlAnd, Criteria2:="<=5"
Code 2
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=Range("P1").Value, _Operator:=xlAnd, Criteria2:=Range("Q1").Value
I have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
I've a table of data which users auto-filter. I'm wanting to capture the filter criteria each time the filter is applied / re-applied. Can't seem to find an 'event' for the auto-filter.
View 4 Replies View RelatedHow do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
How do I filter multiple columns of information with the use of one dropdown arrow?
see the attached picture (jpg.) for a visual representation of exactly what I am trying to achieve. Excel Filtering.jpg
I have also attached an Excel Filtering Example spreadsheet for use in explaining how to create this type of stacking filter. Excel Filtering Example.xlsx
I have many sheets which I am using the advanced filter to filter the data to a single summary sheet. Everthing works great with one exception. I have cell comments added with relevent info to to cell values. When the advanced filter is performed the cell comments are not transfered to the summary sheet with the cell values. Is there a method to filter the data to another sheet and keep the comments?
View 5 Replies View RelatedI have the following data on sheet1
COLOUR
ART NO.
BRAND
SIZE
SURFACE
PACKING
M2 CTN
OUR CODE
INC
STK
BOOK
FREE
Grey
GCM04
CEMENTUM
60 X 60
Matt
3
1.08
239.000.02
2,450.00
[Code] ...........
Can a drop box on sheet 2 specifying tile size pull data or without drop box , I know the normal filter is the best option but trying to spoon feed the sales team since some are weak users, they would hardly know how to use the FILTER option.
Is there an easier way to use a column FILTER without using the very ugly AUTO FILTER ?
View 9 Replies View Relatedi 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