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))
         Loop
         x = 1
         y = y + 1
         tvar = trim(Cells(y, x))
 Loop
End Sub