Microsoft Query won't give me access to data in a Microsoft Excel list.
Close the workbook Before you try to access the data, make sure the workbook is not open in Microsoft Excel.
Check the worksheet Check the following on your Microsoft Excel worksheet:
- Check for list format Make sure the list follows Microsoft Excel list format. (For information about guidelines for creating a list on a worksheet, see Microsoft Excel Help.) Name the ranges that are tables, and make sure you give each range a different name.
- Name the list Microsoft Query recognizes only named ranges as tables. To name a list, select the entire list in Microsoft Excel, and then type a name in the Name box on the formula bar.
- Don't access the list as a system table Do not attempt to gain access to Microsoft Excel tables by clicking the System tables option under Show in the Table Options dialog box. If you use this approach, Microsoft Query may recognize blank cells on your worksheets as data, may not recognize some of your actual data, and may not be able to retrieve your data correctly.
- Format lists to combine data the same way Use the same format for columns in each list that you want to combine. For example, if each list has a date column, apply the same date format to each column. If you mix text and number formats within a column, the Microsoft Excel driver may not be able to find and combine all of your data.
- Don't use column labels such as Table or Database Microsoft Query uses some labels internally and does not recognize them in your data.
- Move all workbooks to the same folder A data source that you set up for the Microsoft Excel driver can provide access to workbooks in only one folder at a time. If the lists are in two different workbooks, make sure both workbooks are in the same folder as the workbook that you specified when you set up the data source.
- Move workbooks to the folder expected by the data source Before you start Microsoft Query, make sure you copy all the workbooks you want to use to the folder specified in your data source. Microsoft Query does not automatically recognize new workbooks that you add to the folder while you are creating a query. You can gain access to new workbooks by clicking Options in the Add Tables dialog box and then clicking Refresh.
Use Excel to combine data Microsoft Query is useful when you want to combine data from two or more Microsoft Excel lists. If you're working with data from a single worksheet or you want to use simple criteria to select rows from a single worksheet, you may find the Microsoft Excel AutoFilter and Advanced Filter commands (Data menu, Filter submenu) easier to use. For more information about AutoFilter and Advanced Filter, see Microsoft Excel Help.