Copy .csv contents from clipboard to a spreadsheet

[sparky2205]

Folks,
I have a .csv file which contains data spread across a number of rows and columns.
I want the user to be able to copy this data to the clipboard and then run a macro that will take this data from the clipboard and paste it into a spreadsheet called "Data" beginning at cell A2.

I currently have:

Sub GetData()

Dim MyData As DataObject
Dim sText As String

Set Data = ThisWorkbook.Worksheets(1)
Set MyData = New DataObject
MyData.GetFromClipboard
sText = MyData.GetText

'ThisWorkbook.Worksheets(1).Range("A2").Value = sText
Data.Range("A2").Value = sText

End Sub

But this puts all the data into cell A2 on Data.
What I need is a way to have Excel understand that the data being pasted is a range that should be pasted starting at A2.
I want the Data worksheet to look the same as the .csv file, from a data perspective. There are some header rows on the Data spreadsheet as well.

 

[sparky2205]

Anthony,
I got that working:

StartTime = CDate(Data.Cells(Row, 1).Value)

TotalTime = TotalTime + CDate(Data.Cells(Row, 1).Value) - StartTime

It was a matter of figuring out where exactly the CDate went.
Thanks a million for your help with that.

I do have one other query on the same thing. Feel free to ignore me at this point, I've taken up a lot of your time.
Is it possible to check that the content of A1 in the .csv file on the clipboard is the word Time?
If not then display a messagebox to the user that they haven't copied the relevant data and Exit Sub.

 

[GWteB]

@GWteB;
Your code has the same result as Anthony47 i.e. the pasted data is all in one column
But your code also errors on the line below:
Application-defined or object-defined error

The "all in one column" issue is caused by another delimiter than a comma, as already pointed out by @Anthony47
The Run-time error occurred since my code did not take into account there could be semi-empty rows: rows containing delimiters meanwhile not containing any data at all.

Although you now have a solution, I didn't want to withhold my revised version.

Sub GetData()

    GetCSVData vbTab
'    GetCSVData ","      ' << optional
'    GetCSVData ";"      ' << optional
End Sub

Sub GetCSVData(ByVal argDelimiter As String)

    Dim MyData As DataObject
    Dim sText As String

    Dim Sht As Worksheet
    Set Sht = ThisWorkbook.Worksheets(1)
    
    Set MyData = New DataObject
    MyData.GetFromClipboard
    sText = MyData.GetText

    Dim arrRows As Variant, arrCols As Variant
    arrRows = VBA.Split(sText, vbNewLine)

    Dim rData As Range, i As Long
    Set rData = Sht.Range("A2").Resize(UBound(arrRows) + 1)
    For i = 1 To rData.Count
        arrCols = VBA.Split(arrRows(i - 1), argDelimiter)
        If UBound(arrCols) > 0 Then
            rData.Cells(i, 1).Resize(, UBound(arrCols) + 1).Value = arrCols
        End If
    Next i
End Sub

 

[GWteB]

Missed your additional query, couldn't edit my previous post. Try:

Sub GetData()

    If Not GetCSVData(vbTab) Then
        MsgBox "You haven't copied the relevant data.", vbExclamation
    End If
End Sub


Function GetCSVData(ByVal argDelimiter As String) As Boolean

    Dim MyData As DataObject
    Dim sText As String

    Dim Sht As Worksheet
    Set Sht = ThisWorkbook.Worksheets(1)
    
    Set MyData = New DataObject
    MyData.GetFromClipboard
    sText = MyData.GetText

    Dim arrRows As Variant, arrCols As Variant
    arrRows = VBA.Split(sText, vbNewLine)
    If VBA.InStr(1, arrRows(0), "time", vbTextCompare) > 0 Then
        Dim rData As Range, i As Long
        Set rData = Sht.Range("A2").Resize(UBound(arrRows) + 1)
        For i = 1 To rData.Count
            arrCols = VBA.Split(arrRows(i - 1), argDelimiter)
            If UBound(arrCols) > 0 Then
                rData.Cells(i, 1).Resize(, UBound(arrCols) + 1).Value = arrCols
            End If
        Next i
        GetCSVData = True
    End If
End Function

 

[sparky2205]

Thanks for coming back GWteB. Your code works perfectly when the data is selected. It also works when I select other Excel data that is not the data I want.
However, it errors when no data is selected, on this line:

sText = MyData.GetText

with error:

I ran into this problem myself when I made my own humble attempts and I got around it like this:

On Error Resume Next
If IsError(sText = MyData.GetText) Then
    MsgBox "Check that you have copied the data"
Else: sText = MyData.GetText
End If

So this appears to work for both scenarios:

Sub GetData()

    If Not GetCSVData(vbTab) Then
        MsgBox "You haven't copied the relevant data.", vbExclamation
    End If
End Sub


Function GetCSVData(ByVal argDelimiter As String) As Boolean

    Dim MyData As DataObject
    Dim sText As String

    Dim Sht As Worksheet
    Set Sht = ThisWorkbook.Worksheets(1)
    
    Set MyData = New DataObject
    MyData.GetFromClipboard
    'sText = MyData.GetText

    On Error Resume Next
    If IsError(sText = MyData.GetText) Then
        MsgBox "Check that you have copied the data"
    Else: sText = MyData.GetText
    End If

    Dim arrRows As Variant, arrCols As Variant
    arrRows = VBA.Split(sText, vbNewLine)
    If VBA.InStr(1, arrRows(0), "time", vbTextCompare) > 0 Then
        Dim rData As Range, i As Long
        Set rData = Sht.Range("A2").Resize(UBound(arrRows) + 1)
        For i = 1 To rData.Count
            arrCols = VBA.Split(arrRows(i - 1), argDelimiter)
            If UBound(arrCols) > 0 Then
                rData.Cells(i, 1).Resize(, UBound(arrCols) + 1).Value = arrCols
            End If
        Next i
        GetCSVData = True
    End If
