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.

 

[Anthony47]

Try modifying as follows:

Dim MyData As DataObject
Dim sText As String

Set Data = Worksheets("Foglio4")
Set MyData = New DataObject
MyData.GetFromClipboard
sText = MyData.GetText

'ThisWorkbook.Worksheets(1).Range("A2").Value = sText
mySplit = Split(sText, Chr(10), , vbTextCompare)
Data.Range("B2").Resize(UBound(mySplit), 1).Value = Application.WorksheetFunction.Transpose(mySplit)
End Sub

Bye

 

[GWteB]

How about:

Sub GetData()

    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), ",")
        rData.Cells(i, 1).Resize(, UBound(arrCols) + 1).Value = arrCols
    Next i
End Sub

 

[sparky2205]

Hi folks,
thanks for the quick responses.

@Anthony47;
Your code runs without error but I end up with:

i.e. The pasted data is all in one column. It should be spread across as many columns as there are headers.

@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

rData.Cells(i, 1).Resize(, UBound(arrCols) + 1).Value = arrCols

Here's an example of data from the .csv file in case that helps.

Extracted Data 2022-03-24 08_39.csv
	A	B	C	D	E	F	G	H	I	J
1	Time	6C15EF8C	0DD70CB5	7CBFE38C	E34BEBE2	9CB8C2C2	49C2260B	CAE57EB5	8CB790CE	E7C4450B
2	21/03/2022 15:48	1
3	21/03/2022 15:49		1
4	21/03/2022 15:49			1
5	21/03/2022 15:49				1
6	21/03/2022 15:49					1
7	21/03/2022 15:49						1
8	21/03/2022 15:49							1
9	21/03/2022 15:49								1
10	21/03/2022 15:58								1
11	21/03/2022 16:05					0
12	21/03/2022 16:05						0
13	21/03/2022 16:05							0
14	21/03/2022 16:05								0
15	21/03/2022 16:05				0
16	21/03/2022 16:05	0
17	21/03/2022 16:05			0
18	21/03/2022 16:05		0
19	21/03/2022 16:11			1
20	21/03/2022 16:11				1
21	21/03/2022 16:11		1
22	21/03/2022 16:11	1
23	21/03/2022 16:16			0
24	21/03/2022 16:16		0
25	21/03/2022 16:16				0
26	21/03/2022 16:16	0
27	21/03/2022 16:52		1
28	21/03/2022 16:52			1
29	21/03/2022 16:52				1
30	21/03/2022 16:52	1
31	21/03/2022 16:57			1
32	21/03/2022 16:58				0
33	21/03/2022 16:58	0
34	21/03/2022 16:58		0
35	21/03/2022 16:58			0
Extracted Data 2022-03-24 08_39

This is just an example. The number of rows and columns is variable.

 

[Anthony47]

I ignored that the results had to be put in columns (realized that when looking at @GWteB solution, that btw I think is correct)

The problem I gues is that the fields are not "comma" separated, but use different separator, thus VBA.Split(arrRows(i - 1), ",") don't split properly the columns
Can you specify what is used to separate the columns?

In the meantime you could test adding the following snippet to my code:

'... previous lines
Data.Range("B2").Resize(UBound(mySplit), 1).Value = Application.WorksheetFunction.Transpose(mySplit)
'ADD THE NEXT LINES >>
    Data.Range("B2").Resize(UBound(mySplit), 1).TextToColumns Destination:=Range("B2"), 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
'<< End
End Sub

The code defines Comma:=True as the column separator; if it is not Comma you may choose other default separators, for example Tab, or Semicolumn
If you cannot make it running properly then tell us which is the separator and I'll try to modify the code

Bye

 

[sparky2205]

I ignored that the results had to be put in columns (realized that when looking at @GWteB solution, that btw I think is correct)

@GWteB solution didn't work for me. Maybe I did something wrong but I just copied the code. I got the same result as your code but with that error I mentioned earlier.

The data is coming from a .csv file, example provided in previous post. This has been extracted from another application.
I just highlight and copy the data from the .csv file to place it on the clipboard.
I don't know what the separator would be in this case as the copied data is in separate columns in the .csv file.

 

[Anthony47]

From one of the images you published this morning i think the separator is Tab

So the additional code I published one hour ago should work and put in columns the information

If it doesn't work, open the ".csv" file with a text editor and inspect which is the separating character
Or, using Office365, put one of the string of your csv file in A1, then in B1 set this formula:

