Month: June 2005

Get Outlook’s Currently Selected Time

Posted on by Dick Kusleika

I got an email from Dennis, a blind computer user (visually impaired as a bat, as he puts it). He uses a screen reading program that includes a scripting language. This language gives him access to the object models so he can get whatever information he needs.

What he needs to know is which day/time is selected when a user is looking at the calendar. That seems like it would be pretty easy, but you may be surprised. I went through every property and method of the ActiveExplorer object and could get there. Next I thought that I could create a new AppointmentItem and read where it defaulted the Start property. When I created a new AppointmentItem using

Application.CreateItem(olAppointmentItem)

It defaulted to the current time, not the selected time. Drat.

Next, I discovered that selecting Actions > New Appointment would create an appointment with the proper Start default. The only problem was that I could find that commandbarcontrol. I looped through every commandbar and every control and simply could not locate it. I know it’s there.

Finally, I went with the old standby: SendKeys. This code produced a message box with the currently selected time. I’m sure it’s fraught with danger, but it’s the best I could do.

Sub GetCurrentDay()

    Dim dtSelected As Date
    Dim i As Long
   
    If Application.ActiveExplorer.CurrentFolder.DefaultItemType = olAppointmentItem Then
        SendKeys “^+A”
        For i = 1 To 10000: DoEvents: Next i
        dtSelected = Application.ActiveInspector.CurrentItem.Start
        Application.ActiveInspector.Close olDiscard
        MsgBox Format(dtSelected, “mm/dd/yyyy hh:mm”)
    End If
   
End Sub

By the way, this is still an Excel blog, but this particular piece of code goes in Outlook.

Multi Column Match

Posted on by Rob van Gelder

Here is a little trick for matching two values against a two column table.
It’s much like creating a hash code for each row, then matching the hash code.

With this spreadsheet, the top section allows lookup, the lower section is the lookup table.

Here we are searching for a Value by looking up a City and Season:

The array formula in cell C3:
=MATCH(A3 & CHAR(1) & B3, A7:A16 & CHAR(1) & B7:B16, 0)
Enter the formula by pressing Ctrl+Shift+Enter.

The formula in cell D3:
=INDEX(C7:C16, C3)

We simply concatenate A3 and B3 to match against concatenated A7:A16 and B7:B16.

The CHAR(1) is to ensure uniqueness.
For example: “userdata” & “base” would also match “user” & “database”
Fixed by: “userdata” & CHAR(1) & “base” does not match “user” & CHAR(1) & “database”

Update:
Daniel M points out a different, more efficient, match lookup using boolean comparison:
The array formula in cell C3:
=MATCH(1, (A3=A7:A16) * (B3=B7:B16), 0)
Enter the formula by pressing Ctrl+Shift+Enter.

D3 remains the same.

This works because…
A3=A7:A16 results in an array of TRUE, FALSE values
Arithmetic on a boolean results in a number. eg. TRUE * FALSE = 0.
Only TRUE * TRUE will result in the “1? MATCH is looking for.

Thanks Daniel!

Java script to show “What’s new” on site

Posted on by jkpieterse

Hi all,

I’ve been looking for a way (technology unimportant ) to get a couple of headlines from my own (handmade) rss feed file onto my website’s homepage.

I stumbled on this site:

www.rss-to-javascript.com

and plugged it into my homepage.

Tell me what you think of this!

If anyone would know of JAVA script code that doesn’t use this external site, please let me know, I’d rather be in control myself.

Regards,

Jan Karel Pieterse

Textfiles – Part 1: Create Mdbs on the fly

Posted on by Anonymous

Itís common within large companies that end users are not allowed to access central databases and instead all data are received in large textfiles. Itís also usual that end users donít have MS Access available and that all users use the same Office-version.
Instead of import all data directly to a workbook a better option can be to create a Mdb (Microsoft Database) on the fly, import data from the text file(s) to it and aggregate the data before importing it to Excel.

