MS Query of Excel Data

[excelhelp2]

I have a workbook that contains data in two worksheets that act as a data source for a MS Query on a third worksheet. The MS Query works fine, but when I go back to 'edit' the query, MS Query wants to open the workbook with the data source (which is already open), so I get an error message, and can't edit the query.

Yes, one answer is to have one workbook with the data sources, and a second workbook to do the query so that MS Query can access the separate data source.

But is there a way to do it all in one workbook by letting MS Query know that the data is in the current workbook which is already open ?

 

[Fazza]

I am unclear why the situation in the first paragraph occurs. It doesn't sound right.

Maybe something is corrupted? So, re-create should overcome.

However it should be fixable from your current file. There are two main things to get right with the query - the connection and the SQL. The connection property should refer to the data file & the SQL then need not. (Though in fact the connection can refer to any valid file & the SQL can explicitly refer to a different file...) Anyway, be sure the connection & SQL (aka CommandText) are correct. How you do that will be version dependent. I use Excel 2003 & find it easy to change - for simple instances - in the VBA immediate window. For really simple, the MS Query interface should suffice. Within VBA's immediate window debug.print activesheet.querytables(1).SQL and as it is read/write, you can edit it then re-assign it with activesheet.querytables(1).SQL = "amended SQL". Same for connection.

cheers