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