September 2, 2010
1 comment »
We interrupt today's blog post to bring you this announcement. Yesterday, John Walkenbach announced that this will be his last month as a Microsoft Excel MVP.
If you don't know who John Walkenbach is, he's the guy with the hat on all your Excel books.
Â
Personally, I'm disappointed that he won't be going to any more of the Microsoft MVP summits. I was at the last Summit he attended and I almost had a heart-attack from all the laughing we did.
I don't know many of the Microsoft MVPs – but he's one of the few I really like.
Â
In any case, I don't think this means he's dying or going senile. But if he is, I've got dibs on replacing him on the Excel Bible series.
Â
To learn more about what makes John Walkenbach tick, check out my heart-warming interview with him.
August 31, 2010
4 comments »
One of the more common questions I get revolves around running an Excel macro from Access. That is, how do you fire an existing Excel macro from Access.
I'll share the code to do that in a moment.
I first want to point out that this code can also come in handy if you need to run an external Excel macro in another workbook. That is to say, while you are working in an Excel file, you can reach out and run a macro in another workbook.
Â
OK. Here it is. Simply paste this code in a new module.
Â
Sub RunExcelMacro
()
Dim xl
As Object
'Step 1: Start Excel, then open the target workbook.
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open ("C:\Book1.xlsm")
'Step 2: Make Excel visible
xl.Visible = True
'Step 3: Run the target macro
xl.Run "MyMacro"
'Step 4: Close and save the workbook, then close Excel
xl.ActiveWorkbook.Close (True)
xl.Quit
'Step 5: Memory Clean up.
Set xl = Nothing
End Sub
Â
Some notes on the code:
Step 1: Create a new instance of Excel, open the target workbook; the workbook that contains the macro you need to run.
Â
Step 2: Making Excel Visible. By default, Excel will open and run in the background (invisible to you). You can set the Visible property to TRUE if you need to see the workbook. If you don't want to see the Excel workbook while the macro is running, simply set the Visible property to FALSE.
Â
Step 3: Run the target macro
Â
Step 4: Close and save the target workbook. The True argument in xlwkbk.Close(True) indicates that you want the workbook saved after the macro has run. If you do not want to save the target workbook, change this argument to False. Also in Step 4, you quit the Excel application, effectively closing the instance of Excel.
Â
Step 5: Release the objects assigned to your variables, reducing the chance of any problems caused by rogue objects that may remain open in memory.
August 28, 2010
1 comment »
On this blog, Saturday is Bacon recipe day. So no Excel tips and tricks today. Today we learn about bacon.
Â
It's morning here in Dallas and I'm thinking about making some Bacon Oatmeal.
This is sure to give me the calories I need to face a day of sitting in front of the television.
» Read more: Bacon Oatmeal
August 26, 2010
4 comments »
Here's a tip for all you cool dudes who like to wear your security badges on your belt.
Take your badge off your belt before you have a go at the toilet.Â
Everyone can see exactly who's releasing the toxic fumes.Â
» Read more: Workplace Bathroom Tip
5 comments »
I was reviewing someone's workbook the other day way I saw some code similar to this:
Sheets("Sheet1").Unprotect Password:="MeatFace"
PROCEDURE
Sheets("Sheet1).Protect Password:=" MeatFace "
This code unprotects the sheet, runs a procedure, and then protects the sheet again.  The reason this guy wrapped his procedure with Unprotect/Protect is because his procedure changes a worksheet in some way. And you cannot run a macro that changes a worksheet while that worksheet is protected.
 » Read more: Worksheet Protection Best Practice
August 24, 2010
2 comments »
Sandra asks:
"Any way to add a rank to a pivot table"?
DataPig Answers:
In Excel 2010, it's really easy. In Excel 2007/2003, it's a bit clunky.
 » Read more: Add Rank to PivotTable
August 22, 2010
4 comments »
On this blog, Saturday is Bacon recipe day. So no Excel tips and tricks today. No, today we learn about bacon.
Â
Today's recipe is Bacon Wrapped Fried Macaroni and Cheese – a.k.a "the beautiful gut bomb".
» Read more: Bacon Wrapped Fried Mac and Cheese
August 20, 2010
5 comments »
Every now and then, one of my 12 fans asks me about the Saturday Bacon Recipes I used to post here. You may have noticed I've stopped posting them. Why? Mostly because of the book schedules I was committed to. Now that I'm done with books, I can start the recipes back up again.
A few loyal Bacon-ites have been sending recipes my way, so this Saturday, the Bacon Love resumes.
To get in the spirit, I'll share this educational link that Chris sent me. Thanks Chris, I've learn so much from this nifty graphic. But I do believe they got a few things wrong.
» Read more: Some Bacon Love
August 16, 2010
2 comments »
Wendy writes to ask:
"In Excel, I can use the PROPER function to change my text to proper case. Why is there is no PROPER function in Access? "
Wendy has stumbled on one of the mysteries of life - there are many functions you use in Excel that are not found in Access. Well…it's not really a mystery. I actually have an idea why this may be.
 » Read more: Converting Text to Proper Case in Access
August 13, 2010
1 comment »
When I started this blog over a year ago, I knew that 'Spam Comments' would be an issue. These are comments inserted into posts by web crawlers - typically containing links to sites trying to sell something.
Â
» Read more: A Sampling of Spam
