Add-ins in Excel 2007

20th November 2008, 06:42 pm by Dick Kusleika

Recently, someone asked me if there was a better way to manage add-ins in Excel 2007. Specifically, he wants something better than Office Menu - Excel Options - Addins - (wait) - Go. Here's a recent edition to my Personal.xls

Sub ShowAddins()
   
    Dim wb As Workbook
   
    'Dialog won't show if there's no workbook showing
    If ActiveWorkbook Is Nothing Then
        Set wb = Workbooks.Add
    End If
   
    'Show addin dialog
    Application.Dialogs(xlDialogAddinManager).Show
   
    'Close wb if it was created
    On Error Resume Next
        wb.Close False
       
End Sub

And here's how I added it to my QAT (Quick Access Toolbar). Click on the little down-arrow next to the QAT and choose More Commands...

Visit PTS Installing an Add-in in Excel 2007 for more on Add-ins.

Visit Ron de Bruin's How do I create a PERSONAL.xls(B)... page for more on Personal.xls.

Category: Excel12  |  6 Comments

XML and Excel

20th November 2008, 08:47 am by jkpieterse

Hi everyone,

I have had a Dutch article on XML and Excel on my site for quite some time now, but never got round to translating the thing into English.

Well, yesterday I found a bit of time during a long train-ride, so here it is:

XML And Excel

From the intro:

Microsoft Office 2003 Professional was the first Office version that took the XML standard seriously. The XML standard has been devised to ease the markup of data (especially on the web). A well known example of the use of XML are RSS feeds with which one can gather news from web pages. In reality, these so-called RSS-feeds are nothing less than XML files. This article introduces XML and shows some things that can be done with it (specifically in Excel).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

Category: General, MVP, External Data, Miscellany  |  4 Comments

Creating Classes from Access Tables

15th November 2008, 09:46 pm by Dick Kusleika

As I mentioned earlier, I want to develop a framework generating app to create some of the code necessary to read and write to and from an Access database. This is iteration 1 of about 100.

Start with this table in Access

Set a reference (Tools - References) to Microsoft ActiveX Data Objects 2.8 Library.

