Excel Macros Revisited

Is it possible to change a recorded Excel macro which contains an Access query to be dynamic?

That is, if I have set the query to only display fields with the autonumber value '3' could I replace this '3' with a variable to let me access different records using the same code?

I would appreciate any help because I really have to get this working soon. Alternative approaches also very welcome.

I believe it would be possible, but you will need to provide some code examples for someone to even speculate what you might do. What does the macro look like now?

I think therefore I am... sometimes right. images/icons/wink.gif

Thanks for the offer of help...

I need to access the field "Project Ref" in "Milestones Stuff" table. Having used Excel to record the query you can see that the value of '3' for Project Ref has been hardcoded from the query wizard. However, I need to be able to switch '3' for a variable which I can use to access whichever project I need.

I have tried replacing it with a locally declared variable but this results in no data being returned from the query.

The code is in the attached file...any help appreciated.

You can definitely replace the 3s with values from local variables. It is just a matter of getting the concatenation correct. The [Project Ref] field appears to be numeric, however, you are building a string SQL statement. If your local variable was called intValue then the statement could be something like
<pre>
.CommandText = Array( _
"SELECT `Milestones Stuff`.`Project Ref`, `Milestones Stuff`.Milestone, `Milestones Stuff`.`Week 1`, `Milestones Stuff`.`Week 2`, `Milestones Stuff`.`Week 3`, `Milestones Stuff`.`Week 4`, `Milestones S" _
, _
"tuff`.`Week 5`" & Chr(13) & "" & Chr(10) & "FROM `Milestones Stuff` `Milestones Stuff`" & Chr(13) & "" & Chr(10) & "WHERE (`Milestones Stuff`.Milestone='Acceptance Test Complete') AND (`Milestones Stuff`.`Project Ref`= " & CStr(intValue) & " ) OR (`Milestones Stuff`.Milestone='I" _
, _
"ntegration Test Complete') AND (`Milestones Stuff`.`Project Ref`= " & CStr(intValue) & " ) OR (`Milestones Stuff`.Milestone='Integration Test Start') AND (`Milestones Stuff`.`Project Ref`= " & CStr(intValue) & " ) OR (`Milestones Stuff`.Milestone" _
, _
"='Project Plan Signed Off') AND (`Milestones Stuff`.`Project Ref`= " & CStr(intValue) & " )" & Chr(13) & "" & Chr(10) & "ORDER BY `Milestones Stuff`.`Project Ref`" _
)
</pre>
If something like this doesn't work then it is time to reformat the SQL.
I guess the SQL was generated by the record macro. It is too complicated.

I think therefore I am... sometimes right. images/icons/wink.gif

It worked.

I had a feeling that it might have been something to do with the variable type.

Thanks for the help.