End Function

When I select the wrong data your message is displayed.
When I select no data my message is displayed.

 

[GWteB]

Fortunately, you are even more careful than I am and the contributions of other forum members included we end up with something that satisfies. Well done and thanks for posting back!

 

[Anthony47]

To check that A1 contains "Time", just before End Sub:

If UCase(Range("A1")) <> "TIME" Then     'Should it be A2??
    MsgBox ("Your error message")
End If
End Sub

BUT....
Why don't you IMPORT the csv file, rather than open the csv, copy its text, import the clipboard to excel?

For example:
A) Select your worksheet
B) Run this macro once to set a connection to the csv file

Sub SetQuery()
'Run this only once; then run UpdConn
'
    Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;D:\DDownloads\demo.csv", _
        Destination:=Range("$A$2"))
        .Name = "demo"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(4, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

BEFORE RUNNING THE MACRO, modify the name of the file (I used D:\DDownloads\demo.csv)

Then, whenever you need to import a new file you can use the following code:

Sub UpdConn()
Dim FullNome, Scelta
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Csv", "*.csv"
    .Show
    If .SelectedItems.Count = 0 Then
       MsgBox ("No any selection, aborted")
       Exit Sub
    End If
    FullNome = .SelectedItems(1)
End With
'
'Modify the connection:
With Range("A2").QueryTable
    .Connection = "TEXT;" & FullNome
    .Refresh
End With

End Sub

You will be asked to select the csv file to import and then the connection is refreshed to update the values

Bye

 

[sparky2205]

Morning Anthony,
thanks for this, great to have another option.
I've run your new code and it runs fine but it's back to the previous issue where all the data is in column A. Additionally this time extra commas are also inserted:

I tried adding your previous splitting code:

Sub UpdConn()
Dim FullNome, Scelta
Dim pw As String: pw = ""

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Csv", "*.csv"
    .Show
    If .SelectedItems.Count = 0 Then
       MsgBox ("No any selection, aborted")
       Exit Sub
    End If
    FullNome = .SelectedItems(1)
End With
'
'Modify the connection:
ThisWorkbook.Worksheets(1).Unprotect Password:=pw
With Range("A2").QueryTable
    .Connection = "TEXT;" & FullNome
    .Refresh
End With

mySplit = Split(sText, Chr(10), , vbTextCompare)
Data.Range("A2").Resize(UBound(mySplit), 1).Value = Application.WorksheetFunction.Transpose(mySplit)
    Data.Range("A2").Resize(UBound(mySplit), 1).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True

ThisWorkbook.Worksheets(1).Protect Password:=pw

End Sub

But this errors on:

Data.Range("A2").Resize(UBound(mySplit), 1).Value = Application.WorksheetFunction.Transpose(mySplit)

with a Type Mismatch error.
I've tried converting a few of the data types but to no avail.

 

[Anthony47]

I've run your new code and it runs fine but it's back to the previous issue where all the data is in column A. Additionally this time extra commas are also inserted

Commas are (I assume) your field separators; and if you used my Sub SetQuery the "comma" (as well as the "tab") should have been used to automatically split the content of each row in its columns.
No need for further text-to-columns

If that don't work for you maybe those are not canonical "commas"...
Either let's forget this method or analyse the content of one row using the formula and method I suggested here: Copy .csv contents from clipboard to a spreadsheet

Bye

 

[sparky2205]

Hi Anthony,
these are the results of the header and first row of data from your formula:

84 # 105 # 109 # 101 # 9 # 54 # 67 # 49 # 53 # 69 # 70 # 56 # 67 # 9 # 48 # 68 # 68 # 55 # 48 # 67 # 66 # 53 # 9 # 55 # 67 # 66 # 70 # 69 # 51 # 56 # 67 # 9 # 69 # 51 # 52 # 66 # 69 # 66 # 69 # 50 # 9 # 57 # 67 # 66 # 56 # 67 # 50 # 67 # 50 # 9 # 52 # 57 # 67 # 50 # 50 # 54 # 48 # 66 # 9 # 67 # 65 # 69 # 53 # 55 # 69 # 66 # 53 # 9 # 56 # 67 # 66 # 55 # 57 # 48 # 67 # 69 # 9 # 69 # 55 # 67 # 52 # 52 # 53 # 48 # 66 # 13

50 # 49 # 47 # 48 # 51 # 47 # 50 # 48 # 50 # 50 # 32 # 49 # 53 # 58 # 52 # 56 # 9 # 49 # 9 # 9 # 9 # 9 # 9 # 9 # 9 # 9 # 13

 

[Anthony47]

Both the lines use Horizontal Tab as the field separator; there are no commas; and they get imported "as expected" (i.e. in columns) with the file import method.
I cannot see where the many ",,,," that are visible in your text come from

If it does not work for you I suggest we forget about it, given that you already have a working solution

Bye