Excel 2008 query tables not remembering their SQL-SOLVED
Excel 2008 query tables not remembering their SQL-SOLVED
- Subject: Excel 2008 query tables not remembering their SQL-SOLVED
- From: Cameron Knowlton <email@hidden>
- Date: Mon, 19 Sep 2011 11:34:19 -0700
Title: Excel 2008 query tables not remembering their
SQL-SOLVED
I just went through a Microhell morning, thought I'd share the
workaround with everyone. This may be of interest to anyone who is
using Microsoft Excel 2008 External Data Queries (Applescript
isn't part of the problem, but it's the only solution I know).
I have a highly detailed spreadsheet with about a hundred data
queries, some of them (as in this case) spanning only a single cell.
Those queries have basic SQL commands in them to extract data from a
database.
I recently changed my database structure for a few tables, so I'm
cleaning up old spreadsheets with old code embedded. This is where the
problem lies.
I have one single query among them all that, regardless of the
number of times that I change the code, it doesn't stick. I can change
and refresh the data query successfully as many times as I want.
BUT... As soon as I close the document and reopen it, it's reverted to
it's previous code.
Frustrating doesn't begin to cover it.
I finally solved my Excel problem (as most can be solved) with
Applescript. Rather than using Microsoft's dialogs to change the code,
I used this script:
set
sqlCode to
"select POSITION as \"position\" from SEO..COMPETITORS
C, ENGINES..WEFOLLOW W where C.project='abc' and C.siteID='abc' and
W.query='medicare' and W.querymode='influential' and
W.title=C.sitename"
tell application "Microsoft Excel"
set
currentDoc to active workbook
set
theSheet to sheet "Aggregate Dashboard Report" of
currentDoc
set
theQuery to query table "socialTwitterInfluential_1" of theSheet
set
sql of
theQuery to
sqlCode
end tell
Works like a charm.
FYI, the majority of Excel's problems lie not with the backend of
the application itself, but in the interface. When (not if) Excel
refuses to do something (or chooses to wipe out formatting), use
Applescript to do the job instead of Excel dialogs.
I really hope this helps someone avoid the anguish that
Microsoft has unleased on me.
Cheers everyone.
Cameron Knowlton
--
Cameron Knowlton
KnowledgeVine SEO Dashboards
email@hidden
P: 250.382.0226
http://www.knowledgevine.net
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden