Dear All,
Please can you help with another issue which is testing my sanity!
My excel spreadsheet contains the following code which uses ADO to query a closed workbook - until I add the last line, all works perfectly:-
sSQL = sSQL + " select T1.* from "
sSQL = sSQL + " ( "
sSQL = sSQL + " select iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]) as ""FRANGY"",' ' as ""DIM1"",sum(FRA.YTD_CURR) as ""VALUE"" from "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.FRANGO_MAIN FRA, "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.VALIDATE_LOOKUP_FRANGO_SECOND VAL, "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.VALIDATE_LOOKUP_FRANGO_THIRD VAL1 "
sSQL = sSQL + " where FRA.[Element 2] = VAL.Second_Element and FRA.[Element 3] = VAL1.Third_Element "
sSQL = sSQL + " and right(iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]),3) <> '999' "
sSQL = sSQL + " group by iif(VAL.FRANGO='999',VAL1.[FRANGO CODE],VAL.FRANGO & VAL1.[FRANGO CODE]),' ' "
sSQL = sSQL + " union all "
sSQL = sSQL + " select frango as ""FRANGY"",DIM1,round(YTD_CURR*1000,0) as ""VALUE"" from "
sSQL = sSQL + " `c:\downloads\01-FRANGO-BACKUP.xls`.FRANGO_OTHER "
sSQL = sSQL + " ) T1 "
'Then add this line
sSQL = sSQL + " where T1.FRANGY= '107100' "
Debug.Print sSQL
Display More
However, when I add the last line for the "where" clause it bombs out with an "automation error" - I have fiddled and fiddled with it without joy all day (on my day off!) and am just lost for what to do next.
When I cut the debug.print sSQL output into a Querytable on the same sheet it works precisely as expected.
For some reason, ADO (i guess) doesn't like the build up of the sSQL string that way although it doesn't choke on any of the other "where" clauses.
Please someone tell me the errors of my ways!
I look forward to your replies.
Regards,
Pete