References must be set to Microsoft ActiveX Data 2.5 Objects Library and Microsoft ADO Ext. 2.5 for DDL and Security.

Option Explicit
 
Const stPath As String = “c:DDE”
Const stDBase As String = “Source.mdb”
 
‘In order to create a mdb in Access 97 format add the line to the connection string:
‘”Jet OLEDB:Engine Type=4;”
‘The default is Access 2000 format (Type=5).
Const stCon As String = _
“Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Data Source=” & stPath & stDBase & “;”
 
‘The SQL-query to import data from the textfile.
Const stSQLAdd As String =  _
“INSERT INTO tblReportData “ & _
“SELECT * “ & _
“FROM [Text;DATABASE=” & stPath & “].[Data.txt];”
 
‘The SQL-query to import selected summarized data from the mdb-file.
Const stSQLSelect As String = _
“SELECT Dept, Quarter, SUM(Amount) “ & _
“FROM tblReportData “ & _
“GROUP BY Dept, Quarter;”
 
Sub Create_MDB_On_The_Fly()
Dim xCat As ADOX.Catalog, xTable As ADOX.Table, xCol As ADOX.Column
Dim cnt As ADODB.Connection, rst As ADODB.Recordset
Dim wsSheet As Worksheet
 
‘Delete the present mdb.
On Error Resume Next
Kill stPath & stDBase
On Error GoTo 0
 
‘Instantiate the objects.
Set wsSheet = ActiveSheet
Set xCat = New ADOX.Catalog
Set xTable = New ADOX.Table
 
‘Create the new mdb.
xCat.Create stCon
 
‘Add columns to the table and make sure that they accept null values.
With xTable
    ‘Name the table.
    .Name = “tblReportData”
    .Columns.Append “Dept”
    .Columns.Append “Quarter”
    .Columns.Append “Amount”, adInteger
    ‘Provide access to provider specific properties.
    .ParentCatalog = xCat
    For Each xCol In .Columns
        .Columns(xCol.Name).Properties(“Nullable”).Value = True
    Next xCol
End With
   
‘Add the table to the mdb.
xCat.Tables.Append xTable
 
‘You will be surprised when You see the outcome of the following.
Debug.Print xCat.ActiveConnection
 
‘Associate the variable to the already open connection.
Set cnt = xCat.ActiveConnection
 
With cnt
    ‘Import the data from the text file.
    .Execute (stSQLAdd)
    ‘Retrieve a recordset from the mdb.
    Set rst = .Execute(stSQLSelect)
End With
 
If Not rst.BOF Or rst.EOF Then
    Application.ScreenUpdating = False
    ‘Add fieldnames and dump the retrieved recordset into the active sheet.
    With wsSheet
        With .Range(“A1:C1”)
            .Value = VBA.Array(“Dept”, “Quarter”, “Total amount”)
            .Font.Bold = True
            .EntireColumn.AutoFit
        End With
        .Range(“A2”).CopyFromRecordset rst
    End With
    Application.ScreenUpdating = True
End If
 
Set rst = Nothing: Set cnt = Nothing
Set xCol = Nothing: Set xTable = Nothing: Set xCat = Nothing
End Sub

Although the emergency development sets focus on XML-files we still work with textfiles and Part II will be cover some issues when trying to access textfiles as ADO-datasources.

G’Day Excel Thoughts

Posted on by Dick Kusleika

When he’s not throwing more shrimp on the barbie (or deflecting stupid comments like that), Andrew is writing his Excel blog.

Excel Thoughts

He’s only got two posts up so far, but if you start reading now you can say “Oh, I’ve been reading Andrew Roberts from the beginning” when he’s famous.

Update: Andrew, I can’t find an RSS feed on your blog. If it’s there, tell me how to get to it and if it’s not, get one immediately. Okay, not immediately, but let me know when you have one so I can the feed to my daily Bloglines reads.