All posts by eric tomlinson

Integrating Google Drive

Most people know my favourite challenge is:

‘It can’t be done.’

So when a client wanted to update a dozen different google drive folders with different data every week, this came straight to my desk.

Google offer some great services, but they make integrating it into a business process a little clunky, or expensive.

Take Maps, for instance. If you want to use them for business there’s a $10k charge per annum. Interestingly, they’ve just dropped fusion tables, a quick way to integrate maps and data. That decision is going to bite somebody somewhere, but I digress.

Getting the data ready, good old Sql Server Reporting Services (SSRS) gave the basis of the data. An elegantly presented price list, in colour saved as a pdf. Setting up a subscription to save to a file share, rather than screen or email allowed me to create a folder of appropriate price lists.
A quick ferret around the SSRS subscription table gave me a clue how to run multiple subscriptions in a single pass.
In general I hate using a CURSOR to loop in SQL, I will almost do anything I can to avoid it. However, this really did seem the easiest way to produce a flexible batch of subscriptions.

declare @subscriptionID as NVARCHAR(50)
declare @mycursor as cursor
set @mycursor = CURSOR for
select SubscriptionID from ReportServer.dbo.Subscriptions where Description like ‘%\pricelist%’;
open @mycursor
fetch next from @mycursor into @subscriptionID
while @@FETCH_STATUS = 0
exec [ReportServer].dbo.AddEvent @EventType=’TimedSubscription’, @EventData=@subscriptionID
fetch next from @mycursor into @subscriptionID

CLOSE @mycursor
DEALLOCATE @mycursor

What I did encounter was a distinct lack of VB.NET examples of how to use the Google API (V3) Not only did not much exist, but the ones that did, appeared to have errors in them (most of them missed out the ‘Execute’). It’s like any API, some are worse than others and once you have worked with it. It starts to hang together and make sense.
Because Google drive does not actually care if files of the same name exist in the same folder, I created a delete function to make sure all copies of a document were deleted.

Private Sub DeleteFile(inFile As String, infolder As String)
Dim parents As New List(Of String)
Dim flist As New List(Of File)

If Service.ApplicationName <> “Google Drive VB Dot Net” Then CreateService()
Dim listRequest As FilesResource.ListRequest = Service.Files.List()
listRequest.Fields = “*”
Dim files As FileList = listRequest.Execute()
For Each myFile As File In files.Files
If myFile.Name = inFile Then
Dim plist As New List(Of String)
plist = myFile.Parents
If plist IsNot Nothing Then
If plist.Count > 0 Then

For Each par As String In plist
If par = infolder Then
Dim delme As FilesResource.DeleteRequest = Service.Files.Delete(myFile.Id)
End If

End If
End If

End If
End Sub

I did find the parent list an odd feature. Google drive will allow you to link a file into multiple folders. Rather like a Unix link. I know Windows has the same, but I have never found any applications that used it.

So, now by combining SSRS and some cloned .NET my client now has pricelists on an easy to access location for their sales folk to pick up.

SSRS Reporting

More time for for honey?

“What are you doing, Pooh?” asked Piglet.

“I’m busy busy busy. I’ve been working since 7am. I have to get this data ready for Christopher Robin’s board meeting.”

“How do you do that?” said Piglet.

“It’s very complicated,” Pooh scratched his head. “I have to run this report and then extract the columns I need into a spreadsheet.

“Then, I run this macro that removes the blank lines and headings. I then copy it into this spreadsheet and add the totals.”

“Wow,” Said Piglet. “Does that ever go wrong?”

“No, as long as nobody distracts me, and I do it exactly the same without missing out a step. Even a bear of very little brain can do it,” said Pooh. “Finally, I print it to a pdf and email it to everybody on a list I have.”

“Oh,” Piglet said. “Why not get somebody to write an SSRS report that does all of that without you touching it?”

“More time for honey?” Pooh said.

“Exactly,” Piglet replied.

VBA Coding Part 2

In the seventies there was a computer pundit, Edsger W. Dijkstra  who used to claim that a programmer only needed to use three statements to construct any programme:

Assignment: LET,

Branching: IF THEN ELSE,

and: LOOP

He considered everything else a confusing frippery. Whilst this is possibly true, using the full range of statements available within a programming language is the best way to achieve an efficient program. DO you care … If you’re writing an excel Macro and it works, probably not.

