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