Get External Data grayed out

[Guest]

My worksheet is linked to an external data source using ODBC drivers. I can't
edit the query anymore because the Get External Data buttons are grayed out.
The Edit Links button is grayed out also.

 

[Gord Dibben]

Sounds like the sheet is protected.

Have you checked under Tools>Protection to see the status?


Gord Dibben MS Excel MVP

 

[Guest]

I have Excel 2007, and the workbook/worksheet is not protected. Under Excel
2003 that function was available. However, since upgrading to Excel 2007,
Get External Data is greyed-out. I cannot access any of the functions. How
do I have the Get External Data to be available?

 

[Roger Govier]

Hi

Are you trying to do it from a cell within a Table?
If I try from within a Table, then it is Greyed out for me.
If I try from a cell outside of a Table, then the options are available.
If within the table, you can use Table Tools>Design>Tools>Convert to
Range which will turn it back to a List, then the options become
available.
To go back to a Table after, Insert>Table.

 

[Gord Dibben]

Roger

Just curious............

In 2007 is Insert Table the same as Data>Create List in 2003?


Gord Dibben MS Excel MVP

 

[Guest]

Hi Roger,

It is not a table. It is data retreived from an ODBC source.

When I click outside of the data (an empty cell), the Get External Data
options are available.

Even though this is not a table, do I still have to click outside of the
data returned from my query to have access to the Get External Data options
to adjust my query?

Kip

 

[Guest]

Hi Roger,

I was wrong. It IS a table. In Excel 2003, even though you were on a cell
from the returned data, you can still access the query. Apparently it is
different with Excel 2007.

Kip

 

[Roger Govier]

Hi Gord

Yes, it is.
However, I had never really investigated the "List" properly in 2003.
But the "Table" in 2007 is an Object with it's own properties and the
way it behaves when trying to access the Autofilter dropdowns
(automatically created) is slightly different from accessing and
manipulating the autofilter in a standard "list" as Xl2007 would now
refer to a straightforward set of columns and rows. This is the same in
both 2003 and 2007

One you choose, Insert Tab>Table, having started from any cell within
your list, it defines the used range for you, asks whether there are
Headers, adds filters exactly the same as with Excel 2003. The table
automatically "grows" as you add more data the same as 2003 so it saves
creating dynamic ranges as an input to Pivot Tables for example.

The Object has a number of additional features over the 2003 model
however, including the neat facility of auto freeze panes on the header
when you scroll down the sheet, but, the column labels A,B,C etc.
disappear, with the actual header labels fixed.

Getting External data is the same in both versions, from within the
"Table", those options are greyed out.

 

[Roger Govier]

Hi Kip

If I use Data>List.Create List in XL 2003 it does create what is now
termed a Table in XL2007.
If I am on a cell within the "Table", Get external Data options are
greyed out for me.

As I said earlier, if you choose the option to convert back from table
to List in XL2007, you will find that those options are available to you
once more.

 

[Gord Dibben]

Thanks Roger.

I do notice that the "get external data" is grayed out on the 2003 "list" area.


Gord

 

[AJH]

I have suffered the same greying out. (sorry -English)
The way I corrected it was to right click on one of the greyed-out cells. select "data range properties" and uncheck the save query definition box.
do this for each set of data imported.