Need Ribbon help? You're not alone.

July 3, 2009 – 10:23 am by JP

    Today on TechRepublic's Microsoft Office blog, Susan Harkins mentions an add-in for several Office 2007 products that helps you find menu commands.

    Say what?

    You need an additional product for that?

    Sorry, but there's something wrong when you have to use an add-in to search for commands in one of the most popular programs of all time. It reminds me of how there are certain WordPress features missing from the standard install, yet desperately needed (like built-in caching). I feel bad for Office 2007 power users who are disoriented by the new menus.

    Isn't it sad that you have to install an add-in to use a product's core functionality?

    Here's the tagline from the add-in's homepage at Office Labs: "You know there’s a button for it, but you don’t know or remember where it is."

    Maybe that's because you moved it. Why does a phrase like that even apply to a piece of software? If you have to utter a sentence like that, there's something wrong with your product.

    I've never seen so much time spent tweaking a user interface. How many productive hours have been lost? There are add-ins and commercial products and books and tutorial pages dedicated to rearranging elements of the UI or helping you to make it look like Excel 2003. Does anyone do this with 2003, or do you just add a few commands to an existing toolbar (if necessary)?

    As for the Ribbon's effect on productivity, I leave for others to decide. But here's a great article about it.

    Simon Murphy has a series of posts about the Excel Ribbon, the latest of which is Ribbon Style Princess which highlights more UI woes. I'd love to hear from anyone out there, agree or disagree.

Posted in Rant | 2 Comments »

Q2 Stats Wrap Up

July 2, 2009 – 7:30 am by JP

    Quarter 2 of this year has passed, and here are the highlights of the last three months.

Visits

    The site had 800 visitors on June 25th, the most for a single day this past quarter, and seems to be consistently producing numbers in the mid-600 to 700 range on a daily basis. There was a slight drop in overall traffic when I switched the main site to use WordPress as a CMS, but finally seems to be recovering in the last week or so.

    The VBA Search Engine hit 400 sites (become a volunteer contributor), and we're very close to 200 blog posts and 800 feed subscribers.

    As usual, the most popular pages were the blog homepage, the Excel page and the Outlook page.

    There were 36 posts and 59 comments/trackbacks (half of the comments were me, I think).

    Thanks to Dick's blog, the Random Data Generator was by far the most popular page this past quarter. The Add-Ins page was also very popular during this time.

Top Referral Sources:

    I'd to thank and send some link love to the following sites that top out the list of referring sites for Q2:

    Plus some new friends at accessclub.jp and utteraccess.com, who seem to be paying visits more frequently. I attribute that to the Access code samples I've been posting.

Add-Ins

    To save bandwidth, I've moved the add-ins to Amazon's S3 service. The download traffic was extremely high last month, so I also decided to move all the images to Photo Bucket. It seems to be working, and the site is faster with PHP compression (take that, mod_deflate!). So much so that I was able to turn off WP-Super Cache. (I don't think I configured it properly anyway.) If I see the bandwidth being abused again (especially the zip files, which third party download sites love to link to), I may move the files back here, and simply block any access to zip files if there's no valid referrer from this site.

    The add-in download traffic was brisk, both from here and from download.com. Here is the breakdown. Next quarter it won't be accurate, since Amazon S3 doesn't show you which files were downloaded.

April add-in downloads

  • Random Data Generator – 441 downloads
  • Post To Twitter – 250 downloads
  • ShipTrack (and UI) – 127 downloads
  • Data Filter Tool – 298 downloads
  • SheetPicker – 108 downloads
  • Amazon Ad Link Generator – 29 downloads
  • Bingo Number Generator (.NET version) – 5 downloads

May add-in downloads

  • Random Data Generator – 340 downloads
  • Post To Twitter – 117 downloads
  • ShipTrack (and UI) – 138 downloads
  • Data Filter Tool – 159 downloads
  • SheetPicker – 56 downloads
  • Amazon Ad Link Generator – 29 downloads
  • Bingo Number Generator (.NET version) – 4 downloads

June add-in downloads

  • Random Data Generator – 217 downloads
  • Post To Twitter – 94 downloads
  • ShipTrack (and UI) – 114 downloads
  • Data Filter Tool – 106 downloads
  • SheetPicker – 21 downloads
  • Amazon Ad Link Generator – 19 downloads
  • Bingo Number Generator (.NET version) – 11 downloads

    Visit the Add-Ins page to download any of these Excel add-ins.

Other News

    Several DMCA complaints were successfully filed with ISPs that hosted download sites that for some unknown reason felt very comfortable taking my add-ins without asking and putting them up for download on their own sites. The site owners, who live in Hungary, China, and other eastern locations, choose to host their sites in the U.S.A., where we have laws against that. A few form letters, combined with a few .htaccess tweaks, should prevent any more incidents.

    Coming soon (read: six months) I'll have a standalone version (VB .NET) of the Random Data Generator which will export to Excel/Access/CSV and be available for purchase.

Stats Mania!

    Seems everyone's getting into the stats mood. My timeline might be wrong, but I believe Chandoo started it, then J-Walk posted his, then Dick, then Jon.

Best month ever
Visitor Stats
June Stats
Web Stats – June 2009

And Debra from Contextures also posted hers.

Tags: stats

Posted in Administrative | 2 Comments »

List matching files in a folder, part two

July 1, 2009 – 3:02 pm by JP

    In my previous post titled List matching files in a directory, I posted a method that creates a string array of filenames in a folder that match a given file extension. Here we are going to put that code to some real use.

    Don't forget to visit that post and grab the GetFilesList and GetFileType procedures.

    Here's the problem: I have a friend who has a column of values on a worksheet. He has a bunch of other workbooks he needs to search for the values in the column, so he has put those workbooks into a separate folder. The challenge? Search each of the workbooks for each of the values in the column, then copy and paste part of the found cell's row next to the value in the original workbook.

    Normally I would just use VLOOKUP, INDEX/MATCH or OFFSET to retrieve the value, but the other workbooks have the data all out of order. In other words, the value we're looking for isn't guaranteed to be in a certain column. It could be anywhere on the first worksheet.

    Warning: The procedures below are slightly hard coded — this is a real application after all. ;)

    First we'll use the GetFilesList procedure to loop through a specified folder on my friend's desktop and create a string array of workbook names.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub FindWorkbookData()

