Cathi in Maryland points out one of the effects of a disturbing trend. The trend is that Microsoft is trying to jam online help down our throats. This particular manifestation is the lack of Whats This? (that little question mark at the top of dialog boxes) help on the Data Range Properties dialog.
Here’s 2002
Here’s 2003
To be fair, this could just be an oversight on the part of the Office Help developers. It seems strange, though, that features would disappear.
Here’s the information you’ll be missing:
Name: The named range created with the table. When you create an external data range, Excel automatically creates a named range that covers the table. You can type a new name here which changes the named range. The dialog shows spaces in the name, but the defined name box shows underscores in place of them. If you type spaces in the new name, they will be underscores in the range name.
Save Query Definition: The external data table is connected to the underlying data source. That allows you to refresh the data when the source changes. If you uncheck this box, you break the link between the Excel range and the source, meaning you can’t refresh the data anymore. The data doesn’t disappear and the named range stays too. When you uncheck the box, you get a warning that the link is permanently deleted. You can still close the workbook without saving changes, though.
Save Password: If the underlying data requires a password, you can avoid having to type it in every time by checking this box. If you want users to have to type in a password, uncheck this box. Data sources that don’t require a password are unaffected by this.
Enable Background Refresh: Refreshing in the background means that you can do other stuff while the query is refreshing, including continuing executing VBA code. The alternative is that user interface is locked up and VBA code execution is suspended until the refresh is done.
Refresh Every: You can schedule the query to refresh automatically every so many minutes, from 1 to 32, 767 (that’s about every 23 days).
Refresh Data on File Open: She does what she says. If you are so bold as to check this box, you will be pestered with a message about enabling or disabling the automatic refresh when you open the file. Blech.
Remove External Data…Before Saving: You can reduce your file size by removing the data before you save. This clears the data, but retains the query definition so it can be refreshed. Or does it? If you save the workbook without closing the data doesn’t disappear. Nor does it delete the data and refresh it. So actually, it deletes the data on close in the state it was in when you last saved it. At least I think it does. The first time I’ve ever used this feature was about 5 minutes ago.
Include Field Names: If you check this, the first row of your table will be field names. If you don’t, the first row of data will, well, the first row of data.
Include Row Numbers: If you check this, the first column of your table will be sequential numbers, otherwise the first field in your query will the first column.
Adjust Column Width: Checked, this causes the column widths to adjust to the data on refresh. Unchecked, the column widths stay the same.
Preserve Column/Sort/Filter/Layout: If this is checked, then the order of the columns and rows of the Excel range stay the same no matter what you do to the query. For instance, if you add a field to the middle of the underlying query, it will appear at the end. If you want the Excel range to look like the query, uncheck this box.
Preserve Cell Formatting: If you apply formatting to a cell, it will stay formatted. No, not necessarily the data that you formatted, just the cell. That is, if you format cell C5 and the data changes such that a row above is removed, C5 remains formatting. You might expect the formatting to move to C4, but you’d be wrong.
If the number of rows…: See Data Range Properties Options
Fill Down Formulas…: If you put formulas in the column adjacent to the table, the will expand and retract as the range does. You aren’t limited to one column, you get as many as you like (I think) as long as they’re adjacent.
You know I had not noticed this until today – strange indeed – maybe MS are withdrawing support for MSQuery by stealth, as they certainly don’t seem to want to develop it any further.
I’m going to have to agree that MS is trying to shove online help down our throats.
Infact I think they want to shove online EVERYTHING down our throats. I seem to remember back in the day that they had plans to put Office 100% on the Internet and charge per document or per hour or something similiar. Thankfully their plans to move everything on to the Internet is moving slower than they would like.
You can get rid of this annoying box: “Refresh Data on File Open: She does what she says. If you are so bold as to check this box, you will be pestered with a message about enabling or disabling the automatic refresh when you open the file. Blech.”
by adding a line in your registry. Which isn’t ideal… Here’s the link to the Microsoft article on how. http://support.microsoft.com/kb/248204/en-us
On a side bar, I started using this feature last Thursday. Where is a good resource to find information or “best practices” for using external databases (e.g. Access) in Excel?
May be a bit off topic. But another issue with 2003 upgrade is that I was penalized for the upgrade in that with Office 2000 Pro I had FrontPage. With 2003 Pro FrontPage is gone. Now that is a take over move.
So, I just downloaded a copy of Evrsofts’ 1st Page 2000. :o)
doco
First Page 2000 is a pretty good program. Personally I would use notepad.exe before I ever paid for software to edit HTML. Although I hear Dreamweaver is an amazing piece of software.
It seems that so many companies other than MS are moving more and more to online help. It certainly reduces the cost of having to kill all of those trees and ship all of the weight associated with a paper manual twice as thick as “War and Peace.” Being a bean counter myself, I can see the advantages to the cost savings, but in every aspect of everything I do, it just seems it would be faster to pick up a book and look it up.
One of my favorites is QuickBooks (I think). They give you a book of really basic stuff then refer you to online help for more info.
Stupid corporate suits. :-)
Dick,
Does the NDA allow You to comment on this subject from what You’ve seen in Excel 2006?
Kind regards,
Dennis
Michael –
I just bought QuickBooks, because my accountant doesn’t want to deal with Excel files. Probably a good idea, since Excel allows each of his clients to set up a unique layout.
Anyway, they provide a pamphlet, a terse reference guide (~100 pages), and a coupon to buy the Official Guide on line. I suppose if I want to know how to display my numbers in a particular format as easily as I can in Excel, I’ll have to pony up the additional cost of this book, in addition to the cost of the software itself.
I have a few comments about MS Help and this thread.
First, I think some people are confusing online help with… well, online help. Dick is referring to internet-based online help, I believe, whereas I think some of you are referring to any electronic help file.
Second, I’ve been more and more disappointed with MS Help with each ‘upgrade’ lately. I miss having an Index in the help section. Sometimes you’re not really sure what you’re looking for (or the term that MS uses for an action or object), so browsing through the index can save time.
I personally preferred the help files/methods of a few years ago to the way things are done now.
Fill down DOES NOT WORK. I have a big data dump in one of my sheets and I have column A serve as an index for it. Based off of conditions in that dump, I concatenate a reference number that is then referred to from another sheet. Every few days I have to go back in and copy down the formulas. The data range keeps growing and growing but the formulas are left right where they were.
That’s odd Gregg because it works for me. Make sure you don’t have any other columns between your external data and the formulas – check for hidden columns. Also, if you recreate the external data table, say through VBA, instead of just refreshing it, this wouldn’t work. If you want to provide more information about the formula and the steps you are taking, we may be able to troubleshoot this.
Is it possible to export an access table to excel and whenever I add something in access table. The excel exported table should be updated automatically.
I’m also interested in having an external data range refresh on open WITHOUT the annoying pop up. The MS suggested registry edit does me no good because I cannot change the baseline in my enterprise.
Would it be possible to use VB in the Workbook_Open() function to tell the external data range to refresh itself? If so I could get the effect of having the Refresh Data on File Open box checked without the ugliness.
Hello Guys,
I have an Excel with multiple worksheet. Some of the worksheets are populated using External Data thru MS query. and Other worksheets are attached and summarized with these external data worksheet.
=========
I populate a worksheet(COL A thru COL O) and then creates 4 additional columns (say COL P, COL Q, COL R AND COL T. COL P is the concatenation of COLC+ YEAR/MONTH FROM COLB, COL Q concatenation of (COL A,COL C), COL R is the SUM of Col N+ COL O and Col T IS THE sum of COL D.
When I entered concat formula for COL Q in cell Q2 – no problem.
When I entered concat formula for COL R in cell R2 – no problem.
and now I clicked REFRESH/REFRESH ALL BUTTON, everything works fine. Point to be noted here is Still I didn’t entered formula for COL P. I save the file and got out. No issues
I open the file again no problem, everything looks ok and in place. No I entered concat formula for COL P in cell P2 , and save, No problem
My problem at this stage of game
=======================
When I click the REFRESH/REFRESH ALL button again, all the values gets replaced in the COL P2, Q2 and R2 with LABEL FOR COL P, COL Q AND COL R. This happens only when MQ-Query returns 1 row only.
There is no macro or VBA code running behind the scene. I am not able to figure out how the P2, Q2, R2 cell gets populated with the col LABELs. I have trying to figure out this but so far no success..
I have used “Override existing cells with new data and fill down formulas …” option
Please let me know if everything is clear here and any help will be greatly appreciated!
sba