Get External Data grayed out


G

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.
 
Ad

Advertisements

G

Gord Dibben

Sounds like the sheet is protected.

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


Gord Dibben MS Excel MVP
 
G

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?
 
R

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.
 
G

Gord Dibben

Roger

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

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


Gord Dibben MS Excel MVP
 
G

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
 
Ad

Advertisements

G

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
 
R

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.
 
R

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.
 
G

Gord Dibben

Thanks Roger.

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


Gord
 
Ad

Advertisements

AJH

Joined
May 15, 2018
Messages
1
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top