Replies

Many Mahalos Stuart,

You are righteous and kind, and your facial hair grows very quickly. I use macros several times daily in Excel to do the mundane and the obscure, but I’m totally unfamiliar with Word’s object model and usually waste frustrating time when I try to find the correct syntax to get at a particular property. But your macro is a good reminder — there’s only one way to learn. I’ll experiment with your suggested code.

JohnJ

Hi folks,

Too late for a solution, but not too late for the example SammyB’s asking for: see the post #47726 dated 21-Jun-01 12:56 and thread Re: clustered stacked chart (sorry, I don’t know how to put a link or shortcut into this message).

See my attached file at the end, with an example of exactly what you were trying to create, Bernie. It is also a bit convoluted to create, you have to plot a calculated sum of stack 1 and stack 2 and set it on the primary axis as stack2, and set the series for stack1 on the secondary axis. Let me know if you would like the file again.

Aloha,

JohnJ

I agree, that itty-bitty view makes parsing large data files a chore. Even if you resign yourself to the tedium of scrolling through the whole file, it’s easy to scroll too fast and miss the one or two oddities or the chunk 2/3rds down that can throw off your parsing.

An option worth considering: try opening the data file first in a basic text editor (that can handle the file size, unlike Notepad). Set the font for the entire file to a monospace font like Courier or Courier New and make sure wrapping is turned off if those are options.

That’s one of the things I love FoxPro for, an unexpected surprise in a database development platform: it has a killer text editor. I can open up a 200,000-record file, scroll through it without any hesitation to determine where to place parse breaks, carve it into Excel-manageable chunks, and after parsing the first chunk, simply Edit | Repeat the process.

Thanks for tolerating my babble,

JohnJ

…or right-clicking on the toolbar, choosing Customize… to bring up the Customize dialog box and re-setting the toolbar in question. A good reminder, too that Excel often has more than one way of performing the same command. Ctrl-Z, as well Edit | Undo [action], would still work if the toolbar button were disabled.

Did some mischievious imp put a fake Undo button on the toolbar? I wonder because my Excel won’t let me change the function associated with the real Undo button. Hm.

JohnJ

One way is to add series and apply another chart format to just those series.

In the attached example, I started with a side-by-side column chart. I added a ‘low’ and a ‘high’ series with constant values, and formatted them as Area (stacked, the second Area sub-type). To do this, you simply select the series on the chart, choose Chart | Chart Type… from the menu and select Area. You have to do this one at a time for each series.

Excel will combine all series that are formatted similary, such as stacked-area and side-by-side column. To achieve the appearance of a ‘floating’ range from 20 to 30, I formatted the ‘low’ series to have no area or border, so all that appears is the ‘high’ series (really the difference between your low and high ends of the portion you want shaded, stacked on top of the now invisible ‘low’).

Excel charts have a great deal of flexibility, but it can sometimes take a good deal of manual tweaking and some creativity to achieve the desired effect. You’ll notice I extended the range of the two Area series one row before and one row after the other charted data. I guess in Area charts, the X-axis has to stop and end on a category. Until I added those two rows, the column set of series were half cut off at each end of the chart. I also modified the legend by deleting the legend items for ‘low’ and ‘high’ (select the legend, pause a moment, then select the specific legend item, and hit Delete) and formatted the border of the visible Area series to match that of the chart gridlines.

Anywho, hope this helps, and happy charting!

Aloha,

John Jacobson

Thanks Andrew,

Your code opens the browser just fine, but my problem really begins there. Initially, I want to input a value in the web page’s single edit field. Ultimately, I want to maintain control of the browser and continue to send commands, via SendKeys method or some other, to achieve what I want — a trouble-free, simplified access to this paging utility from my Excel phone directory for even the most challenged Joe User.

I tried adding the SendKeys method within LaunchWebPage() but nothing happened. Any other suggestions? How to I keep from losing focus?

mahalo for your feedback,

JohnJ

If you knew that your imported dates were all after 1929, you could rely on Excel’s arbitrary choice of century. Any two-digit year from 30 to 99, Excel assumes 1900. For years 00 to 29, Excel picks 2000.

John Jacobson

Thanks AJuk,

but “Internet Explorer” does not work as an appname for either the Shell() function (to launch IE) or the AppActivate() statement (with IE running already), nor does any variation I’ve tried. If I can find the right name, I think the SendKeys statement would work to get me past the first page where the pager number is input, maybe farther. Where does one find this appname?

And Thanks Adam,

I don’t have access to the code behind the web site paging. Via email I’ve asked the subject website’s support for help, nothing yet.

I like your suggestion about creating and manipulating an Internet Explorer object! Unfortunately for me, I’m horribly uneducated/inexperienced with that. If I could find a good example or two of code referencing the right library and creating an MSIE object, I could muddle my way through it I think. But I don’t know where to start.

Mahalo for everyone’s continued help,

John Jacobson

Thanks Guy,

That’s the one. Thanks also, Deb, for the extra goodies.

JohnJ

Hi BookLady,

Don’t want you hanging in space on this one, though my input is not hopeful.

Unless you have the file backed up somewhere you haven’t uncovered yet, my confidence level is low. I’ve never had success tracking down temp Excel files nor restoring unintentionally deleted or saved-over Excel files.

I hope someone else has a happier message.

Condolences,

JohnJ

BOOK.XLT and SHEET.XLT only exist if they are created by a user. In their absence, Excel uses its built-in program settings. See “templates, customizing workbook defaults” in the Excel helpfile.

Set your chart series to extend 1 row beyond the last row of data. As long as the new data row is inserted and not pasted on top of that blank row, your chart series will automatically expand to include the inserted row.

You’ll have to tolerate one empty column at the right of your charts, which shouldn’t bother anyone. Or you can change all charts to line charts, and have some more options about shifting axes. This method doesn’t work well with area charts, which zoom down to zero on any blank point in their series.

Aloha,

JohnJ

So Andrew has it corrrect then? Sounds like it by your original question.

You can contrive the appearance a multiple-cluster stacked chart, but it’s a bit convoluted. Example is attached. The first stack is plotted on the secondary y-axis. The second stack is actually the sum of the two stacks that would be plotted in a typical stacked chart, and is plotted on the primary axis to get it to appear behind the first stack. This way it appears stacked, but it’s really just two co-located sets of series on different axes.

You may have to set each y-axis to the same maximum scale. The chart could also be cleaned up a little. Legend items can be manually re-ordered or deleted, and the secondary y-axis can be deleted or formatted to be invisible (white font and line color).

This method works with only two stacks.

John Jacobson

Another possibility that has impacted me in the past is that your small GIFs can bloat a bit if you use Excel’s default Paste, which can sometimes mean pasting an embedded OLE object. I’ve had the best luck by choosing Paste Special… and selecting Picture.

A more likely culprit, however, is that worksheets copied from other files are carrying some unwanted baggage. I don’t even know all the nooks and crannies to search here, but look for unnecessary custom formats, defined names, pivot tables carrying data, formatting (such as borders or shading) that carries on to the end of rows or columns… As a test, you could copy and save each sheet as a separate file, and check file sizes to see if any one sheet stands out.

HTH

John

mahalo, all. Others experiencing the same problem measn I don’t have to rebuild my personal macros file … yet.

John