Code For Excel And Outlook Blog

Advanced Excel Conference Recap

JP — Mon, 06 Jul 2009 11:30:54 +0000

I attended this year's Advanced Excel Conference. There were presentations by Bob Umlas and Jon Peltier, Excel MVPs. See link for topics list.

This year's conference was different from last year's in the following ways:

I didn't eat at Burger King on the way to Atlantic City. Or on the way home.
Red Bull and Vitamin Water (Energy) help you stay awake on the road.
I have a much better job than I did last time. They don't offer paid vacation, but I can come and go as I please (more or less).
I attended the full conference (both days).

The weather was terrible; it's still cloudy and raining in New York to this day. But as we were indoors most of the time, it didn't matter. The only time I went out was for dinner on Wednesday night, when most of the group went to an Irish pub inside the Tropicana hotel. Food was great, drinking beer and talking about Excel and Access was a lot of fun.

I got there on Tuesday the 16th around 11:30 pm, same as last year. The conference started at 8:30 on Wednesday morning. Bob spoke until about 12, then Jon took over at 1 pm until about 5. Thursday was the reverse; Jon started and then after lunch, Bob presented until 5pm. My laptop actually decided to cooperate and work most of the time; last time, the monitor kept powering down.

Instead of CDs with the presentation spreadsheets on them, Jon passed around a USB drive with the files on them. It worked really well because you just insert the drive into an available USB port, then drag and drop the files over. And there were a lot of files. Bob used a different spreadsheet for each topic, so there were about 25 or so for his Intermediate VBA class. Jon used a single workbook with multiple worksheets.

I also met a few people I correspond with online, including Ray Schinzel and Al Vachris.

Overall, a very good experience, and worth the money. I learned a lot about the Range object from Bob, and charting is much easier thanks to Jon's presentation (which I missed last year).

Gas prices, on the other hand, have remained high. Hopefully this will change for next year's conference.

This post is from : Code For Excel And Outlook Blog. Copyright © Code For Excel And Outlook.

Do you need plug-and-play Excel Dashboards?

Advanced Excel Conference Recap

Need Ribbon help? You're not alone.

JP — Fri, 03 Jul 2009 14:23:41 +0000

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.

This post is from : Code For Excel And Outlook Blog. Copyright © Code For Excel And Outlook.

Do you need plug-and-play Excel Dashboards?

Need Ribbon help? You're not alone.

Q2 Stats Wrap Up

JP — Thu, 02 Jul 2009 11:30:46 +0000

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:

Excel VBA Macro – a wonderfully keyword-loaded URL with a useful blog.
Chandoo and Pointy Haired Dilbert
John Walkenbach's Spreadsheet Page
Debra Dalgleish and her Contextures blog
Jon Peltier's PTS Blog
Daily Dose of Excel
NewtonExcelBach
Excel Guru Ken Puls

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.

This post is from : Code For Excel And Outlook Blog. Copyright © Code For Excel And Outlook.

Do you need plug-and-play Excel Dashboards?

Q2 Stats Wrap Up

List matching files in a folder, part two

JP — Wed, 01 Jul 2009 19:02:07 +0000

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.

This post is from : Code For Excel And Outlook Blog. Copyright © Code For Excel And Outlook.

Do you need plug-and-play Excel Dashboards?

List matching files in a folder, part two

List matching files in a directory

JP — Mon, 29 Jun 2009 16:34:13 +0000

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.

This post is from : Code For Excel And Outlook Blog. Copyright © Code For Excel And Outlook.

Do you need plug-and-play Excel Dashboards?

List matching files in a directory