An example of needing to be efficient is. I was once asked to look at a company’s process to migrate the data from system A to system B. The data extraction from system A took 60 hours and they wanted to do it over the weekend without disruption. By making the programming more efficient, I reduced the extraction to 120 minutes. This meant I could do the migration overnight, rather than at the weekend. Saving them the cost of all that weekend work.

So lets take a look at the rest of the code:

   Do Until tvar = ""
      Do Until tvar = ""
        Cells(y, x) = tvar
        x = x + 1
        tvar = trim(Cells(y, x))
      x = 1
      y = y + 1
      tvar = trim(Cells(y, x))

The DO statement introduces the start of a LOOP in the code.

You will notice that between the While and the Loop the lines of code are indented. Why have I done this? It is a visual clue to the code included in the loop. It serves no purpose, but can save a lot of time when tracking down a bug in the code.

It might seem odd that the very next line of the Loop code is another Do Loop section. You will also see that this is further indented. Again, this serves only to highlight the inner loop.

Look at the code and I am sure you will make sense of it:

Do Until tvar="" Means that the loop will run until tvar = "" (The cell is empty)
Cells(y, x) = tvarReturns the contents of tvar into the spreadsheet cell on row Y, Column x.
x = x + 1Is incrementing the variable x (take the contents of x and add one)
tvar = trim(Cells(y, x))Is reloading the variable tvar with the next cell on row y.

And then the loop causes the code to return to the do statement which will run until the condition is TRUE
The final part of the programme executes after the inner loop has finished.

x = 1Resets the column pointer to one.
y = y + 1Increments the row pointer
tvar = trim(Cells(y, x))Once more reloads the variable tvar to satisfy the outer loop

The outer loop repeats until the condition is TRUE


That was a brief insight into how my mind works! I tend to think a little like a computer and so I find these things easy to write and understand. I hope it has tempted you to consider trying to extend your macro writing beyond simply duplicating a simple sequence of tasks.

Using VBA in Excel

After the last post, I got a couple of emails asking about using VBA in macros.
Remember that I am a programmer at heart, so there might be ways to do things using ‘raw’ excel functionality. I just find it easier to cut code.
My best advice on getting started is  is to use the macro recording function and then look at what gets recorded. Excel is writing VBA. So, with a little bit of knowledge it is possible to do quite complex things.
If I take the lines from the working version I will explain them a section at a time:

Sub trimit()

The macro is a VBA subroutine. The keyword Sub declares a subroutine Trimit() gives it a name. The last line is END SUB Each subroutine is bracketed by these lines allowing multiple routines to reside on the same piece of source code.

x = 1

If VBA finds no keyword at the start of a line it assumes the word LET. Therefore this line is creating a variable called ‘x’ and assigning a value to it of 1. (Read it as LET x = 1).  LET is the original BASIC command to assign a variable with a value. The variable is on the left of the equals sing, the value being assigned on the right.
The next line is almost exactly the same, except it declares a second variable called ‘y’.

y = 1

The fourth line is yet another assignment, but this is also using TWO VBA functions, rather than a specific value.

tvar = trim(Cells(y, x))

This means the line starts LET tvar =
I then use a Trim function. This removes any extra spaces from whatever is passed to it.

What is passed to it, is another function Cells. This takes two arguments. The y position (Row) and the x position, Column of the current sheet.

This means that 1,1 is cell ‘A1’ NOTE: The row is the first argument, and the column is the second.

If you’re using Excel, you already use functions. Functions are a useful piece of code that returns a result. EG: SUM([RANGE]) is something we all use. SUM is passed the Argument of a range and returns the value of all of the cells in that range added up.


Okay, that’s the first lesson. Hopefully, you can find where to create a macro/ VBA subroutine and understand how to assign a variable. The remaining part of the explanation will be along in the next few days.

Compile Error: Wrong number of arguments

SQL Data juggling.

Excel is a reasonable tool for juggling data from system to system and it has VBA behind it to make life easy if you can knock up a quick macro. So I thought …

As it happens, I haven’t needed to do too much data fiddling in the last year or so and I know I have become a little rusty. This sent me around in rings.

A quick export, fix a dozen rows and then import it back in.

The data would not go back in using the flat file import export tool for MSSQL. Truncating error.

I needed a quick macro I have written a dozen times to trim every cell in the sheet.