Then run this code. (It won't work for you without changing the connection string.)

Sub MakeClass()
   
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim adField As ADODB.Field
    Dim sql As String
    Dim sCon As String
    Dim sType As String, sPrefix As String
    Dim sDeclare As String
    Dim sCode As String
    Dim sVariableName As String
   
    Const sNL As String = vbNewLine & vbTab & vbNewLine
   
    'create strings for ado connection
    sCon = "DSN=MS Access Database;DBQ=C:\Documents and Settings\Dick\My Documents\Payroll.mdb;"
    sql = "SELECT * FROM tblEmployees;"
   
    Set cn = New ADODB.Connection
    cn.Open sCon
   
    Set rs = cn.Execute(sql)
   
    'sDeclare will hold the declarations secton
    'sCode will hold the Property statements
    sDeclare = "Option Explicit" & sNL
   
    For Each adField In rs.Fields
       
        ConvertADOType adField.Type, sType, sPrefix
        sVariableName = "m" & sPrefix & adField.Name
       
        sDeclare = sDeclare & "Private " & sVariableName & " As " & sType & vbNewLine
       
        sCode = sCode & "Public Property Let " & adField.Name & "(" & sPrefix & _
            adField.Name & " As " & sType & ")" & sNL
        sCode = sCode & vbTab & sVariableName & " = " & sPrefix & adField.Name & sNL
        sCode = sCode & "End Property" & sNL
       
        sCode = sCode & "Public Property Get " & adField.Name & "() As " & sType & sNL
        sCode = sCode & vbTab & adField.Name & " = " & sVariableName & sNL
        sCode = sCode & "End Property" & sNL
       
    Next adField
   
    'copy from immediate window and paste into class module
    Debug.Print sDeclare & vbTab & vbNewLine & sCode
   
    rs.Close
    Set rs = Nothing
   
    cn.Close
    Set cn = Nothing
   
End Sub

It uses this helper sub

Sub ConvertADOType(lType As Long, ByRef sType As String, ByRef sPrefix As String)
   
    If lType = 3 Then
        sType = "Long"
        sPrefix = "l"
    ElseIf lType = 202 Then
        sType = "String"
        sPrefix = "s"
    ElseIf lType = 135 Then
        sType = "Date"
        sPrefix = "dt"
    ElseIf lType = 5 Then
        sType = "Double"
        sPrefix = "d"
    Else
        sType = "Variant"
        sPrefix = "v"
    End If
   
End Sub

Obviously that's not going to cover all of the types, but it's a start. To be honest, I don't need this thing to do 100% of the work, just 98% of it. So if there's some touch up after the fact, I'm OK with that.

When I paste the resulting code in a class module, I get:

Option Explicit
   
Private mlEmployeeID As Long
Private msEmployeeFirstName As String
Private msEmployeeMiddle As String
Private msEmployeeLastName As String
Private mdtHireDate As Date
Private mdSalary As Double
   
Public Property Let EmployeeID(lEmployeeID As Long)
   
    mlEmployeeID = lEmployeeID
   
End Property
   
Public Property Get EmployeeID() As Long
   
    EmployeeID = mlEmployeeID
   
End Property
   
Public Property Let EmployeeFirstName(sEmployeeFirstName As String)
   
    msEmployeeFirstName = sEmployeeFirstName
   
End Property
   
Public Property Get EmployeeFirstName() As String
   
    EmployeeFirstName = msEmployeeFirstName
   
End Property
   
Public Property Let EmployeeMiddle(sEmployeeMiddle As String)
   
    msEmployeeMiddle = sEmployeeMiddle
   
End Property
   
Public Property Get EmployeeMiddle() As String
   
    EmployeeMiddle = msEmployeeMiddle
   
End Property
   
Public Property Let EmployeeLastName(sEmployeeLastName As String)
   
    msEmployeeLastName = sEmployeeLastName
   
End Property
   
Public Property Get EmployeeLastName() As String
   
    EmployeeLastName = msEmployeeLastName
   
End Property
   
Public Property Let HireDate(dtHireDate As Date)
   
    mdtHireDate = dtHireDate
   
End Property
   
Public Property Get HireDate() As Date
   
    HireDate = mdtHireDate
   
End Property
   
Public Property Let Salary(dSalary As Double)
   
    mdSalary = dSalary
   
End Property
   
Public Property Get Salary() As Double
   
    Salary = mdSalary
   
End Property

It looks right and it compiles, so that's good.

Here's my todo list:

  • Create the class module rather than copy and paste
  • Name the class module based on the table name
  • Select the database and table from a list on a userform
  • Establish Parent Child relationships between two classes
  • Create functions to fill the class from a recordset
  • Create a userform to add/edit/delete records

What if you ran through all the steps of this utility and you had a working CRUD program? That'd be pretty cool, I think. Sure, the userform wouldn't be just what you want, but it would give you a great starting point with the underlying structure in place.

Looking at the list, I expect this utility to be functionally complete in 2014 and polished in 2036. But I already used what I have here today and it saved me some time. Anything to add to this list?

Category: External Data, Classes  |  6 Comments

Lessons in SQL

14th November 2008, 04:07 pm by Dick Kusleika

Here's some code I have to generate a SQL statement

Public Function BuildRepSQL(ByRef clsRepLine As CRepLine, eType As aiDBStatus) As String
 
    Dim sReturn As String
 
    With clsRepLine
        Select Case eType
            Case aidbstatusdelete
                sReturn = "DELETE * FROM tblRepLine WHERE ReplineID = " & .RepLineID & ";"
            Case aidbstatusadd
                sReturn = "INSERT INTO tblRepLine (TxnLineID, ItemNumber, Quantity, SalesDollars) "
                sReturn = sReturn & "VALUES ('" & .TxnLineID & "'," & Nz(.ItemNumber) & "," & .Quantity & "," & .SalesDollars & ");"
            Case aidbstatusupdate
                sReturn = "UPDATE tblRepLine SET ItemNumber = " & Nz(.ItemNumber) & ", "
                sReturn = sReturn & "Quantity = " & .Quantity & ", "
                sReturn = sReturn & "SalesDollars = " & .SalesDollars
                sReturn = sReturn & " WHERE RepLineID = " & .RepLineID & ";"
        End Select
    End With
 
    BuildRepSQL = sReturn
 
End Function

Here's what a piece of that code looked like yesterday

Case aidbstatusupdate
    sReturn = "UPDATE tblRepLine SET ItemNumber = " & Nz(.ItemNumber) & ", "
    sReturn = sReturn & "Quantity = " & .Quantity & ", "
    sReturn = sReturn & "SalesDollars = " & .SalesDollars & ";"

The difference between those is that with the second one, 164 records in tblRepLine are made identical. I forgot to put the WHERE clause in there so it effected every record instead of just one. Whoops, thank goodness for backups. Luckily I have seven days of backups. Unfortunately, it seems I made this error more than seven days ago.

It only took me an hour or so to fix all the records. I'm glad it wasn't 1,164 records. It was a development database, not production, so it's going to get deleted someday anyway. But it's nice to have a development database that actually has correct data in it.

I've been working between Excel and Access a lot recently. I feel like I'm recoding the same stuff over and over - setting up classes, filling the class, writing back to the database, etc. I really need a framework generating application that sets up the classes and the basic structure of the code for me. It's one my 'to code' list.

Category: External Data, VBA  |  6 Comments

Table Tools in Excel 2007

14th November 2008, 12:43 pm by Ron de Bruin

Hi all

This week I create a small Add-in to add a few options to the contextual Table Tools tab.
This is a start and love to have feedback what you miss or like to see in a add-in like this.

This are now the options in the Add-in

1) Turn AutoFilter On or Off
2) Clear Filter of the ActiveCell's column
3) Special Filter options : Filter on selected Cell value, Color, Font color or Icon
4) Special Sort Options : Put selected Cell Color, Font Color or Icon on top and Custom Sort
5) Copy Visible data to a new worksheet
6) Copy visible data to a new workbook
7) Delete visible data
8)) Print Table
9) Split data of every unique value in a column to a new worksheet of the same workbook
10) Split data of every unique value in a column to a new worksheet in a new workbook
11) Split data of every unique value in a column to a new workbook in a new folder
This option creates a worksheets with hyperlinks to the workbooks so you can open them easy.

