Simple Tweeter - A simple Excel based Twitterbot

After playing with Excel for some time now I am pretty much safe in saying that you can do just about anything with Excel. It isn't just for number crunching and creating graphs. To that end I thought about creating a TwitterBot using Excel just because I can. I've seen them in many other computing languages but not in VBA. However I did find this rather splendid post on just that. It all looks pretty complex and granted it is much more long winded than in some languages (I built something similar in Ruby not so long ago and that was quite 'slimline' in comparison) but it does the same thing and is pretty useful.

Having adapted the code so that it will pick up tweets from a sheet. It simply runs in the background tweeting at intervals of one hour from the point of starting. Once you finish the list, it'll start again.

It'll even check your tweet length to make sure you stick to 140 characters.

Content delivery without having to sit by the computer and do it manually and all in Excel. What could be simpler!

If you would like to have a go just click here and download the file.

It works in Excel 2007 and newer but is untested in previous versions.

All that needs to be done then is enable Macros on opening the file and follow the simple instructions contained on the first page about authorising the application with your Twitter account.

Once this is complete it doesn't need to be repeated.

Add Tweets using the add tweet function and hit start to start tweeting. The application will continue until you stop it or you turn off the power!

VBA Print Macro

I thought it was about time I went all geeky and actually did a bit of coding on this blog of mine so here we go! In the last workbook I worked on I needed to create a macro that would print a specific range each time and shrink that table to fit onto one page i.e. it was too wide.


This is fairly simply only that the sheet could be filtered and therefore data hidden. Excel can get a bit twitchy about things like that.

To Start...

First thing is we need to define a function. Put this in a module within the IDE of Excel. If you don't know where that is open the Developer tab on the ribbon and click Visual Basic. I always have a module defined as Functions so I can easily find any of them if tinkering is needed.


Next you need to know how long the range is. You can do this by using the below lines of code.

4:   With shtName  
5:     lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
       .....
18:  End With  

This line of code is a little life saver and is generally peppered throughout any automation project as finding the last row of data is generally rather important!


"shtName" is interchangeable for any name you want as long as it corresponds to the name of the worksheet you are fiddling with as is "C". Simply replace this with the relevant column in your case.


If you protect your worksheets then the line 6 on the completed listing below is needed as it can't calculate the range without removing that. If not you can remove the line as required.

Actual Printing...

The next section is the print set up and negates the need to show a preview, especially useful if you have hidden the ribbon to prevent unruly users playing with things they shouldn't be!

7:  With .PageSetup  
8:     .Orientation = xlLandscape  
9:     .CenterHorizontally = True  
10:     .FitToPagesTall = 1  
11:     .FitToPagesWide = 1  
12:     .Zoom = 75  
13:     .PrintArea = Range("C6:N" & lastRow).SpecialCells(xlCellTypeVisible).address  
14:   End With  

This section will change the printed page to landscape and centre the table alongside fitting it to the page in both height and width.


I set the zoom to 75% as this is what worked for my table, you can change this to whatever figure works best for you.


Setting the print area is basically the range you want to print. The first cell in my case being "C6" and the last in the predefined lastRow variable. We know the last column is "N" but we have no idea how long that column is hence the use of the lastRow variable.


Tagging on .SpecialCells(xlCellTypeVisible).address makes sure that if the data is filtered then only the visible cells are printed and Excel doesn't have a go at shoving the hidden data back into the table.


At this point you are ready to print and as there is no need of the preview button

15:  Application.Dialogs(xlDialogPrint).Show  

will show the print dialog box in Excel and allow you to change some of the printer settings but not the set up of the page.


If you wanted to see a print preview then simply substituting the line:

15.5:  .PrintOut preview:=True  

for the above would allow that. However if you have hidden the ribbon you can't do anything with the preview as the print button is also hidden.

Tidying up...

One thing I always found is that Excel leaves behind dotted lines in the print area showing how that range fits on the page. In an application this is unsightly and I'm not keen to be honest so I add the next line to satisfy the OCD and remove them.

16:  .DisplayPageBreaks = False  

Then if needs be simply lock the page remembering to allow for filtering again and get back to viewing your data.

Add this code to a button or within another procedure and you're good to go. You can also adapt it so that the range to print is added dynamically giving you more flexibility.

The Finished Article...

1:  Public Function printRange()  
2:    Dim lastRow As Long
3:      
4:    With shtName  
5:      lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row      
6:      .Unprotect "pwd"  
7:      With .PageSetup  
8:        .Orientation = xlLandscape  
9:        .CenterHorizontally = True  
10:        .FitToPagesTall = 1  
11:        .FitToPagesWide = 1  
12:        .Zoom = 75  
13:        .PrintArea = Range("C6:N" & lastRow).SpecialCells(xlCellTypeVisible).address  
14:      End With  
15:      Application.Dialogs(xlDialogPrint).Show  
16:      .DisplayPageBreaks = False  
17:      .Protect "pwd", AllowFiltering:=True  
18:    End With  
19:  End Function  

The Excel Clinic

Excel it's insanely powerful but also drives you insane.

Have you got an issue you just can't get round, a small grumble with the sheet or workbook you're using?

