Excel Macro help on SQL select remove column limit-Logged in!

Question

Hi, Sorry I have just created an abccount and lost the thread for the last query I just placed. Anyone know how to link it back up with my openid? Query below.

This is really another question linking with the question before. I have an issue where I have a code written however it will only let me select 22 columns then comes up with 'too many line continuations' if I try to add another one. Does anyone know how to extend this-I have about 100 columns to add on for this formulae, and need to copy over another 100 which don't need 'checking'.

Code

strSQL = "SELECT a.[A], " _ & "(SELECT Max([Serial]) FROM [Data$] b WHERE b.[A]=a.A) As Serial, " _    
& "(SELECT Max([B]) FROM [Data$] b WHERE b.[A]=a.A) As B, " _    
& "(SELECT Max([C]) FROM [Data$] b WHERE b.[A]=a.A) As C, " _    
& "(SELECT Max([D]) FROM [Data$] b WHERE b.[A]=a.A ) As D, " _    
& "(SELECT Max([E]) FROM [Data$] b WHERE b.[A]=a.A ) As E, " _    
& "(SELECT Max([F]) FROM [Data$] b WHERE b.[A]=a.A ) As F, " _    
& "(SELECT Max([G]) FROM [Data$] b WHERE b.[A]=a.A ) As G, " _    
& "(SELECT Max([H]) FROM [Data$] b WHERE b.[A]=a.A ) As H, " _    
& "(SELECT Max([I]) FROM [Data$] b WHERE b.[A]=a.A ) As I, " _    
& "(SELECT Max([J]) FROM [Data$] b WHERE b.[A]=a.A ) As J, " _    
& "(SELECT Max([K]) FROM [Data$] b WHERE b.[A]=a.A ) As K, " _    
& "(SELECT Max([L]) FROM [Data$] b WHERE b.[A]=a.A ) As L, " _    
& "(SELECT Max([M]) FROM [Data$] b WHERE b.[A]=a.A ) As M, " _    
& "(SELECT Max([N]) FROM [Data$] b WHERE b.[A]=a.A ) As N, " _    
& "(SELECT Max([O]) FROM [Data$] b WHERE b.[A]=a.A ) As O, " _    
& "(SELECT Max([P]) FROM [Data$] b WHERE b.[A]=a.A ) As P, " _    
& "(SELECT Max([Q]) FROM [Data$] b WHERE b.[A]=a.A ) As Q, " _    
& "(SELECT Max([R]) FROM [Data$] b WHERE b.[A]=a.A ) As R, " _    
& "(SELECT Max([S]) FROM [Data$] b WHERE b.[A]=a.A ) As S, " _    
& "(SELECT Max([T]) FROM [Data$] b WHERE b.[A]=a.A ) As T, " _    
& "(SELECT Max([U]) FROM [Data$] b WHERE b.[A]=a.A ) As U, " _    
& "(SELECT Max([V]) FROM [Data$] b WHERE b.[A]=a.A ) As V " _    
& "FROM [Data$] a " _    
& "GROUP BY a.[A]"

Thanks

Answer 1

Well, just make it less lines, each line a bit longer...

strSQL = "SELECT a.[A], " _ & "(SELECT Max([Serial]) FROM [Data$] b WHERE b.[A]=a.A) As Serial, " _    
& "(SELECT Max([B]) FROM [Data$] b WHERE b.[A]=a.A) As B, (SELECT Max([C]) FROM [Data$] b WHERE b.[A]=a.A) As C, (SELECT Max([D]) FROM [Data$] b WHERE b.[A]=a.A ) As D, " _    
& "(SELECT Max([E]) FROM [Data$] b WHERE b.[A]=a.A ) As E, (SELECT Max([F]) FROM [Data$] b WHERE b.[A]=a.A ) As F, (SELECT Max([G]) FROM [Data$] b WHERE b.[A]=a.A ) As G, " _    
& "(SELECT Max([H]) FROM [Data$] b WHERE b.[A]=a.A ) As H, (SELECT Max([I]) FROM [Data$] b WHERE b.[A]=a.A ) As I, (SELECT Max([J]) FROM [Data$] b WHERE b.[A]=a.A ) As J, " _    
& "(SELECT Max([K]) FROM [Data$] b WHERE b.[A]=a.A ) As K, (SELECT Max([L]) FROM [Data$] b WHERE b.[A]=a.A ) As L, (SELECT Max([M]) FROM [Data$] b WHERE b.[A]=a.A ) As M, " _    
& "(SELECT Max([N]) FROM [Data$] b WHERE b.[A]=a.A ) As N, (SELECT Max([O]) FROM [Data$] b WHERE b.[A]=a.A ) As O, (SELECT Max([P]) FROM [Data$] b WHERE b.[A]=a.A ) As P, " _    
& "(SELECT Max([Q]) FROM [Data$] b WHERE b.[A]=a.A ) As Q, (SELECT Max([R]) FROM [Data$] b WHERE b.[A]=a.A ) As R, (SELECT Max([S]) FROM [Data$] b WHERE b.[A]=a.A ) As S, " _    
& "(SELECT Max([T]) FROM [Data$] b WHERE b.[A]=a.A ) As T, (SELECT Max([U]) FROM [Data$] b WHERE b.[A]=a.A ) As U, (SELECT Max([V]) FROM [Data$] b WHERE b.[A]=a.A ) As V " _    
& "FROM [Data$] a " _    
& "GROUP BY a.[A]"

Answer 2

Or make the statement

strSql = "bla bla"
strSql = strSql & "bla bla 2"
strSql = strSql & "bla bla 3"
strSql = strSql & "bla bla 4"
...
strSql = strSql & "bla bla n"

This will keep it readeble and not span many columns of text.