tag:blogger.com,1999:blog-32906024236101027652024-02-19T11:38:47.068+00:00Office for Lazy PeopleA blog for lazy Microsoft Office users (mainly Outlook and Excel, with some Google and iPhone apps thrown in the mix). We'll talk about Office automation from an end-user perspective.
We believe you can save a lot of time on repetitive tasks, and concentrate on what you do best: being yourself.RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-3290602423610102765.post-76677500625276015582010-09-06T12:46:00.000+01:002010-09-06T12:46:38.862+01:00Using PowerPivot to find out more about you<span class="Apple-style-span" style="font-family: inherit;">Here are two fun tutorials you can follow that think outside the box about potential real world PowerPivot applications:</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">On Kasper de Jonge's blog (the best resource for PowerPivot information, IMO): </span><a href="http://www.powerpivotblog.nl/project-gain-insight-into-your-music-taste-using-last-fm-powerpivot-and-the-pivotviewer"><span class="Apple-style-span" style="font-family: inherit;">Gain insight into your music taste using Last.fm, PowerPivot and the PivotViewer</span></a><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">On Chris Webb's BI Blog: </span><a href="http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!7670.entry"><span class="Apple-style-span" style="font-family: inherit;">Using PowerPivot to Analyse Windows Search Results</span></a>RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com1tag:blogger.com,1999:blog-3290602423610102765.post-55800797838783638232010-09-02T17:08:00.000+01:002010-09-02T17:08:46.191+01:00TipIf you're working with PowerPivots, don't create relationships on Calculated Columns. I just learned that today, the hard way...RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com0tag:blogger.com,1999:blog-3290602423610102765.post-31907967451046476422010-08-15T17:17:00.000+01:002010-08-15T17:17:40.789+01:00PowerPivot - An IntroductionChances are you already heard about Excel 2010's main new feature: PowerPivots. Having spent a few days reading about and using PowerPivots, I'm starting to get the hang of it.<br />
<br />
If you're a little confused, or just want to know more, this article is for you.<br />
<br />
<br />
<a name='more'></a><br />
<br />
<b>What are PowerPivots for Excel?</b><br />
<br />
Basically, it's a new way to manage and analyze data in Excel. It's main features are:<br />
<ul><li>in-memory processing of large amounts of data (forget the old limit of 65k rows - think millions instead)</li>
<li>import disparate data and create relationships between the tables and columns to join data from different sources (no need for <i>vlookup </i>formulas anymore)</li>
<li>a powerful new expression language (DAX) for calculated fields</li>
<li>integration with visualization tools within Excel, including Slicers</li>
<li>no need for IT assistance (big one for business users like me)</li>
</ul><b><br />
</b><br />
<b>What does that mean?</b><br />
<br />
It means that you will be able to handle much more data than before.<br />
It also means that you can, for the first time, connect different tables (from different sources) using a common column.<br />
<br />
<br />
<b>So?</b><br />
<br />
That's huge! Imagine you have a Sales table, which you get from a corporate database, and a Targets table, which you maintain in Excel (oh, what a familiar feeling...)<br />
<br />
Both of them have a "Sales Representative" column. With PowerPivot, you can create a report using a single PivotTable that compares actual sales with the sales target without using formulas.<br />
<br />
You're effectively modelling a database within Excel!<br />
<br />
<br />
<b>That means I don't need databases anymore! I can fire half of the IT department!</b><br />
<br />
Hold on!<br />
<br />
Excel should be used to analyze data and design reports, not to store data, and not to serve as the standard reporting. Databases are reliable, Spreadsheets are volatile. You need both to handle your information needs.<br />
<br />
<br />
<b>Is that all there is to it?</b><br />
<br />
No.<br />
<br />
PowerPivot is a crucial part of Microsoft's BI strategy and integrates with its main products: SQL Server, SharePoint, and Excel. The main advantage of this integration is the possibility of publishing and sharing your PowerPivot. I'm not going to go into that right now, because we're an Office focused blog.<br />
<br />
<br />
<b>I'm tired of reading. I want to try it!</b><br />
<br />
PowerPivot is a free plugin for Excel 2010. Download it <a href="http://powerpivot.com/download.aspx">here</a>.<br />
<br />
Let's try a really simple thing to get you started.<br />
<br />
<i>Step 1 - Data:</i> Create the following tables in Excel:<br />
<div><br />
</div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVtP9iThpwJ0Y9olsTC1XkgXiAnS0zFz323pnVvbzN2sIJhtD_ZiKXM_ytYV7SO6X4_MqsCpbTX4E1R88Zq28Aw4_N8oJXOgFsrKr6l3rWMaSjHNSNq-2JliTtosulcC2_3rIRoXRijZ84/s1600/pp-step1.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhVtP9iThpwJ0Y9olsTC1XkgXiAnS0zFz323pnVvbzN2sIJhtD_ZiKXM_ytYV7SO6X4_MqsCpbTX4E1R88Zq28Aw4_N8oJXOgFsrKr6l3rWMaSjHNSNq-2JliTtosulcC2_3rIRoXRijZ84/s400/pp-step1.GIF" width="400" /></a></div></div><br />
<i>Step 2 - Adding the data to PowerPivot:</i> Select the first table, and then click "Create Linked Table" on the PowerPivot tab.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcigCjAIKu9Qneq168oqsiW6SknGozw5Vc2LG9n6DBdBtBxDcxB5lnZeVlMCOnqQZRIn59ZKN-kCq6on5Y7ZuGpR4jhTDqJYbSr1p0Uj1K80027W0OEfbQXO-PLXEleoEBeCL5xrbTk6ue/s1600/pp-step2.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjcigCjAIKu9Qneq168oqsiW6SknGozw5Vc2LG9n6DBdBtBxDcxB5lnZeVlMCOnqQZRIn59ZKN-kCq6on5Y7ZuGpR4jhTDqJYbSr1p0Uj1K80027W0OEfbQXO-PLXEleoEBeCL5xrbTk6ue/s400/pp-step2.GIF" width="400" /></a></div><br />
Do the same for the other table.<br />
<br />
<i>Step 3 - PowerPivot configuration</i>:<br />
<br />
Go to the PowerPivot Window. Rename the tables "Sales" and "Countries" by double clicking on "Table1" and "Table2" (bottom of the PowerPivot Window).<br />
<br />
Then create a relationship between the tables by clicking on the appropriately named "Create Relationship" icon in the Design tab.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuvBj-0oL9ThUzrGpbv0__C-joWAzoiBSQiI7MAl0k_tc7BnCRDxiikNrUXk34IDGiRryneRPAFhg0383SWKQZJXpsUTkRSngRAGQUrxa1aS_fxSHNf6GhlJ-lrnpQBS-QbzwYW1UKv638/s1600/pp-step3.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="206" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuvBj-0oL9ThUzrGpbv0__C-joWAzoiBSQiI7MAl0k_tc7BnCRDxiikNrUXk34IDGiRryneRPAFhg0383SWKQZJXpsUTkRSngRAGQUrxa1aS_fxSHNf6GhlJ-lrnpQBS-QbzwYW1UKv638/s400/pp-step3.GIF" width="400" /></a></div><br />
What you want is to link the sales data to the corresponding country. Select "Sales" as the first table and "Country" as the column. Then "Countries" as the second table and "Country" as the Related Lookup Column.<br />
<br />
Note: one of your tables should have a column with unique values, and that column should be the Related Lookup Column. You want each row of your sales table to lookup the country information.<br />
<br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><i>Step 4 - Create a Report</i>: It's as easy as a PivotTable. Just click the PivotTable icon on the Home tab of the PowerPivot Window.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">The first thing you'll notice is that the field list now contains a tree. The fields appear within your tables, which makes navigating a lot of field much more easy - another advantage of PowerPivot.</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFhqZ2svXAF8O8DQphjNwj5eIPskweLFnaZz2qddPiVyeLCQ69MYTI8CuL4uKJN5eMKrafdEvl0Zp7ex9JaG1CuIFU_AbBPgq4KfhlSqVKTeFg4zLfAyYeyTqPtgsbTT9-pQJA36GoxmCw/s1600/pp-step4.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="291" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFhqZ2svXAF8O8DQphjNwj5eIPskweLFnaZz2qddPiVyeLCQ69MYTI8CuL4uKJN5eMKrafdEvl0Zp7ex9JaG1CuIFU_AbBPgq4KfhlSqVKTeFg4zLfAyYeyTqPtgsbTT9-pQJA36GoxmCw/s400/pp-step4.GIF" width="400" /></a></div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div>As you see, I can now filter the sales data by Region, even though there is no region information on the Sales table - just country.</div><br />
<b>Conclusion</b><br />
<br />
The way I see it, there's not much you can do with PowerPivots that you couldn't before, using LookUp formulas and calculated fields. But you can do it faster and more easily. Which is what we lazy people like.<br />
<br />
Besides, DAX opens a lot of opportunities, and we'll be sure to talk more about that in the future.RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com0tag:blogger.com,1999:blog-3290602423610102765.post-80296271816179970982010-08-15T12:09:00.001+01:002010-08-15T12:10:12.368+01:00Visual Basic's Basics #1The target audience for this blog is not computer experts, but normal 21st century office works that use computers for their day to day needs, and are looking to increase their productivity through automation. I’m not an expert myself, so what I write may not be the most accurate or efficient way to do things. I just want it to work, and that’s you want too. So, on to the lesson.<br />
<br />
<a name='more'></a><br />
The best way to learn about VBA is to record Macros and then look at the automatically generated code. Even if you don't know how to code, you will start to understand how everything works and suddenly you will be confident enough to try to change some lines.<br />
<br />
Unfortunately, Outlook doesn't have a native Macro recording feature, so start with Excel.<br />
<br />
Note: I'm going to assume you are using Office 2007 or Office 2010, because, really, you shouldn't be using 2003 anymore.<br />
<br />
<b>Step 1:</b><br />
<br />
If you don't have it yet, add the Developer tab to your Ribbon. Go to Excel Options.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ3uVtLADOvvwRDfN9VUnXsVJGC4oytRdopeqTc8a-d14ztmHOFfhyY0Rhruo2E5AmeykTuhTF-8-UJW3Wf1SIcl7nbJirLNN-fdL5yFx9m4mLlEIAv4BKmHtBR120XTNBsnvT-JpMqbAg/s1600/step1.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="322" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ3uVtLADOvvwRDfN9VUnXsVJGC4oytRdopeqTc8a-d14ztmHOFfhyY0Rhruo2E5AmeykTuhTF-8-UJW3Wf1SIcl7nbJirLNN-fdL5yFx9m4mLlEIAv4BKmHtBR120XTNBsnvT-JpMqbAg/s400/step1.GIF" width="400" /></a></div><br />
<br />
<b>Step 2:</b><br />
<br />
Go to the developer tab and select “Record Macro”. Enter something in Cell B2 and select “Stop Recording”<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_8ONwzREb0OQ3h7B7YkbuXK3tUPFaa8VAooIzVUWSgzDwRHwAlOk0e6awFoe3WyMQ134T5frl2ONn2EdppQ2ou6KdS6XLSoaB7Zv0YT6s4L0YaQmS2YwhXHIVjf0bOHedcydpXSkIlRdC/s1600/step2.GIF" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_8ONwzREb0OQ3h7B7YkbuXK3tUPFaa8VAooIzVUWSgzDwRHwAlOk0e6awFoe3WyMQ134T5frl2ONn2EdppQ2ou6KdS6XLSoaB7Zv0YT6s4L0YaQmS2YwhXHIVjf0bOHedcydpXSkIlRdC/s320/step2.GIF" /></a></div><br />
<br />
<b>Step 3:</b><br />
<br />
To look at the code, select “Macros” and then “Edit”. This will show up:<br />
<br />
<blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>Sub Macro1()</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>'</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>' Macro1 Macro</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>'</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i><br />
</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>'</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>Range("B2").Select</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>ActiveCell.FormulaR1C1 = "something"</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>Range("B3").Select</i></span></blockquote><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><i>End Sub</i></span></blockquote><br />
As you can see, it is quite easy to adapt this code. Instead of "something", you wanted enter "some other thing"? No need to record the macro all over again, just edit the text!<br />
<br />
Some explanations:<br />
<br />
-“Sub” is used to define and name the macro. “End Sub” is used to, you guessed it, end the macro.<br />
<br />
-Text after ‘ is a comment. That means you can write whatever you want, except code. You should use comments to remind yourself (and others that may look at the code in the future) what it is supposed to do. For instance, <a href="http://officeforlazy.blogspot.com/2010/05/random-task-in-outlook.html">in my previous post</a>, I wrote:<br />
<blockquote>'Selects incomplete tasks from your default tasks folder</blockquote><br />
You’ll find out pretty soon that writing clear comments is a good practice. Otherwise you will feel lost when you look at old code, even if it is your own.<br />
<br />
-Things in VBA are “Objects”. A menu is an object, a cell is an object, a sheet is an object, a workbook is an object. In this case, <i>Range(“B2”)</i> and <i>ActiveCell </i>are objects. There are many types of objects, and each type is associated to a set of “Methods” and “Properties”.<br />
<br />
-A Method is an action that can be performed on an object. In this case, the <i>Select </i>Method selects the object <i>Range("B2"</i>).<br />
<br />
-Properties describe objects. You are able to change most properties, while some are read only. In this case, <i>FormulaR1C1 </i>is a property, which we change to "something".<br />
<br />
Both Methods and Properties are used with a specific object, separated by a period (<i>object.method</i> or <i>object.property</i>).<br />
<br />
There some others concepts in VBA programming, like Functions and Variables, which we will talk about in the future, but these are the most important for you to start to understand Visual Basic.RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com0tag:blogger.com,1999:blog-3290602423610102765.post-706820599497020162010-07-26T21:40:00.001+01:002010-08-15T12:09:14.225+01:00Convert Table To List<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;">This is a code I wrote to convert a table (values and headers) to a list, so it can easily be manipulated using pivot tables or loaded intro a database. I posted this two years ago on the excellent Ozgrid Forums, so it makes sense I share it here too. It was tested on Excel 2003, but it should work in 2007/2010. Let me know if it doesn't.</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><a name='more'></a></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;">Imagine you have this table:</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><br />
<blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;"> a b</span></span></blockquote><span class="Apple-style-span" style="color: #333333;"></span><br />
<span class="Apple-style-span" style="color: #333333;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">c 1 2</span></span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-size: 13px;"></span><br />
<span class="Apple-style-span" style="color: #333333; font-size: 13px;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"><span class="Apple-style-span" style="font-size: small;">d 3 4</span></span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;">The result would be this list:</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">1 a c</span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-size: 13px;"></span><br />
<span class="Apple-style-span" style="color: #333333; font-size: 13px;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">2 b c</span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-size: 13px;"></span><br />
<span class="Apple-style-span" style="color: #333333; font-size: 13px;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">3 a d</span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><blockquote><span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">4 b d</span></blockquote></span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;">It support headers with more than one row or column.</span><br />
<div><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;"><br />
</span></span></div><div><span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: small;"><span class="Apple-style-span" style="font-size: 13px;">Here is the code:</span></span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"><br />
</span><br />
<span class="Apple-style-span" style="color: #333333; font-family: Tahoma, Calibri, Verdana, Geneva, sans-serif; font-size: 13px;"></span><br />
<pre class="bbcode_code" style="background-attachment: initial; background-clip: initial; background-color: #f2f6f8; background-image: initial; background-origin: initial; background-position: initial initial; background-repeat: repeat no-repeat; border-bottom-style: inset; border-bottom-width: 1px; border-color: initial; border-left-style: inset; border-left-width: 1px; border-right-style: inset; border-right-width: 1px; border-top-style: inset; border-top-width: 1px; direction: ltr; font-family: monospace; font-size: 12px; height: 636px; line-height: 12px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; overflow-x: scroll; overflow-y: scroll; padding-bottom: 6px; padding-left: 6px; padding-right: 6px; padding-top: 6px; text-align: left;">Public Sub TableToList()
'variables
Dim tabela As Range
Dim cabColunas As Range
Dim cabLinhas As Range
Dim nColunas As Integer
Dim nLinhas As Integer
Dim nCabColunas As Integer
Dim nCabLinhas As Integer
Dim i As Integer
Dim j As Integer
Set tabela = Application.InputBox("Select Table", Type:=8)
Set cabColunas = Application.InputBox("Select Column Labels)", Type:=8)
Set cabLinhas = Application.InputBox("Select Row Labels", Type:=8)
nColunas = cabColunas.Columns.Count
nLinhas = cabLinhas.Rows.Count
nCabColunas = cabColunas.Rows.Count
nCabLinhas = cabLinhas.Columns.Count
'size check
If cabColunas.Columns.Count <> tabela.Columns.Count Then
MsgBox ("Column Header should have the same # of columns the table has")
Exit Sub
End If
If cabLinhas.Rows.Count <> tabela.Rows.Count Then
MsgBox ("Row Header should have the same # of rows the table has")
Exit Sub
End If
'creates sheet
Worksheets.Add
'fills the list
For i = 1 To nColunas * nLinhas
Range("A" & i).Value = tabela(i).Value
For j = 1 To nCabColunas
If i Mod nColunas = 0 Then
Cells(i, j + 1).Value = cabColunas(nColunas * j)
Else: Cells(i, j + 1).Value = cabColunas(i Mod nColunas + nColunas * (j - 1))
End If
Next
For j = 1 To nCabLinhas
Cells(i, j + 1 + nCabColunas).Value = cabLinhas(Int((i - 1) / nColunas) * nCabLinhas + j)
Next
Next
End Sub</pre></div>RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com1tag:blogger.com,1999:blog-3290602423610102765.post-16227707337146630502010-06-15T12:01:00.000+01:002010-06-15T12:01:08.680+01:00Google Calendar Sync with Outlook 2010For everybody that uses Outlook and Google Calendar, there's an <a href="http://www.google.com/support/calendar/bin/answer.py?answer=98563">official syncing tool</a> made by Google which you should already use. Unfortunately, it doesn't work with Outlook 2010. Fortunately, there's an unofficial fix which you download <a href="http://mychannellogos.com/Documents/GoogleCalendarSync.zip">here</a> (supposedly, it's a Google private beta version).<br />
<br />
This is not an official patch, so <b>use it at your own risk</b>.<br />
<div><br />
</div><div>If you are unsure about using an external application and are a little tech savvy, find the manual instructions <a href="http://www.google.com/support/calendar/bin/answer.py?answer=98563">here</a> (you need to use an hex editor).</div>RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com0tag:blogger.com,1999:blog-3290602423610102765.post-52646446414145674552010-05-21T10:42:00.001+01:002010-08-15T12:10:39.376+01:00Random Task in OutlookSo maybe you have 20 minutes available and you want to do something, but you're not sure what. Or you have a tendency to delay the tasks you don't like to do. I find that leaving some decisions to luck is sometimes the best solution.<br />
<br />
This VBA code will make Outlook select a random task from you to-do list, and then based on your feedback give you positive or negative reinforcements. In this case, if you do the task it shows a simple message and marks the task as complete, while not doing it displays a random post from <a href="http://failblog.org/">Fail Blog</a>. It was tested with Outlook 2010, but should work with any Outlook version that supports RSS feeds.<br />
<br />
Please note that I will not be held responsible for any loss of productivity as a result of navigating Fail Blog!<br />
<br />
<br />
<a name='more'></a><br />
<br />
Code:<br />
<blockquote><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">Sub PickRandomTask()</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Generic stuff necessary for Outlook Macros.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objOutlook = CreateObject("Outlook.Application")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objNamespace = objOutlook.GetNamespace("MAPI")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Selects incomplete tasks from your default tasks folder</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objFolder = objNamespace.GetDefaultFolder(olFolderTasks)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set colItems = objFolder.Items</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> strFilter = "[Complete] = FALSE"</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set coltasks = colItems.Restrict(strFilter)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Selects posts from an RSS Feed to display when a task is not completed.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Note: You have to previously add the RSS Feed to your Outlook.</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objFolder2 = objNamespace.Folders.Item("Personal Folders")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objFolder1 = objFolder2.Folders.Item("RSS Feeds")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set objFail = objFolder1.Folders.Item("FAIL Blog: Epic Fail Funny Pictures and Funny Videos of Owned, Pwned and Fail Moments")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Set FailPosts = objFail.Items</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'variables</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Dim n As Integer</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Dim f As Integer</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Picks a random task for the user, and asks for feedback on task completion</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> n = Int((coltasks.count) * Rnd + 1)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Answer = MsgBox("Do this task: " & coltasks(n).Subject & vbCrLf & vbCrLf & "RIGHT NOW!", vbQuestion + vbYesNo, "???")</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> If Answer = vbNo Then</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Code for No</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> f = Int((FailPosts.count) * Rnd + 1)</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MsgBox "EPIC FAIL!"</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> FailPosts(f).Display</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> Else</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> 'Code for Yes</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> MsgBox "You are my hero!"</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> coltasks(n).MarkComplete</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> End If</span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;"> </span><br />
<span class="Apple-style-span" style="font-family: 'Courier New', Courier, monospace;">End Sub</span></blockquote><br />
This feature w<span class="Apple-style-span" style="font-family: inherit;">as inspired by </span><span class="Apple-style-span" style="border-collapse: collapse;"><span class="Apple-style-span" style="font-family: inherit;"> </span><a href="http://todoneapp.com/do" style="color: #2244bb;" target="_blank"><span class="Apple-style-span" style="font-family: inherit;">To -> done</span></a><span class="Apple-style-span" style="font-family: inherit;"> (a task management website based on randomizing the next task).</span></span><br />
<br />
<span class="Apple-style-span" style="border-collapse: collapse;"><span class="Apple-style-span" style="font-family: inherit;"><b>EDIT:</b> Lifehacker (my personal Bible) linked to this post, added some instructions and removed the RSS part of the cripts (yeah, I know, it won't be very productive...). Check it out <a href="http://lifehacker.com/5544487/make-outlook-assign-you-random-to+do-items">here.</a></span></span><br />
<br />
<span class="Apple-style-span" style="border-collapse: collapse;"><span class="Apple-style-span" style="font-family: inherit;">I'll dedicate a few posts to VBA basics in the following days. If you're confused about how to use this stay tuned!</span></span>RJThttp://www.blogger.com/profile/11409465236810409281noreply@blogger.com4