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 copy of a workbook’s values for each workbook
Sub copyWorkbook()
Dim sourceWB As Workbook, destWB As Workbook, sourceWS As Worksheet, destWS As Worksheet
Set destWB = Workbooks(”Destination.xls”)
Set sourceWB = Workbooks(”Workbook_that_has_the_data_you_want.xls”)
For Each destWS In destWB.Worksheets ‘I like to keep one workbook in there, just to avoid problems
If destWS.Name <> “__BLANK__” Then
destWS.Delete
End If
Next destWS
‘Here’s the important stuff
For Each sourceWS In sourceWB.Worksheets
destWB.Worksheets.Add().Name = sourceWS.Name
sourceWS.Range(”A:IV”).Copy (destWB.Worksheets(sourceWS.Name).Range(”A1″))
Next sourceWS
End Sub
Posted in Excel 
No Comments »
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 Selection.Font
.Name = “Arial”
.FontStyle = “Regular”
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Next wSheet
Posted in Excel No Comments »
March 20, 2008
Here’s an interesting article (with links to underlying studies) from Knowledge@Wharton summarizing two studies by Gavin Cassar. In one, he observes that (unsuprisingly) ”internal accounting report preparation significantly improves forecast accuracy“. The catch is: “the accuracy benefits from internal reports preparation are only observed for firms with high uncertainty” in forecasting.
In his second study, he shows how some of these same tools can lead entrepreneurs to adopt overly optimistic outlooks for their company’s prospects. I wonder if these entrepreneurs are using sensitivity and risk analysis to vet their forecasts, or are they getting a number they like, and calling it quits?
Posted in Articles, Business No Comments »
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
‘ Source: http://www.j-walk.com/ss/excel/tips/tip82.htm
‘ Retrieves a value from a closed workbook
Dim arg As String’ Make sure the file exists
If Right(path, 1) <> “\” Then path = path & “\”
If Dir(path & file) = “” Then
GetValue = “File Not Found”
Exit Function
End If
‘ Create the argument
arg = “‘” & path & “[” & file & “]” & sheet & “‘!” & _
range(ref).range(”A1″).Address(, , xlR1C1)
‘ Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function
Posted in Excel No Comments »
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 Object Library! (see code)
Sub CreateNewMail(strRecipients As String, strCCs As String, strSubject As String, strBody As String, Optional strAttachPath As String)
‘
‘ Sub CreateNewMail
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ Source:
‘ http://www.sitepoint.com/forums/showthread.php?t=406443
‘ Creates email message to send report
‘ ToDo:
‘ Check for and load Outlook object library
‘In Visual Basic click on Tools, References and tick Microsoft Outlook 11 Object Library
Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim myRecipient As Outlook.Recipient
Dim myCCRecipient As Outlook.Recipient
Dim myBCCRecipient As Outlook.Recipient
Set myOlApp = CreateObject(”Outlook.Application”)
Set myItem = myOlApp.CreateItem(olMailItem)
Set myRecipient = myItem.Recipients.Add(strRecipients)
Set myCCRecipient = myItem.Recipients.Add(strCCs)
myCCRecipient.Type = olCC
myItem.Subject = strSubject
myItem.HTMLBody = strBody ‘If you don’t want to use HTML Body, comment this line and uncomment the next
‘myItem.Body = strBody ‘If you would rather use HTML Body, comment this line and uncomment the previous
If (strAttachPath <> “”) Then
Set myAttachments = myItem.Attachments
myAttachments.Add strAttachPath
End If
myItem.Display
End Sub
Posted in Excel No Comments »
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 No Comments »
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 No Comments »
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 THERE ARE NO BLANK CELLS IN COLUMN A!! If there are, you need to find another column with no blank cells, or insert one. Then just change the reference in the first “COUNTA()” to that column.
Posted in Excel No Comments »
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 couple I have found helpful:
Public Function RegMatch(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Boolean
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘This function returns whether or not the Source string contains the pattern
‘ Modified from http://www.dailydoseofexcel.com/archives/2005/08/13/pattern-matching/
Dim reg As New RegExp
reg.IgnoreCase = IgnoreCase
reg.MultiLine = MultiLine
reg.Pattern = Pattern
RegMatch = reg.Test(Source)
End Function
This one returns the part(s) of the Source string that contain the pattern. Could also be modded for replacement, but I think the link contains examples that already do that.
Function RegExtract(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True, Optional Glbal As Boolean = True) As String
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Modified from http://www.vbaexpress.com/kb/getarticle.php?kb_id=68
‘ For help with regular expressions: http://www.regular-expressions.info/reference.html
‘Dimension the RegExp objects
Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, OutPutStr As String
‘ create the RegExp Object with late binding
Set RegEx = CreateObject(”vbscript.regexp”)
‘ set the RegExp parameters
With RegEx
.IgnoreCase = IgnoreCase
.MultiLine = MultiLine
.Global = Glbal ‘Not sure what this does, don’t care right now
.Pattern = Pattern
End With
OutPutStr = “”
Set RegMatchCollection = RegEx.Execute(Source)
‘Loop through each match in the string and concatenate them
For Each RegMatch In RegMatchCollection
OutPutStr = OutPutStr & RegMatch
Next
‘Put the extracted match into the corresponding B value cell
RegExtract = OutPutStr
End Function
Posted in Excel No Comments »
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 and file name of active workbook to clipboard
‘Requires Microsoft Forms 2.0 Object Library
‘Click on Tools, References and tick Microsoft Forms 2.0 Object Library
Dim MyDataObj As New DataObject
MyDataObj.SetText ActiveWorkbook.Path & “\” & ActiveWorkbook.Name
MyDataObj.PutInClipboard
End Sub
Posted in Excel No Comments »
content rss