Dim folder As String
Dim extension As String
Dim dummyString As String
Dim fileslist() As String
Dim i As Long

folder = Environ("userprofile") & "\Desktop\Forms\"
extension = "xls"

' build list of Excel files from folder
fileslist = GetFilesList(folder, extension)

' check if array is empty (i.e. no files matching extension exist in given folder)
On Error Resume Next
dummyString = fileslist(0)

If Err <> 0 Then
  MsgBox "No files found with " & extension & " extension in" & vbCrLf & folder, vbExclamation
  Exit Sub
End If
On Error GoTo 0

For i = 0 To UBound(fileslist)
  Call ProcessFile(fileslist(i))
Next i

End Sub

    Once we have the array of names, we call a custom function called ProcessFile which takes a string argument. In this case, we want to process each of the spreadsheets in the folder.

    ProcessFile will first set a reference to the currently open workbook (the one with the values we want to search the other workbooks for). The values are present in column A, starting in cell A2. Then the procedure will open the specified workbook (the first one in the array) and use the Range.Find Method against each value in the column, to see if the workbook contains any of the values we need to find. If the given value in column A is found in the newly opened workbook, we copy the values in columns A through L in the same row as the found value into the original workbook, right next to the search value. Sort of like a VLOOKUP on steroids.

    At the end, we print the filename where the value was found, in case there are a lot of workbooks and we need to know later where specifically the value was located. We don't want to have to then go manually through each workbook!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
Sub ProcessFile(fileName As String)

Dim folder As String
Dim rng As Excel.Range
Dim cell As Excel.Range
Dim wkbk As Excel.Workbook
Dim wksht As Excel.Worksheet
Dim currentWkbk As Excel.Workbook
Dim currentWksht As Excel.Worksheet
Dim currentRange As Excel.Range
 
Set wkbk = ActiveWorkbook
Set wksht = wkbk.Sheets("My first sheet name")
Set rng = wksht.Range(wksht.Range("A2"), wksht.Range("A" & Rows.Count).End(xlUp))
 
folder  = Environ("userprofile") & "\Desktop\Forms\"

' open workbook
Set currentWkbk = Workbooks.Open(folder & fileName)
Set currentWksht = currentWkbk.Sheets(1)
Set currentRange = currentWksht.UsedRange
 
Dim foundRange As Excel.Range
For Each cell In rng
 
If cell.Offset(0, 1).Value = "" Then ' only check if previous info wasn't found
On Error Resume Next
  Set foundRange = currentRange.Find(cell.Value)
  If Not foundRange Is Nothing Then  ' range found in this file
    Set foundRange = currentWksht.Range(currentWksht.Range("A" & foundRange.Row), currentWksht.Range("L" & foundRange.Row))
   
    foundRange.Copy cell.Offset(0, 1)
    cell.Offset(0, 13).Value = fileName
    cell.EntireRow.AutoFit
  End If
 
