-
Title
Gets 'No user tables could be found in <filename>' message after connecting to Excel -
Description
When I connect to an Excel file, I get a message that says 'No user tables could be found in <filename>'. However, I do have a named region in the Excel file. Why is it not picking it up?
-
Cause
The named region on the Excel file is not absolute.
-
Resolution
Make sure that the named region on the Excel file is absolute. When designating the region for the named region, If both columns and rows do not have a $ in front of them, then Toad will not recognize it. Here is how you can check this.
In Excel versions prior to 2007...
1) Open up your spreadsheet and select Insert | Name | Define in the menu.
2) Highlight the entry for your named region and take a look at the ‘Refer to’ field at the bottom.
3) You should see something similar to “='SpreadsheetName'!$A1:$C107” in the field. The dollar signs next to the column letter means that those columns are fixed. As you can see, there is not a dollar sign next to the row number. So, you want to add the dollar signs so it looks like, “='SpreadsheetName'!$A$1:$C$107”. You may need to also change the row numbers, if there are not set to the range you want them to be. Once the dollars signs are added click OK.
In Excel 2007...
1) Open up the spreadsheet and select Formulas | Name Manager.
2) Highlight the entry for your named region and take a look at the ‘Refer to’ field at the bottom.
3) You should see something similar to “='SpreadsheetName'!$A1:$C107” in the field. The dollar signs next to the column letter means that those columns are fixed. As you can see, there is not a dollar sign next to the row number. So, you want to add the dollar signs so it looks like, “='SpreadsheetName'!$A$1:$C$107”. You may need to also change the row numbers, if there are not set to the range you want them to be. Once the dollars signs are added click OK.