Daily Dose of Excel

Ginger comments:

The only thing I can’t do is to get XL to open the existing IE window so I can ’sendkeys’ to do that part. I can only see similar posts for “create.object” related to IE, and nothing for “get.object” (or whatever will work).

According to XtremeVBTalk, GetObject doesn't work with IE and you have to use Shell. You need to set a reference to Microsoft Internet Controls and Microsoft Shell Controls and Automation.

Then this sub will give one of the IE instances the focus. In this example, it's the IE instance whose title contains the phrase "Daily Dose", but you'll have to change to suit your situation.

Of course I almost never advocate the use of SendKeys. There is almost certainly a better way to get that html into Excel than copying and pasting, but it's probably beyond the scope of a blog post.

Also, there's some question in my mind about whether I can declare objIndex as InternetExplorer. From ExtremeVBTalk, it seems like both Windows Explorer and Internet Explorer windows are in the Shell.Windows collecton. If that's the case, declaring objIndex as Object would be more prudent (similar to looping through sheets in a workbook - you don't know if you'll get worksheets, chartsheets, or something else). At msdn, it says that Shell.Windows returns a ShellWindows collection object, which is a collection of InternetExplorer windows, and doesn't mention Window Explorer windows. I tried the code as written with multiple IE and Windows open and it didn't fail. It did loop through both types of windows, but it happily coerced Windows Explorer windows into an InternetExplorer object, I guess.

How do you communicate a pivot table to someone? I have a huge table of payroll transactions and I created a pivot table for my worker's compensation premium audit. Someone else who has Quickbooks could use this pivot table for their own purposes, but I don't know how to tell them how to build it. I have two basic problems: There are a lot of seemingly irrelevant options when creating a pivot table and when I create a pivot table there's a lot of trial and error.

If I want to tell someone to create a table in an Access database, for example, I could send them a CREATE TABLE sql statement, like this one from w3schools:

CREATE TABLE Person
(
LastName varchar(30),
FirstName varchar,
Address varchar,
Age int(3)
)

It doesn't tell them the steps to create the table in the Access UI, but it gives them all the information they need to create the table (not to mention they could just execute the sql).

Do we need SPL (Structured PivotTable Language)? Does anyone else have trouble explaining the properties of a pivot table or is it just me?

Here's what I get when I record a macro to create the aforementioned PT:

Complete but not concise. Maybe I just list out the relevant properties:

PivotTable
  Source: Data!R1C1:R16497C81
  RowField "Source Name"
    Position: 2
  RowField "State Worked In"
    Position: 1
    RowTotals: Off
  ColumnField "Payroll Item"
    AllItems
      Visible: False
    Item "Federal Unemployment"
      Visible: True
  DataField "Income Subject To Tax"
    Aggregator: Sum
  RowGrand: False

Can you create my exact pivot table with just that?

There's an interesting discussion at Smurf about the macro recorder. I rarely use it, but I used to use it quite often. I actually just used it last week for something to do with FormatConditions, but I don't remember the details. Intellisense or the Object Browser usually gets me where I want to go.

The number one reason people say they use the recorder is get object names and syntax. It's great for that, no doubt. But is that the best delivery mechanism for syntax? I would think good documentation would be more useful and efficient than the macro recorder.

There is one aspect to the macro recorder that you'll never get from the best documentation: A direct mapping from the UI to code. If you don't know about the FormatConditions object, then you may not have any luck determining how to manipulate conditional formatting in code. If you know how to do it in the UI, however, the recorder will at least give you a clue.

If VBA had perfect documentation (complete, good/relevant examples, context) would that change how much you use the macro recorder?

Hi all,

For a project I needed a quick way to display the content of an array to the user. I didn't want to use a worksheet, but opted for a userform. The data I wanted to show was contained in an array. The array contained something like:

So I figured I'd put a listbox on a userform and make sure the column widths of the listbox resize with the data I want shown.
I wanted it to look like this:

That proved far from easy...

Read on...

Enjoy!

Regards,

Jan Karel Pieterse
www.jkp-ads.com

I previously mentioned that VBA is dying. At least one of my arguments was that MS couldn't be bothered to port it to the new MAC architecture. Now c-net says:

Finally, the company announced that, for the next full release of Office for Mac, it is bringing back support for Visual Basic scripts. That feature was in Office 2004 but was pulled in the current version as Microsoft worked to add support for Intel processors and new file formats.

That changes things, I think.

If you can't find it on the Ribbon, it's on the Insert tab. Don't ask me why. You're welcome.