On Error GoTo 0
 
End If
 
Next cell
 
currentWkbk.Close False

End Sub

    Right after the For loop starts, we check to see if anything already exists in column B (the place where we are writing information when the value is found). If so, that means the value was already found and we can skip to the next one. This assumes that there is nothing already in column B and that each value in column A exists in only one (at most) of the other workbooks. Both of which are true in this case.

Tags: GetFilesList, GetFileType

Posted in Excel | No Comments, add yours now! »

List matching files in a directory

June 29, 2009 – 12:34 pm by JP

    In List matching folders in a directory, I posted a method for creating a list of matching folders in a specified directory.

    Here is a method to list the files in a directory that match a specific file extension. This would be useful when you want to perform some operation on all files of a certain type in a folder (move, copy, open, delete, etc).

    First I'll post the method with a small sample, and in the next post I'll use it in a real application that opens every Excel file in a folder and performs some calculation.

    The following function, titled GetFilesList, returns a string array of filenames in the folder variable folder that have the file extension specified in fileType. Notice the parentheses after the type declaration in row 1 is what tells VBA we want it to return an array.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Function GetFilesList(ByVal folder As String, ByVal fileType As String) As String()
' build list of files in a folder folder
Dim fileslist() As String
Dim i As Long
Dim currentWorkbook As String

currentWorkbook = Dir(folder)

Do While Len(currentWorkbook) > 0
  If UCase$(GetFileType(currentWorkbook)) = "." & UCase$(fileType) Then
    ReDim Preserve fileslist(i)
    fileslist(i) = currentWorkbook
    i = i + 1
  End If

  currentWorkbook = Dir
Loop
GetFilesList = fileslist

End Function

Function GetFileType(ByVal fileName As String) As String
' get file extension
' assumes there are no periods in filename
' (other than the one between the filename and extension)

GetFileType = Mid$(fileName, InStrRev(fileName, "."), Len(fileName))

End Function

Usage:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub TestFileListGet()
Dim str() As String
Dim i As Long
Dim dummyString As String
Dim folder As String
Dim extension As String

folder = Environ("userprofile") & "\Desktop\My Files\"
extension = "xls"

str = GetFilesList(folder, extension)

' check if array is empty (i.e. no files matching extension exist in given folder)
On Error Resume Next

dummyString = str(0)
If Err <> 0 Then
  MsgBox "No Files found with " & extension & " extension in " & vbCrLf & folder, vbExclamation
  Exit Sub
End If

On Error GoTo 0

' str contains array of matching filenames from given folder,
' do whatever you want with it
For i = LBound(str) To UBound(str)
  Debug.Print str(i)
Next i

MsgBox "Found " & UBound(str) + 1 & " file(s) with " & extension & _
  " extension in folder:" & vbCrLf & folder, vbInformation

End Sub

    If you want to limit the filetypes that a user can browse for, use an enumerated section like this:

1
2
3
4
5
6
Enum MyType As FileToOpen
xls
ppt
doc
mdb
End Enum

    Then change the first line of GetFilesList to

Function GetFilesList(ByVal folder As String, ByVal fileType As FileToOpen) As String()

    You would also need to convert the constant back to a string using Select Case or If statements, for comparison purposes, but now the function has custom Intellisense (or is it Auto List Members?) which limits selection to only Excel, PowerPoint, Word and Access documents.

    A custom function called GetFileType, which I used in a previous post, returns the file extension for a given file. It includes the period "." as well, so the IF statement adds the period to the matching file extension to see if there's a match. ReDim Preserve is used because we don't know how many of a given file type will be in the folder. There can be zero, one, or two hundred, and the code doesn't have to be edited.

    To check if the array is empty (i.e. there were no matching files in the folder), simply try to assign the first value to a string. An error is thrown if there is no value. After the "On Error GoTo 0″ in the sample procedure, you can do anything you like with the array. In my sample, I printed the members of the array to the Immediate window, then used the UBound property to print a count of files found.

    Next post we'll see a real example of this code in action.

Tags: GetFilesList, GetFileType, InStrRev, ReDim Preserve

Posted in Excel, VBA | No Comments, add yours now! »

Next Page »  1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 28 | 29 | 30 | 31 | 32 | 33 | 34 | 35 | 36 | 37 | 38 | 39 | 40 | 41 | 42 | 43 | 44 | 45 | 46 | 47 | 48 | 49 | 50  Next Page »
Hosted by FutureQuest · Copyright © 2007-2008 Jimmy Peña · Disclaimer · Theme by Bob · Return to top ↑