Automate Excel

Bookmark for Later: Generating Spreadsheets with PHP and PEAR

Looks like the perfect solution for web developers that need to develop dynamic spreadsheets for download.

Can anybody recommend a good tutorial on the reverse of this? Or reading and presenting spreadsheet data with PHP and HTML? I’m not a fan of the “Save as webpage”. Update: PHPReader

You might want to combine or join multiple ranges if the range string is too long, or you want to attempt to make your code easier to work with.

The need to combine multiple range strings is probably most common when selecting a large number of non contiguous cells with VBA.

One way to join multiple range strings is using VBA’s Union function. The syntax for the Union function is:

Union(Arg1 as Range, Arg2 as Range,etc,etc) as Range

The following is an example of using Union. First you declare some range variables, then set the range variables equal to something, and finally combine them using Union.

Sub UnionExample()
Dim Rng1, Rng2, Rng3 As Range

Set Rng1 = Range("A1,A3,A5,A7,A9,A11,A13,A15,A17,A19,A21")
Set Rng2 = Range("C1,C3,C5,C7,C9,C11,C13,C15,C17,C19,C21")
Set Rng3 = Range("E1,E3,E5,E7,E9,E11,E13,E15,E17,E19,E21")

Union(Rng1, Rng2, Rng3).Select

End Sub

What is autocorrect? From microsoft help: “a built-in proofreader that automatically corrects common typos, misspellings, grammatical errors, and incorrect capitalization.”

You can access the autocorrect dialogue box by clicking Tools->Autocorrect Options. For a detailed explanation you can read more at Microsoft’s website.

You can also use autocorrect as a shortcut to save time typing long names, places, or things. For instance if you work at the company “Superior Banking and Investments LLC.” you can set the autocorrect options to correct “mycompany” with “Superior Banking and Investments LLC.”, and every time you type the former in a cell it will change it to the latter.

And a final note about autocorrect for helpdesk employees or someone working in an office with a prankster or two. A lame joke someone may play on a less informed employee is to alter the autocorrect options slightly or altogether. For instance when an employee enters their name “John Doe” it changes it to “Johny Doe” or “Worst Employee Ever!”. Just remember the first place to look in a situation like this is the autocorrect settings.

You can display the formulas in all cells on a worksheet instead of displaying the actual result. For an example you can read the post Print the formulas in a worksheet.

But what if you want to display the formula instead of the result for just one cell? Excel does not have the built in ability to do this, however placing an apostrophe before your formula will get the job done.

Placing the following formula in a cell with a leading apostrophe:

‘=G8/F8

Will result in the formula to be displayed instead of the result.

If you need to create a copy of a worksheet in the same workbook there’s a quicker way than right clicking the tabname and using the “move or copy” dialogue box.

While holding the CTRL key down, left click the activesheets tab, then drag the tab to the location you would like a copy.

Note: This only works for the activesheet. In other words, if Sheet1 is active and you hold CTRL and click Sheet2’s tab, you will invoke the multiple select, not the copy.

You can launch Excel to display fullscreen using VBA by using this line of code:

Application.DisplayFullScreen = True

To exit fullscreen using VBA:

Application.DisplayFullScreen = False

While Excel’s main function is not word processing, many spreadsheets can accumulate a large amount of text, and spell checking a worksheet is usually the last thing people think to of doing.

If your spreadsheets fall in this category, you might try some creative ways to launch Excel’s “SpellChecker” from code as a gentle reminder.

The syntax to launch the Spelling dialog box using the default settings:

expression.CheckSpelling

expression is a reference to the range or object to check the spelling of.

The following is example code to launch the Spelling dialog box before a workbook closes, but before asking to save. The expression tells VBA to check the spelling of all the cells in Sheet1. For the code to work you must place it in the ThisWorkbook code window in VBA.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheet1.Cells.CheckSpelling

End Sub

To change a cells background color using VBA you can use the interior.colorindex property. Here’s a couple ways to change the background color of cell A1.

An example using the Range() method:

Range("A1").Interior.ColorIndex = 5

An example using the Cells() method:

Cells(1, 1).Interior.ColorIndex = 15

Need an easy way to determine what number equals what color? Check out Color Reference For Colorindex.

A formula to insert the tabname in a cell in excel:

=MID(CELL(”filename”,A1),SEARCH(”]”,CELL(”filename”,A1))+1,LEN
(CELL(”filename”,A1))-SEARCH(”]”,CELL(”filename”,A1)))

Note: Remove the line break from the formula in Excel.

A formula to insert the filename of the workbook in a cell:

=MID(CELL(”filename”,A1),FIND(”[",CELL("filename",A1))+1,FIND("]“,
CELL(”filename”,A1))-FIND(”[”,CELL(”filename”,A1))-1)

Note: Remove the line break from the formula in Excel.