=TEXTJOIN(" # ",TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

Copy the result of this formula and paste it in your next message

Bye

 

[sparky2205]

From one of the images you published this morning i think the separator is Tab

So the additional code I published one hour ago should work and put in columns the information

If it doesn't work, open the ".csv" file with a text editor and inspect which is the separating character
Or, using Office365, put one of the string of your csv file in A1, then in B1 set this formula:

=TEXTJOIN(" # ",TRUE,CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

Copy the result of this formula and paste it in your next message

Bye

Anthony,
yes that worked when I used your amended code, thanks for that.

It did however cause an error in another macro which I am using to parse the data and do some calculations.
It looks like something simple. I'm getting a type mismatch which is not happening when I simply copy the data into the worksheet.
This is the macro:

Sub CalculateSums()
Dim LastRow As Long
Dim LastColumn As Long
Dim TotalTime As Double
Dim StartTime As Double
Dim pw As String

pw = ""

'set sht as the main sheet
Set Data = ThisWorkbook.Worksheets(1)

'first check for the keyword in the cell(2,1)
If Not Data.Cells(2, 1) = "Time" Then
    MsgBox "Data are not in the expected region! Please double check!" _
    & vbCrLf & "Paste data in cell A2"
    End
End If

Application.ScreenUpdating = False

'First try to delete
Application.DisplayAlerts = False
While ThisWorkbook.Sheets.Count > 1
    'delete existing "calculations"
    Sheets(2).Delete
Wend
Application.DisplayAlerts = True

'create new sheet if it doesnt exist
If ThisWorkbook.Sheets.Count = 1 Then
    ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Calculations"
End If
'set calculations as the new "Calculations sheet"
Set calculations = ThisWorkbook.Worksheets(2)

'Get dimensions from the sheeet
Data.Unprotect Password:=pw
LastRow = Data.Range("A1").CurrentRegion.Rows.Count
LastColumn = Data.Range("A2").CurrentRegion.Columns.Count
Data.Protect Password:=pw

' we start at second column as the first one contains time stamps
Column = 2

While Column <= LastColumn

  
    'we start at the second row as the first one contains cardIDs, and reset all the variables
    Row = 3
    Last_OnOff = 0
    TotalTime = 0
    StartTime = 0
    
    'process the whole column
    While Row <= LastRow
        If Not IsEmpty(Data.Cells(Row, Column).Value) Then
            cell = Data.Cells(Row, Column).Value
            'everytime we hit 1 we update StartTime
            If cell = 1 Then
                [SIZE=4][COLOR=rgb(0, 0, 0)]StartTime = Data.Cells(Row, 1).Value[/COLOR][/SIZE]
                Last_OnOff = 1
            End If
               
            If cell = 0 And Last_OnOff = 1 Then
                [SIZE=4][COLOR=rgb(0, 0, 0)]TotalTime = TotalTime + Data.Cells(Row, 1).Value - StartTime[/COLOR][/SIZE]
                Last_OnOff = 0
            End If
        End If
        Row = Row + 1
    
    Wend
    'Name the column as from the main sheet
    calculations.Cells(1, Column - 1).Value = Data.Cells(2, Column).Value
    'put TotalTime on the second row
    calculations.Cells(2, Column - 1).NumberFormat = "[h]:mm:ss"
    calculations.Cells(2, Column - 1).Value = TotalTime
    'and go to the next column
    Column = Column + 1
Wend

'now we summ al the times again
TotalTime = 0
Column = 1
While Column < LastColumn
    TotalTime = TotalTime + calculations.Cells(2, Column).Value
    'put TotalTime on the second row
    Column = Column + 1
Wend
  
calculations.Cells(1, Column).Value = "Sumarized:"
calculations.Cells(2, Column).NumberFormat = "[h]:mm:ss"
calculations.Cells(2, Column).Value = TotalTime

'Make headings bold and standardise column width
With calculations.Range(Cells(1, 1), Cells(1, LastColumn))
    .Font.Bold = True
    .ColumnWidth = 10
End With

Application.ScreenUpdating = True

End Sub

I'm getting a type mismatch on "StartTime = Data.Cells(Row, 1).Value"
I changed this from Double to Variant and it seemed to accept that but then I get the same error on "TotalTime = TotalTime + Data.Cells(Row, 1).Value - StartTime"
Changing this also to Variant doesn't resolve the error.

 

[Anthony47]

Probably what is in Data.Cells(Row, 1).Value is not a time or a date, but a string. Try using

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

Bye

 

[sparky2205]

Probably what is in Data.Cells(Row, 1).Value is not a time or a date, but a string. Try using

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

Bye

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

gets me past this line.
But I still get the error on the line:

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

I've tried:

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

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

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

I still get the error on all these.