I finally managed to get some time on this (I'm moving across to a Cognos TM1 developer role) and get this working using ADO parameters...
Took some inspiration from the following thread..
http://www.access-programmers.co.uk/forums/showthread.php?t=219149
Seems to work quite well.. although...
I'm quite confused by this..
I am testing if a sql recordset contains records.. normal stuff..
The WHERE statement is based on input parameters and will insert either 'LIKE' or 'NOT LIKE' condition on the 'Role' field...
When copying the built SQL into a query and running, both return a...
Use an array...
dim varFolder(2) as string
varFolder(0) = "C:\Users\User\Documents\ invoice"
varFolder(1) = "C:\Users\User\Documents\cofc"
varFolder(2) = "C:\Users\User\Documents\despatch
So i could never get this working without closing the bound form frmProfiles first..
Cheers VBAInet for your help.. I can't upload a copy here as it will be flagged as a security breach.
I tested the code with Office 2003 (don't have 2010 here)...
As long as Excel Object Library is added to the list of references, code works fine here.
Suggest maybe an install problem.. try another machine..