Taking the Excel 2007 Plunge 4

[SkipVought]

Well.... almost. I have 2007 on my personal PC and laptops and my work PC. However, I am still running 2003 on my work laptop, to make a full disclosure.

I have discovered that 2007 Data > Get External Data > From other sources > From Microsoft Query... creates a ListObject Object of which the QueryTable Object is a property.

The Worksheet Object still has a QueryTables Collection, but I have not found a way to ADD a QueryTable from a menu, other than via the ListObject. Of course, I can add via code.

Is there a non-code method of adding a QueryTable to a worksheet?

Is there an advantage for using the ListObject method, available on the worksheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

I can't really complain about this change too much. The obvious benefit is that managing the data in a listObject keeps you from having to fool with formulas too much, and it also format the thing like a table, so I guess if you're providing a flat file for someone it is pretty? The downside is, of course, any existing code you have referencing querytables has to be changed, but at least it's a small change, right?

 

[kjv1611]

For Skip, it's probably a big change. No telling what code he has lurking behind his Excel Workbooks!

 

[SkipVought]

So far, my experience has been pretty smooth. I did run into a problem with an MS Query to another table (sheet) within my workbook. I had to remove the path and db name from the FROM clause in my SQL. Apparently, the PATH is only needed in the CONNECTION string in 2007. Good change!

Only other thing is doing the SaveAs with FileFormat property.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

In the very helpful link,

http://office.microsoft.com/en-us/products/FX102774021033.aspx,

there is a [blue]Search for Commands[/blue] download, that loads as an Addin when you start, Excel, Word, Powerpoint.

I just noticed that this search feature is a VBA Project (ScoutCommands.Xlam), and the project is not protected.

You can view the VBA code. It might be instructive.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

[purple]*[/purple]: Shiny!

 

[kjv1611]

Thanks Skip! Didn't even pay attention to the downloadable there..

I'll have to try it out at home, as I don't have local admin rights at work.

 

[MarkWalsh]

That's an awesome find, Skip! Wish I had known about it when I had to migrate to Office 2007. That sure would have saved me a lot of heartache. Almost makes the 'Ribbon' worthwhile in my eyes.


...almost...

 

[SkipVought]

I look at it as my "safty net!"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and thanks for the "shineys!" ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The Table feature in Excel 2007 has some really great aspects:

1. Enter or change a formula within the table and the formula propagates to all rows of the table.

2. Enter data in a new row, and ALL formulas are propagated to the new row.

3. The Header Row option enables you to scroll down and the Table Headings appear in the Cell Column IDs, replacing A, B, C etc.

4. Structured References make formulas more self documenting. Structured References are in some ways similar to Named Ranges.

5. Table aggregations can be turned on or off with the Total Row option.

6. Filter buttons are automatically added to the heading row.

7. You can Remove Duplicates (create a unique list/table) with a simple click.

8. If you don't like all these features, you can Convert to Range.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

That's all good stuff ===> *

Thanks Skip!

Cheers, Glenn.

Beauty is in the eye of the beerholder.