I have been to The Xcel Files before but never got to really explore it as deep as it deserves. I did this morning. And suddenly my to-read list doubled in size !! especially those API calls and libraries that can be called and used from Excel.
I think the time has come for me to go down the same route as Jwalk and Andrew and start writing my own set of utilities. It IS the best way to learn and master VBA. I will try to come up with content different from PUP 6 (Jwalk) and Andrew’s utilities… but I realize that my creativity is going to face a very though time so if you have any ideas for utilities that you would like to have but don’t, send me a comment.
By the way, if you don’t mind spending a few dollars, buy PUP 6 with the VBA source code. It’s definitely one of the best learning experience I have had on Excel and VBA.
Last week I posted about a user-defined function to get the unique values in a range. I’ve now extended this function to return unique or duplicate values based on a parameter passed through the function call. so the function definition now changes from :
Function Unique(AllCells As Range) As Variant
to
Function FilterUniques(AllCells As Range, GetUnique As Integer) As Variant
If you want the function to return Unique values only, then set GetUnique to 1 in your function call. Setting GetUnique to 0 will return Duplicate values.
The function basically imports all the values in the range to the array Entries with a corresponding array EntCount that counts the number of occurrences of every value. The rest is easy to figure out.
Obviously, it would be a good idea to create your own add-in where you can have such functions at close range for your every-day use.
Here’s the code (remember to correct the comments).
Function FilterUniques(AllCells As Range, GetUnique As Integer) As Variant
Dim Entries() As String, EntCount() As Integer
Dim GetUniques() As Variant
Dim EntryCount, i As Integer
Dim Cell As Range
Dim Found As Boolean
EntryCount = 0
For Each Cell In AllCells
Found = False
If EntryCount = 0 Then
EntryCount = EntryCount + 1
Found = True
ReDim Preserve Entries(EntryCount)
ReDim Preserve EntCount(EntryCount)
Entries(1) = Cell.Value
EntCount(1) = 1
End If
i = 1
While i <=”" entrycount and Not Found
If Entries(i) = Cell.Value Then
Found = True
EntCount(i) = EntCount(i) + 1
End If
i = i + 1
Wend
If Not Found Then
EntryCount = EntryCount + 1
Found = True
ReDim Preserve Entries(EntryCount)
ReDim Preserve EntCount(EntryCount)
Entries(i) = Cell.Value
EntCount(i) = 1
End If
Next Cell
Dim j, SwapInt As Integer
Dim SwapStr As String
For i = 1 To EntryCount - 1
For j = i + 1 To EntryCount
If Entries(i) > Entries(j) Then
SwapStr = Entries(j)
Entries(j) = Entries(i)
Entries(i) = SwapStr
SwapInt = EntCount(j)
EntCount(j) = EntCount(i)
EntCount(i) = SwapInt
End If
Next j
Next i
Dim OutCount As Integer
OutCount = 0
If GetUnique = 1 Then
For i = 1 To EntryCount
If EntCount(i) = 1 Then
GetUniques(OutCount) = Entries(i)
OutCount = OutCount + 1
End If
Next i
ElseIf GetUnique = 0 Then
For i = 1 To EntryCount
If EntCount(i) > 1 Then
ReDim Preserve GetUniques(OutCount)
GetUniques(OutCount) = Entries(i)
OutCount = OutCount + 1
End If
Next i
End If
FilterUniques = GetUniques
If AllCells.Rows.Count >= AllCells.Columns.Count Then
FilterUniques = WorksheetFunction.Transpose(FilterUniques)
End If
End Function
screen-shot :
I do like Infocenter. I do. It is liberating writing the queries on and on again and correcting the typos and other structural glitches. But then again, what if you had your standard queries, all of them in a dropdown box, with all of your KTP databases (production and test) in another dropdown box and you could import all of the data into an Excel spreadsheet immediately ? tada ! enters KTP Data v1.0 module.
All of the dropdown data is obviously configured in a “Config” sheet where all the KTP databases, connection strings and SQL queries are configured and obviously fed into the dropdown boxes :
Click on “Retrieve” and the data gets directly fed to the spreadsheet.
Obviously, there’s always the need to retrieve data from the same tables of two different databases, hence the options of :
maintaining the previous data on the spreadsheet when the new data is imported and
selecting a new cell where to paste the imported data.
IF any of you has worked on KTP, I appreciate the comments and suggestions.
Yes. yes. yes….. finally got permalinks working, understood what an .htaccess file is and how this all affects the trackbacks and the comments … trying to impress the few remaining blognorants out there …
I would have liked to think of this as a personal achievement but then again, looking at the number of blogs that have been doing this for quite some time, my ego goes back to the back seat, very very quietly.
PricelessWare.org contains an extensive list of all kinds of softwares for all kinds of needs. It’s always my starting point for any search for tools.
J-walk also posted a while back about a Tech Support Alert on the best free utilities.
Make sure you check out the comments on the post at J-walk’s for interesting links too.
Comments Off
Andrew at Andrew’s Excel Tips had a post a few days back about a very nice set of utilities that he created. I’ve tried it and am very impressed with the results. However, what tickled my curiosity the most is the ability to add a shortcut to the right-click menu. So obviously, I started snooping around his code (sorry Andrew !) and discovered that the menu structure is stored in the add-in worksheet with complete information on the menu names, sub-menu names, icons and references. Mmm… there’s a lot more to explore there. So there goes a new task on my to-do list…. but just when I was about to try coding something similar comes a post from Colo with a beautiful Menu Generator:
My “Menu Generator” is a program which allows you to make your own menu from an Excel add-in. Not only is it easy to create your own menu, you can also use it to update your add-in with new code. All you have to do is write your menu in the worksheet of the file and follow the Menu Wizard’s instructions. No programming for the command bar is necessary.
If you need the functionality, I strongly suggest you use this tool.
If you’re anything like me and your to-do list included learning how to create something similar, then you now have a benchmark to mark your progress to.
Good luck !
I’m trying to think of why someone would want to export their contacts to Excel… this then got me to think of the reason that makes me do it, and couldn’t find an answer except for the obvious one : “because I can do it”. so whatever your reason is, here goes :
(1) Exposing the Outlook model : in any automation job, your primary task is to make sure that the ‘automated’ application is exposed to Excel, which means you can control the other application’s objects from Excel. In this example, you will need to expose Outlook’s library as follows. In the Visual Basic Editor (Alt+F11 from Excel), go to Tools —> References. Locate the “Microsoft Outlook 9.0 Object Library“ (or other versions i.e. Microsoft Outlook x.x Object Library), check the box, then click on OK.
(2) Import the contacts:
Sub GetContacts()
Dim olApp As Outlook.Application
Dim olNs As Outlook.NameSpace
Dim olFldr As Outlook.MAPIFolder
Dim olItms As Outlook.Items
Dim olContact As Variant
Dim i As Long
Application.ScreenUpdating = False
Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace(”MAPI”)
Set olFldr = olNs.GetDefaultFolder(olFolderContacts)
Set olItms = olFldr.Items
olItms.Sort “File As”
i = 1
For Each olContact In olItms
On Error Resume Next
ActiveSheet.Cells(i, 1).Value = olContact.FileAs
ActiveSheet.Cells(i, 2).Value = olContact.Categories
ActiveSheet.Cells(i, 3).Value = “‘” & olContact.MobileTelephoneNumber
ActiveSheet.Cells(i, 4).Value = “‘” & olContact.BusinessTelephoneNumber
ActiveSheet.Cells(i, 5).Value = “‘” & olContact.Email1Address
If olContact.CompanyName <> “” Then
ActiveSheet.Cells(i, 4).Value = olContact.CompanyName
End If
i = i + 1
Next olContact
Set olFldr = Nothing
Set olNs = Nothing
Set olApp = Nothing
Application.ScreenUpdating = True
End Sub
Dick over at Dick’s Clicks is a must read on Automating Outlook with Excel.
Comments Off
You’ve started creating your functions and you’re very proud of the results. Now you want to have them as standard functions in Excel. I recommend to create an Excel Add-In where you can store all your functions and procedures, and have them accessible from the main application window or from your code. Be careful though if you’re redistributing your work.
The following example will guide you through creating the Pragma Add-In and calling the Test procedure from Excel.
1– Create a new workbook.
2– Open the Visual Basic Editor (Alt + F11)
3– Add a module to your workbook : in the Project Explorer, right-click on any of the objects in the workbook and select Insert –> Module
4– Add the following procedure in the Visual Basic Editor :
Sub Test()
MsgBox “Pragma”
End Sub
5– From the main application window (Excel), save your workbook as
FileName = “Pragma” unless you really don’t like the name. really.
Save As Type = Microsoft Excel Add-In (*.xla)
6– Still from the main application window, go to Tools —> Add-Ins and then browse to the folder where you saved the Add-In and select it.
7– Technically you’re done but if you want to test the Add-In, press Alt+F8. The procedure you’ve just created will not appear in the list so you need to type “test” (Macro Name) and Run.
Filling a one-column listbox is a relatively straight-forward job. Use the .AddItem method of a ListBox and you’re done.
Filling a multi-column Listbox is also as simple, if you know the right command.
Basically, what .AddItem does is add a row to the bottom of the list. Now what you need to do is use .List to assign the values to the 2nd, 3rd or more column.
The following code adds the values “RowXcolumn1”,“RowXcolumn2”,“RowXcolumn3” to a 3–column listbox lstMyListBox.
Const NumColumns = 3
Const NumRows = 10
Dim i As Integer, j As Integer
For i = 1 To NumRows
lstMyListBox.AddItem “Row” & i & “Column1″
For j = 1 To NumColumns - 1
lstMyListBox.List(i - 1, j) = “Row” & i & “Column” & j
Next j
Next i
Remember to set the ColumnCount property of the ListBox to the number of columns you need. if you’re trying this example, set the ColumnWidths property of the listbox of 75pt.
Comments Off
Mark at AutomatteExcel.com is officially the first to have discovered Excel Pragma…. Mark is also the first to post a comment !
This is a great step for Excel Pragma. Thanks Mark !
