About a week ago I was preparing to answer a question about External Data on the newsgroups. I felt that I knew the answer, but I like to test it out before making a fool of myself. I opened a workbook that had an External Data table in it to find that the Edit Query toolbar button was disabled. I did a little searching on Google and found some people with similar questions, but no real answers.
I should be able to solve this, so I set to work. I opened a few more workbooks with the same result. I created a new query table in a new workbook, no dice. I tried different DSN’s, different databases, different workbooks, all showed the Edit Query button disabled.
I don’t need to edit my queries very often, so this wasn’t a pressing matter (the newsgroup poster might disagree). So I put the problem on the back burner, knowing that I was missing something really stupid and that it would come to me. Nothing came.
I asked some fellow MVPs and the answer came back in about 5 seconds: The toolbar button was accidentally disabled and you just need to re-enable it. I went to the Immediate Window and typed
Application.CommandBars(“External Data”).Controls(1).Enabled = True
I could have also just Reset the CommandBar with the same result. How that toolbar button became disabled in the first place is a mystery. I do a lot of experimenting in Excel, but I think I would remember messing around with that toolbar. It works now, and I’m happy about that. I’m not happy that I can’t remember all the obvious things to try before I ask for help. Now that I’ve written this post, it should stick in my memory.