Posts

Generating a List of File Names

/0 Comments/in /by

This tip describes how to retrieve a list of file names in a folder and display them in a worksheet.This technique uses an Excel 4 XLM macro function in a named formula. It’s useful because it’s a relatively simple way of getting a list of file names into a worksheet – something that normally requires a complex VBA macro.

Start with a new workbook and then follow these steps to create a named formula:

  1. Choose Formulas Tab ➜ Define Name to display the New Name dialog box (CTRL + F3).
  2. Type FileList in the Name field.
  3. Enter the formula in the Refers To field =FILES(Sheet1!$A$1)
  4. Click OK to close the New Name dialog box.

filelist_name_manager

Note that the FILES function is not a normal Worksheet Function. Rather, it’s an old XLM style macro function that is intended to be used on a special macro sheet. This function takes one argument (a directory path and a file specification) and returns an array of file names in that directory that match the file specification. A normal worksheet formula cannot use these old XLM functions, but named formulas can. After defining the named formula, enter a directory path and file specification into cell A1.

For example:

  • F:\Future in Hands\Training\*  to display all files
  • F:\Future in Hands\Training\*xl* to display all Excel files

Then this formula displays the first file found: =INDEX(FileList, 1)

If you change the second argument to 2, it displays the second file found, and so on.

Below figure shows an example. The path and file specification is in cell A1. Cell A2 contains this formula, copied down the column: =INDEX(FileList,ROW()-1)

The ROW function, as used here, generates a series of consecutive integers: 1, 2, 3, and so on. These integers are used as the second argument for the INDEX function. When you change the directory in cell A1, the formulas update to display the new file names.

generating-a-list-of-file-names

Note

If you use this technique, you must save the workbook as a macro-enabled file (with an *.xlsm or *.xls extension).

 

Send Outlook Mail from Excel VBA

/0 Comments/in , , /by

This article features code that you can use to send outlook mail from Excel by using the Microsoft Outlook object model. The below code can be used to send an Outlook mail directly from Excel.

Sub SendOutlookEmail()

Dim OutlookApp As Object
Dim OutlookMail As Object

Set OutlookApp = CreateObject(“Outlook.Application”)
Set OutlookMail = OutlookApp.CreateItem(0)

On Error Resume Next

With OutlookMail

.To = “abc@xyz.com”
.CC = “”
.BCC = “”
.Subject = “This is a Subject Line”
.Body = “This is a Mail Body.”
.Attachments.Add “C:\Report Folder\Test Report.xlsx”
.display

End With

On Error GoTo 0

Set OutlookMail = Nothing
Set OutlookApp = Nothing

End Sub

Note:

  • .display displays the mail with the information provided above. You can use .send instead of .Display to send the mail instantly.
  • Mail can be sent to multiple users. Simply use “abc@xyz.com;def@xyz.com”.
  • Multiple Attachments can be sent by using .Attachments.Add “File Path” in the next line.

Filling Gaps in a Range

/0 Comments/in /by

When you import data, you can sometimes end up with a worksheet that looks something like the one shown in the figure. This type of report formatting is common. As you can see, an entry in column A applies to several rows of data. If you sort this type of list, the missing data messes things up, and you can no longer tell who sold what when.

filling-gaps-in-a-range

If your list is small, you can enter the missing cell values manually or by using a series of Home ➜ Editing ➜ Fill ➜ Down commands (or its Ctrl+D shortcut). But if you have a large list that’s in this format, you need a better way of filling in those cell values. Here’s how:

  1. In Cell A4, type =A3 and press Enter.
  2. Select A4 and Copy the Cell (CTRL +C).
  3. Select the range that has the gaps (A3:A14 in this example).
  4. Choose Home ➜ Editing ➜ Find & Select ➜ Go To Special. The Go To Special dialog box appears.
  5. Select the Blanks option and click OK. This action selects the blank cells in the original selection.
  6. Paste the Data (CTRL +V) and its done.
  7. You can Replace the formula by Paste Special ➜ Values

After you complete these steps, the gaps are filled in with the correct information. Now it’s a normal list, and you can do whatever you like with it – including sorting.

Shading Alternate Rows in Excel

/0 Comments/in /by

When you create a table (using Insert➜Tables➜Table), you have the option of formatting the table in such a way that alternate rows are shaded. Shading Alternate Rows can make your spreadsheets easier to read.

This tip describes how to use conditional formatting to obtain alternate row shading for any range of data. It’s a dynamic technique: If you add or delete rows within the conditional formatting area, the shading is updated automatically. Shading-Alternate-Rows

Here’s how to apply shading to alternate rows:

  1. Select the range to format.
  2. Choose Home➜Conditional Formatting➜New Rule. The New Formatting Rule dialog box appears.
  3. For the rule type, choose Use a Formula to Determine Which Cells to Format.
  4. Enter the formula in the box labelled Format Values Where This Formulas Is True: =MOD(ROW(),2)=0
  5. Click the Format button. The Format Cells dialog box appears.
  6. In the Format Cells dialog box, click the Fill tab and select a background fill color.
  7. Click OK to close the Format Cells dialog box, and click OK again to close the New Formatting Rule dialog box.

This conditional formatting formula uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). For cells in even-numbered rows, the MOD function returns 0, and cells in that row are formatted.

For alternate shading of columns, use the COLUMN function instead of the ROW function.