CODEWITHSUNDEEP
vbaexcelpaste
Enissay
Enissay

Reputation: 4953

Excel VBA: Paste clipboard data (external source) with the appropriate cell data type

I am copying table data (tab delimited) many times a day from external sources (other applications such as sqlClients: Heidi, MS SQL Management Studio, inhouse tools, ...) and pasting them in Excel sheets for extended analysis.

I've been struggling to make a macro that pastes specific columns in the correct format instead of the General type which alters how data is displayed.

At first I tried setting up specific columns to the appropriate dataType before using paste special to paste values only:

ActiveSheet.Range("B:D,X:X").NumberFormat = "@"
ActiveSheet.Range("A1").PasteSpecial _
    Paste:=xlPasteValues _
    , Operation:=xlNone _
    , SkipBlanks:=False _
    , Transpose:=False

Sadly it gives Run Time Error '1004' PasteSpecial method of Range class failed which I couldn't solve.

Next I tried TextToColumns follows:

    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste

    For Each cell In Selection.Cells
        If cell <> "" Then
            cell.TextToColumns _
                Destination:=cell _
                , DataType:=xlDelimited _
                , ConsecutiveDelimiter:=False _
                , Space:=False _
                , TextQualifier:=xlTextQualifierNone _
                , Tab:=True _
                , semicolon:=False _
                , comma:=False _
                , other:=False _
                , FieldInfo:=Array(Array(0, 2), Array(1, 2), Array(3, 2), Array(10, 2), Array(15, 2))
        End If
    Next

Not only it doesn't work as expected (what is wrong with it ?), but it doesn't seem efficient as It pastes the clipboard content before looping over every cell. My tables can be really huge and thus this will take a lot of time!

So, I was wondering if there is a better/elegant way to paste the clipboard data from an external source into the appropriate cell data type. If not, helping fixing the above leads would be nice as well.

Thank you in advance <3

Edit.1:

Now that I have some correct code working thanks to @jivko's comment:

ActiveSheet.Range("A1").Select
ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, DisplayAsIcon:=False

Since I'm pasting code from different sources with different number of columns, I was wondering how could I know how many columns are there in the clipboard before pasting so I can apply the appropriate format beforehand ?

An idea would be to Paste > Count the columns > Apply the correct Format > Paste again. It doesn't sounds perfect but it should get the job done.

If you have any better solution please feel free to share <3

Upvotes: 2

Views: 6309

Answers (1)

Peter Buzzard
Peter Buzzard

Reputation: 11

I use a Macro to paste my data, and I've inserted this code near the start. It prevents double quotes (at the start of an entry) from being a problem.

'This section prevents any special characters from interfering with the paste by specifying TextQualifier:=xlNone:

Range("A1").Select
ActiveCell.FormulaR1C1 = "abc"
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
    :=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
    1), TrailingMinusNumbers:=True
Range("A1").Select
Selection.ClearContents

'End Section

Upvotes: 1

Related Questions