You can find the add-in together with a test workbook on the page
http://www.rondebruin.nl/table.htm

Thanks for looking at it
See also the links in the "More Information" part

Ron de Bruin
http://www.rondebruin.nl/tips.htm

Category: General, Downloads, Excel12  |  1 Comment

Ribbon, You’re Nothing to Me Now

6th November 2008, 11:52 am by Dick Kusleika

Ribbon, you're nothing to me now. You're not a brother, you're not a friend. I don't want to know you or what you do. I don't want to see you at the hotels, I don't want you near my house. When you see our mother, I want to know a day in advance, so I won't be there. You understand?

Last week I said: "As a developer (not a user), my first impression of the Ribbon is a good one. I fully expect it to turn sour ..."

Consider it soured.

Whilst struggling with the Ribbon over the last week, I took stock of what is important in a menu. There are three basic things that I want to do with a toolbar/menu.

  1. Run code when a button is pressed
  2. Add/delete buttons from existing toolbars
  3. Disable buttons when they're out of context

The Ribbon runs code when I press buttons. Awesome. There's probably a way to add buttons to existing groups, but I can't figure it out. In my add-in, I have this xml code:

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab" label="Invoices">
             <group id="dkInvGroup" label="Invoices">
                    <button id="dkInvNew"
                        label="Create New Invoice"
                        size="large"
                        onAction="CreateNewInvoice" />
                    <button id="dkInvOpen"
                        label="Open Existing Invoice"
                        size="large"
                        onAction="OpenInvoice" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

It adds a tab, then a group, then two buttons that I always want active. I want to add two more buttons when my invoice template is active. That should be easy enough.

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab">
             <group id="dkInvGroup">
                    <button id="dkinvSave"
                        label="Save Invoice"
                        size="large"
                        onAction="SaveInvoice"  />
                    <button id="dkInvCalcTax"
                        label="Calculate Sales Tax"
                        size="large"
                        onAction="CalcTax"  />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Nope, that doesn't do it. I open my template and the Ribbon doesn't change. The xml is well formed, per the Custom UI Editor. There may be a syntax error in there that's causing the problem, but I don't see it. And Excel isn't going to tell me where it is.

But I really don't want to add those buttons when the invoice template is active. I want to add them as disabled when the add-in is loaded and enable them when the invoice template is active. I should be able to do that with xml that looks like this:

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
            <tab id="dkInvTab">
             <group id="dkInvGroup">
                    <button id="dkinvSave" enabled="true" />
                    <button id="dkInvCalcTax" enabled="true" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customui>

Download music on line
Download mp3 music
mp3 online
buy music online
buy mp3 music
Download mp3 music online
Download free MP3 music
Download Adobe software
Download Adobe software
Download Adobe software
Download Adobe software
Music downloads
Online MP3 music
MP3 music downloads online
Downloads music mp3
mp3 downloads online
Buy phentermine
buy viagra online buy cialis online buy levitra online buy cialis professional online buy tramadol online
buy xanax no prescription
Nope, that doesn't do it. Apparently I have to use a getEnabled event to change the enabled status. As far as I can tell, that event only runs when the add-in is loaded. WTF good is that? How exactly is that an event? I'm really struggling to figure out what the people who designed this customization scheme were thinking.

Let's assume for a moment that there is a way to enable/disable buttons based on the active workbook. Do I have to use event procedures to somehow refresh the ribbon and check what's active? I already have to do that with the Commandbars object model, so what's the benefit to me, as a developer, to using the Ribbon? None, that I can see.

So I'm sticking with Commandbars for now. It adds them to the Add-ins tab, which I don't like. But it allows me to easily enable and disable buttons, which I do like.

I'm a reasonable guy. I'd like to think that the Ribbon is a work in process and that the next iteration will be better. But I've culled my list of requirements to three things and they didn't get those right. How can I expect them to produce something worthwhile when they can't even get the basics right. I'm soured. Even when someone shows me how to do what I want, I'll still be soured. It's not intuitive and it's hard to debug. I simply don't like it.

Category: Fluent UI  |  19 Comments
« Previous Entries