Excel extract vba - spaces/formatting

Question

I'm building an extract in EXCEL. I've already asked a few questions about it, but as I continue to make progress, I also continue to face obstacles that prevent me from having it look professional. Here's what my extract is like...

I select date from SQL Server..

Select Location,Name, Q1, Comments
From tblInfo

Then I make headers for extract

With xlSheetInfo
.Cells(5, 1).ColumnWidth = 50
.Cells(5, 1).Value = "School"
.Cells(5, 2).ColumnWidth = 25
.Cells(5, 2).Value = "CLIENT NAME"
.Cells(5, 3).Value = "Q1"
.Cells(5, 4).Value="Comments"
End with

Then I populate it with data...using Recordset

While Not g_RS3.EOF
  For i = xlCol To rCount
        Cells(xlRow, xlCol).Value = g_RS3("School")
        Cells(xlRow, xlCol).Font.Bold = True
        xlCol = xlCol + 1
        Cells(xlRow, xlCol).Value = g_RS3("LastName") & " ," & g_RS3("FirstName")
        xlCol = xlCol + 1
        Cells(xlRow, xlCol).Value = g_RS3("Q01")
        xlCol = xlCol + 1
        Cells(xlRow, xlCol).Value = g_RS3("Comments")
        Cells(xlRow, xlCol).EntireColumn.AutoFit
    xlrow = 1
    g_rs3.movenext
Next i
Wend

The issue Icame across is in the Comments Section. The user is able to enter multiple lines/sentences of comments. What I'm seeing is that some users enter a comments, do a few line breaks, and enter another comment - for the same record. So if you look at the spreadsheet, it shows you these unnecessary spaces. In some cases the user will do a few line breaks, and then enter a comment, in this case it also looks unprofessional. I'm tyring to get rid of the spaces all together, so that if there are a few lines of comments, there one below another. Here's an image of what I'm talkin about ...

https://i.sstatic.net/T0dmS.png

I played around with RTrim, LTrim, Trim but it doesn't seem to work. Another thing is, i might have any values there (NULL). Any ideas ????

Unfortunately there is no easy way of removing such leading or trailing newlines from an SQL character field. You may create a function for that using CLR if you think it is ever worth it. The way you are creating the Excel sheet content is already one of the slowest ways possible, you wouldn't want to do that slower. For example, you could make it much faster if you did a single CopyFromRecordSet, or QueryTables.Add(). I suggest you prevent entering such data into sql server and do this edit on entry instead. — Cetin Basoz
– Cetin Basoz, Commented May 23, 2016 at 15:23
I guess speed doesn't matter in this case - this is an old VB6 applications - i'm creating the extract the same way it's been done before me. I'm new to this environment, just following what others have done. How would this be done with copy from recordset — FatBoySlim7
– FatBoySlim7, Commented May 23, 2016 at 15:29
I am simply saying, what has already been done is not the right way to do that. You shouldn't carry on what is the wrong way. But in any case it is your application and your users. If you think it is not an issue, then you could loop your recordset and trim the leading and trailing newlines from it.Unfortunately I don't know if VBA has such simple functions to take care of it. Check Ltrim() and Rtrim() documentation to see if it supports specifying trim characters. — Cetin Basoz
– Cetin Basoz, Commented May 23, 2016 at 15:33
I tried Ltrim, and rtrim but it didn't seem to do anything. I'd like to learn to do stuff the quicker way, but I'm basically doing the way other extract have been done (right or wrong/ slow or fast) — FatBoySlim7
– FatBoySlim7, Commented May 23, 2016 at 15:34

Robin Mackenzie · Accepted Answer · 2016-05-23 15:11:14Z

1

Put this function in a module:

Public Function RemoveLf(strIn As String) As String
    Dim strOut As String
    strOut = Replace(strIn, vbLf, " ")
    RemoveLf = strOut
End Function

Then in your code, you can replace this line:

Cells(xlRow, xlCol).Value = g_RS3("Comments")

With this line:

Cells(xlRow, xlCol).Value = RemoveLf(g_RS3("Comments"))

HTH

answered May 23, 2016 at 15:11

Robin Mackenzie

19.4k7 gold badges42 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Cetin Basoz Over a year ago

How would this distinguish the CR, LF or CRLF that is not leading or trailing but in the string itself?

FatBoySlim7 Over a year ago

this is a nice function, but it doesn't fix the issue that I showed in the image. This seems to have fixed the issue if there are spaces between two comments, but not if there are spaces before the comment itself. image shows the issue. This function also doesn't work for if I have spaces after my comments

Robin Mackenzie Over a year ago

@CetinBasoz - yes, I see I misread the OP's question and this would be a job for a regular expression. I suppose any \s before the first \w or something.

Robin Mackenzie Over a year ago

@FatBoySlim7 - you might try a variant on the function to look for vbCr and also vbCrLf as alternates for vbLf and see if that does the trick.

FatBoySlim7 Over a year ago

This actually does the job for the most part, so I will accept it as answer @robin

Collectives™ on Stack Overflow

Excel extract vba - spaces/formatting

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related