Stop SQL Pasting Whitespace From Excel

Question

I'm trying to paste columns of data at a time from Excel into SQL Server. Each cell in a column contains a formula to say that, if a certain cell in another sheet is not blank, output an SQL script. If it is blank, output a blank value (""). I select and copy this data with a button (I have a button for each column of data), before pasting it into a new query in SQL Server. The SQL script is fed the values of the cells in other sheets - each column of data represents a different sheet, and each cell in a column refers to the individual cell values in the sheet it represents.

However, every time I paste the data, it includes the blank values, which appear as whitespace.

I've tried reprogramming the button in VBA so that it doesn't select the cells with a blank value, but nothing has worked so far. I've used the SpecialCells method in various different ways, but without success.

Here is an example of the current code for my buttons:

Sub SchemeCondition_Click()
Range("E2:E201").Select
Selection.Copy
End Sub

And here is an example of the formula that I have in each cell that's being copied by the button:

=IF(SchemeCondition!A2<>"","USE [PRSMaster] INSERT INTO SchemeCondition
(SchemeID, ConditionTypeID, Active, Comorbidity) VALUES(" & SchemeCondition!C2 & ",
" & SchemeCondition!D2 & ", " & SchemeCondition!E2 & ", " & SchemeCondition!F2 & ")","")

I just want to stop whitespace from appearing when I paste, so that I don't have to manually remove it each time.

Write a VBA upload script to SQL. Like this you can "translate" during upload (also known as [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load)). I am not even sure what you expect instead of whitespaces. Do you want Excel to copy over NULL instead? — Ralph, Oct 08 '15 at 12:26
@Ralph I don't want the spaces replaced. I need them removed. They represent cells containing a blank value, so I don't need them in my query. — Freya, Oct 08 '15 at 12:43

Tim Williams · Answer 1 · 2015-10-09T04:46:14.337

Sub Tester()
    CopyNonBlanks Selection
End Sub

Sub CopyNonBlanks(rng As Range)
    Dim s, c, MyData As DataObject
    s = ""
    For Each c In rng.Cells
        If Len(c.Value) > 0 Then
            s = s & IIf(Len(s) > 0, vbNewLine, "") & c.Value
        End If
    Next c
    If Len(s) > 0 Then
        'requires reference to `Microsoft forms 2 Object Library`
        Set MyData = New DataObject
        MyData.SetText s
        MyData.PutInClipboard
    End If
End Sub

Stop SQL Pasting Whitespace From Excel

1 Answers1