This problem is made more intriguing by the fact that it works on the local machine but not on the Windows server where the excel workbook is deployed.
Fist of all I dump the contents of a disconnected ADO recordset (has no connection) onto an excel worksheet [Sheet1$] using the CopyFromRecordset method of the range object. At this point I also write out my Field Headings. The results are displayed as expected.
After this I preform an Application.Workbooks.RefreshAll
Now I create a connection to the excel workbook and try to open a new ADO recordset object using "SELECT * FROM [Sheet1$]" (simplified version of a complicated JOIN query - but this simple SQL does not work either). When I try and dump the results of this recordset onto a new worksheet using CopyFromRecordset method...all that is produced is one row with F1 in the upper left cell of the workbook. As if it cannot see the data that has just been produced on Sheet1 worksheet.
As I say it works every time on local machine but not on server. I have tried nearly all the refresh methods of excel, refresh.all, cells.calculate etc.
The only way I have found that works is by using the Save method. HOWEVER I do not want to use this method as it is a big hit on performance.
If anyone can offer some assistance I would be grateful. Thanks.