Whatever that may be contact The Excel Whisperer and have all your problems solved (Excel related obviously, I'm not that good!!)

Tag your issue #ExcelClinic on Twitter or Facebook or add it to the comments right here and I'll try and solve it as quickly and simply as I can.

Data, data everywhere...

Not that kind of data but finally the excuse to post an image of Data from Star Trek TNG I've been looking for, it's tenuous I know but it's that or a large picture of a graph!


Moving swiftly on....if you're a business then like it or not you are producing data of some kind. That could be in the form of sales data, job data, income data, customer data or just data because you've been on-line. I'm producing data simply by writing now.

Each piece of data needs to be managed in some way so that you can find it, use it and manipulate it to your advantage. This is how you get ahead, it's worked pretty well for Google! Manipulating your data may show you where your strengths lie, equally it'll show you were you're weaknesses are and these are the things that could be costing you and your business money.

Data manipulation is essential to any business as you need to know where you may lose money or where too much of your most precious resource is going, your time. Badly managed data can inhibit you significantly therefore you need a solution to allow the growth you want and need. Knowing which products sell the best or which give the highest profit margin is essential but also knowing who is the more productive in your team is of equal importance. On the flip side knowing which products are simply not bringing in the cash and being able to pinpoint them easily is a great way of saving money.


This isn't always available in an off the shelf product as it just doesn't have everything you want in it and trying to shoehorn your information in something that doesn't quite cut it is beyond frustrating.. That's where a bespoke solution comes in, something tailored around you, something encapsulating your personal processes to show your data the correct way but also in the best, most truthful way. There are myriad ways to achieve this and no two ways will be alike. Hopefully it won't include a garishly coloured, endless columned spreadsheet!

So how do you go about collecting your data? Well you could use the old fashioned way of paper and a pencil and that works. I have extensive notes all over the place for a lot of things and processes but that's the point....all over the place. This kind of thing needs to be accessible and in one place with a system of access that's simple and easy.


What digital methods can you use to collect your data?

A database?

If you have Office you most likely have Access. This can provide you with a simple enough means to store and access your data but not process it. You may well end up with an inefficient and unruly set of tables or table depending on your knowledge of relational database structures and SQL. You could try out a NoSQL database if you fancy getting a little off the usual path such as MongoDB that would certainly help in the unruly stakes mentioned before.

Excel?

I've seen many variations on the spreadsheet, some of which have made me nauseous with the colourful columns and charts but if it works for you then it simply works. Personally I have a bias towards Excel as programming within it and automating it are my 'thing' unlocking the full power of the application. A simple solution to crunch your numbers and generate charts is easily created without the need for cumbersome formulae and you may not even realise you're using Excel by the end of it! Plus you probably already have the program at hand and have no need to invest extra cash, something I will be covering in a later post.

Open source programs?

There are many weird and wonderful variations of common programs that are rebuilt as free and community maintained applications. Often they can be better than the commercial applications. For instance I use GIMP for photo editing rather than Photoshop as it's free, considerably easier to use (I think) and better (again I think) but mostly it's free. OpenOffice will give you all the resources and abilities Microsoft Office will but without the cost. Have a look see here if you fancy trying it out.

'Cloud based'?

The new set of buzz words in any industry in recent times. Trumpeted as better but really just obfuscation of the fact that your data rests on other people's servers. That's fine if you want it that way of course. A simple Google search will show a myriad of ways and resources to do simple things all without glancing up from your phone at all, such as this one.



So, how can your data collection and manipulation be achieved? Call me old-fashioned but my personal solution would be a friendly, honest discussion with someone you trust to allow into your business and learn it. Only by learning it can the right solution be found with simple, easy advice. It may not be a short process but it could reap long term benefits.

Simpler Computing, an introduction...

Having read a great deal around the subject of marketing within the 'modern age' it seemed like it was high time for me to start a blog. Whether this be to impart a tip about a little tweak to an Excel sheet, my ideas around a particular bug in Excel or in answer to any questions that may be asked of me. I figured this needed to be done in a format that is capable of prose longer than Twitter can provide. Also I like to put a human side to my work as I'm not very good at serious and corporate!

So *waves* I'm a human that likes to tinker with spreadsheets. Now that's something you don't hear very often but seeing as I have no hair to tear out when Excel provides me with that day's frustrations then I can remain fairly calm...

The idea behind all this is indeed a simple one, hence the name, I remain frustrated to this day by individuals and things that seemingly exist to obfuscate processes or add in extra superfluous layers for whatever reason. I aim to remove these and take things back to how it should be......simple. This is why I like coding. The process may be complex but the user doesn't care about that, they want it to be simple to use and understand. I want it to be simple as well as that's just easier!

My main method of achieving this is Excel. Excel can be one of the most frustrating, infuriating programs going but it is also highly useful and very powerful when used to its fullest. In the past I have used Excel to generate data from databases, web pages, other Excel sheets. I've used it as a makeshift web server, a means to shut down other programs and servers (I must admit to this one being by accident and probably annoyed a lot of people but it made me laugh when I realised what it had done), generate XML, JSON and create and plot maps. Oddly I've also used it to crunch numbers too! This is one powerful and overlooked tool and one pretty much everyone has access to at some point. I'll be pulling on this thread further in a later post.

Hopefully you'll find these witterings helpful or maybe even interesting at some point, who knows, but if you have any questions or points on the subject at hand then drop me a comment, or, if you fancy, I have a number of links on the side to contact me directly through my own website, Twitter or Facebook.