All of a sudden I am popping a compile error:

Compile Error: Wrong number of arguments or invalid property assignment.

What could I be getting wrong? Surely they haven’t changed the trim function in VBA? comment lines out, change things around, add a message to check what is going into the function.

Blushing now on how dim I was, I had to share this.

A) Because I have no pride and it made me chuckle

B) Because I realised that a lot of people can’t easily write a macro from memory to trim every cell and they might appreciate a working version.

What’s the difference?

Notice for some dim, brain out of gear reason I gave the macro the same name as the Trim function. How many years programming knowledge? 40 years! I fell into using a reserved word for something.

Below is the real code in case anybody wants it:

Sub trimit()
 x = 1
 y = 1
 tvar = trim(Cells(y, x))
 Do Until tvar = ""
         Do Until tvar = ""
            Cells(y, x) = tvar
            x = x + 1
            tvar = trim(Cells(y, x))
         x = 1
         y = y + 1
         tvar = trim(Cells(y, x))
End Sub

SSRS Error

SSRS Email sending error

Sql Server Reporting Services is an amazing tool provided as part of Microsoft’s SQL Server product.

The front end isn’t the prettiest, but it allows for users to get to reports under control of active directory security.

One of the great features is being able to schedule a subscription to send an email. It works like clockwork and everybody forgets about it until the day the report fails.

‘Failure sending mail. Exceeded storage allocation. The server response was 4.3.1 Message size exceeds fixed maximum size. Mail will not be resent’

Starting the diagnosis was fairly simple. Checking the subscription showed the failure message. The puzzle being what had changed? I’d heard of a ten Meg limit on some mail servers, but surely this was going to an exchange server. Did that have a limit. After running the report and saving as a spreadsheet, the whole thing was less than 2meg.

The answer was a palm on face slapping. Of course the mail wasn’t going straight out. It was routing through a local server running the ancient SMTP service that I think has been around since NT 4 days.

To be honest, I don’t know if these are the standard settings, or if somebody had been trying out the effects of the settings. However, unticking the restrictions on message size fixed the problem and meant the report will continue to go out for the next geological epoch.

Scammers – You’ll never fall for it, will you?

A local business has just told me about this one:

Their customer received an email from them with a change of bank details. 30000 pounds, yes count the zeroes, thirty thousand pounds paid from the customer into the ‘new bank account’.

The email looks almost perfect except for the double letter in the domain name. It is obviously an email that has been around the building and then sent out.

There is no come back. The bank is not liable, the chances of anybody seeing that cash again is remote. Possibly two local businesses will be forced to close.

The accounts person thought they were doing the correct thing making the change. This company even maintains old-fashioned procedures. The person who makes payments can’t change account details.

Is it time to review your procedures to double check emails and requests?

This is the second email scam aimed at business I have seen in the last month.

RSSQL API Coding fun

There’s a strangely satisfying feeling when I manage to get the RSSQL API to work. It’s only half documented and written for c++. I’ve never yet had to use the parts that are documented and I use c#.

Its a sad confession, but I have never written c++, I can cope with C, and I consider C# to be one of the cleanest languages to use, but c++ and I never got along. I’d like to claim I objected to the implementation of objects, or point to the over use of pointers, but the simple truth is nobody ever paid me to learn it. I have no pride and will code in any language the client asks me to use. However, without a project I don’t have the enthusiasm required to really get a handle on the language.

The problem with using .net to interface onto the API is I need to define every record layout byte by byte. Added to the fact that RSSQL programmers use every form of INT from tiny – big, plus they define a new alias for the type, making it really fun to translate.

However, enough whining, because once I crack the record structure, I then get to fight the internal integrity checks. Nothing better than watching procedure lines through the profiler to ascertain where the error is kicking out!

The latest set of mods were to update records received as web requests over the ONeil bridge. I’m extracting the detail lines to consolidate them into single orders based upon more complex rules than ONeil allows me to use.

Extracting the records proved easy enough, but going back to update the status and work order details onto each line gave me issues.
All now cracked and working sweetly.

Maybe I should publish the records I currently have defined to use the RSSQL API? I’ve done work orders, work order line, Web order details, and items. If anybody wants to have them, drop me an email and I’ll fire them over. Obviously, you have to remember, these can be different on every dot release of the product and rarely survive a full version number increment.