Entries Categorized as 'Excel'

Don’t rely on locking/password protecting to hide your data!

Date January 2, 2009

I’ve seen this a few too many times.  Someone wants to prevent the user of a spreadsheet from viewing detail data, so they hide the sheets (or cells), lock it, and password protect it.  In about 4 line of code, that data is visible again:
Sub copyWorkbook()
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 01/02/2009
‘ Makes a [...]

Posted in Excel Comments No Comments »

Some automated graphing

Date May 5, 2008

Heres some code I’ve used in one form or another to add charts for multiple worksheets that have the same layout (like monthly breakdowns of data)  
For Each wSheet In Sheets
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=wSheet.Range(”B4:AX32″), PlotBy:=xlRows
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=  ”Graph_” & wSheet.Name
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlRight
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.Deselect
    ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With [...]

Posted in Excel Comments No Comments »

Get data from a cell in a closed workbook

Date March 17, 2008

There are lots of ways to get data from other workbooks. This has helped when grabbing summary data for report summaries (see here for creating an email)
Example:
p = “\some\directory”
f = “Some fake file.xls”
s = “Sheet1″
a = “A1″
result = GetValue(p, f, s, a)
Private Function GetValue(path, file, sheet, ref)
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ [...]

Posted in Excel Comments No Comments »

Create an email and attach a spreadsheet

Date March 12, 2008

As long as you’re automating your reports, you might as well automate the email notification!
Example:
strHTMLBody = “<html>Test Message,<br/><br/>This is a test. If this were not a test:” & _
“<ul><li>There would be useful information here</li><li>There would perhaps be more information here</li></ul><br/>Thank you</html>”
Call CreateNewMail(”recipients@somedomain.com;morerecipients@anotherdomain.com”, “ccedfolks@adomain.com”, “Test Subject”, strHTMLBody,”Some fake file.xls”)
Make sure you enable Microsoft Outlook 11 [...]

Posted in Excel Comments No Comments »

Last day of month for a date

Date March 6, 2008

When you create a date with the day part of 0, excel uses the last day of the previous month.
=DATE(YEAR(A1),MONTH(A1)+1,0)
Number of days in the month for a cell:
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Posted in Excel Comments No Comments »

Eliminate prompt to update links

Date February 29, 2008

Not every post has to be about advanced concepts!
To turn off the “Do you want to update links” prompt from popping up every time you open a spreadsheet (you can still update links manually):
Edit | Links…
Lower left corner, click on “Startup Prompt…”
Select “Don’t display the alert and don’t update automatic links”
OK

Posted in Excel Comments No Comments »

Dynamic Named Range

Date February 29, 2008

If you want to reference a range whose size changes (for pivot tables, for example)
Insert|Name|Define…
Name the range, and in the refers to box:
=OFFSET(’Sheet Name’!$A$1,0,0,COUNTA(’Sheet Name’!$A:$A),COUNTA(’Sheet Name’!$1:$1))
the $A$1 is the upper left of the range (change as needed)
The first “COUNTA()” is the number of rows, and the second “COUNTA()” is the number of columns.
THIS ASSUMES THAT [...]

Posted in Excel Comments No Comments »

Regular Expressions in Excel

Date February 22, 2008

Regular Expressions are very powerful pattern matching tools that can be used to find, return, or even substitute string patterns.
To use these, you’ll need to enable the VB Reg-ex Library:

In Visual Basic Editor - Tools|References
Select “Microsoft VBScript Regular Expressions 5.5″

For Help with Regular Expressions, Google is your friend, but you might start here:
http://www.regular-expressions.info/reference.html
Here are a [...]

Posted in Excel Comments No Comments »

File name and path of current workbook

Date February 15, 2008

I’ve found this useful when working on files on a department shared drive. I link it to a custom button on my toolbar, so I can just click the button and copy the full filename to the clipboard.
Sub copyFilePath()
‘ Sub copyFilePath
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Source:
‘ http://www.cpearson.com/excel/clipboar.htm
‘ Copies path [...]

Posted in Excel Comments No Comments »