USING MS QUERY IN EXCEL 1

[BRIANVH]

Is there a way to have a cell in Excel control the criteria used in Microsoft query to pull data into Excel from a MS Access table (Office 2000)? In this case, only one record (row of data) is needed from the Access table for each Excel file. There will be many Excel files, created by copying another. It would be nice if the query criteria were controlled by entering the criteria in a cell within Exel. Currently, the data pulls in fine, but we must manually edit the query to change the criteria as each new Excel file is created.

 

[xlbo]

Yes - you can either do it in the MS Query GUI or you can amend the SQL in VBA and pick up a cell value to insert into the SQL string

In MSQuery, Add a Criteria but instead of hardcoding, put
[Crit] in the criteria space

When you return the data to excel, you should be able to right click on the data range and choose "Parameters" - from there you can fill yer boots

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[MJPPaba]

Hi,

In principle it is quite easy, If you record a macro and go through the process of importing a sample of the data you want to a worksheet ensuring you put in a criteria you can then go into the code and amend it to allow it to become more dynamic, e.g.

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=MS Access Database;DBQ=\\path\Database.mdb;DefaultDir=\\path;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;", Destination:=Range("A1"))
.CommandText = Array("SELECT * FROM TableName
WHERE FieldName='" & Sheets(1).Range("A1").Value & "'")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

see the line with the Select statement in the Where clause it shows a reference to a cell on Sheet one. This cell can contain any value entered or chosen from a drop down combobox.

Any Probs Drop you code here..

Cheese Matt

 

[xlbo]

No real need for code - as per my post - unless you want more of the SQL to be variable. If it is just in a WHERE condition, there is no code needed

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[rewob65]

I am using MSQuery in Excel and want a criteria where the field is less than current date. i cannot find the sql criteria CurrentDate does not work

Thanks

 

[rewob65]

sorry about my misspost