Results 1 to 9 of 9

Thread: [RESOLVED] Copy single dimension array to clipboard

  1. #1

    Thread Starter
    Lively Member StevenM's Avatar
    Join Date
    Mar 2015
    Posts
    73

    Resolved [RESOLVED] Copy single dimension array to clipboard

    I searched the VB.Net part of this site and did not find an answer. So...

    I have a small text array, usually under a dozen elements, that am trying to copy to the clipboard.
    Here is what I have tried so far:

    Code:
    Clipboard.Clear()
    Clipboard.SetText(artemp)
    and...
    Code:
    Clipboard.Clear()
    Clipboard.SetText(artemp(5))
    and...
    Code:
    Clipboard.Clear()
    Clipboard.SetText(artemp())
    Using a string variable with a comma delimiter is not going to work as I want to paste the results into an Excel spreadsheet where Excel will update other cells with the pasted data.

    Thanks in advance!

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Copy single dimension array to clipboard

    If you want to save multi-part text to the Clipboard then you'd need to use the overload of SetText that allows you to specify a TextDataFormat value and that value would have to be CommaSeparatedValue. Of course, the String you provide would obviously have to contain values separated by commas.

    By the way, I didn't know the answer to your question off the top of my head so I went to the MSDN documentation via the Help menu in VS and checked out the Clipboard class and that SetText method. I had the answer in less than a minute. You should always consult the relevant documentation first.

    Hmmm... I should have read your whole post as I see now that you say that using CSV data won't work. I'll test that for myself to see but if it doesn't then I doubt that anything will.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Copy single dimension array to clipboard

    Progress. So, I tested copying several cells from Excel and then getting that data from the Clipboard in CSV format in a VB app and it turned out that the data was returned via a MemoryStream. I was able to get the data as CSV text like this:
    vb.net Code:
    1. If Clipboard.ContainsData(DataFormats.CommaSeparatedValue) Then
    2.     Using clipboardStream = DirectCast(Clipboard.GetData(DataFormats.CommaSeparatedValue), MemoryStream),
    3.           reader As New StreamReader(clipboardStream)
    4.         Dim s = reader.ReadToEnd()
    5.  
    6.         MessageBox.Show(s)
    7.     End Using
    8. End If
    That seems to suggest that writing CSV text to a MemoryStream and either saving that to the Clipboard or getting a Byte array from it and saving that should work. I'll test and get back.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: Copy single dimension array to clipboard

    After a bit of research I've determined that Excel prefers Tab-delimited text and this tested successfully:
    vb.net Code:
    1. Dim arr = {"First", "Second", "Third", "Fourth", "Fifth"}
    2. Dim tabbedText = String.Join(ControlChars.Tab, arr)
    3.  
    4. Clipboard.SetText(tabbedText)
    I'm not sure what other applications would do with that but it certainly seems to work in Excel 2016.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Copy single dimension array to clipboard

    Hi

    the other way woud be to insert the Data via OLEDB,
    I haven't tried a Select Into with Range but I should also be possible

    here a sample Insert to a Cell(s) in XLS and XLSX

    Code:
    Imports System.Data.OleDb
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            'XLS FORMAT
            Try
                Dim MyConnection As OleDbConnection
                Dim myCommand As New OleDbCommand
                Dim sql As String
    
                MyConnection = New OleDbConnection _
                ("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" +
                "'c:\Mybook.xls';Extended Properties=Excel 8.0;")
    
                MyConnection.Open()
                myCommand.Connection = MyConnection
                sql = "Insert into [Sheet1$] (ID,Name) values('5','e')"
                myCommand.CommandText = sql
                myCommand.ExecuteNonQuery()
                MyConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            MsgBox("Row Added ")
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            'THIS IS FOR XLSX FORMAT
            Try
                Dim MyConnection As OleDbConnection
                Dim myCommand As New OleDbCommand
                Dim sql As String
    
                MyConnection = New OleDbConnection _
                ("provider=Microsoft.ACE.OLEDB.12.0; Data Source=" +
                "'c:\Mybook.xlsx';Extended Properties=Excel 12.0 XML;")
    
                MyConnection.Open()
                myCommand.Connection = MyConnection
                sql = "Insert into [Sheet1$] (ID,Name) values('5','e')"
                myCommand.CommandText = sql
                myCommand.ExecuteNonQuery()
                MyConnection.Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            MsgBox("Row Added ")
        End Sub
    End Class
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,040

    Re: Copy single dimension array to clipboard

    Hi,

    just tried it a diffrent way, see if it works for you

    Code:
    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button8.Click
            Dim a As Integer
            Dim Exceldaten(9, 2) As String
    
            For a = 0 To 9
    
                Exceldaten(a, 0) = Chr(65 + a)
                Exceldaten(a, 1) = Chr(48 + a)
                Exceldaten(a, 2) = "Column 3 - Row " & (a + 1)
    
            Next
    
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
    
            oXL = CreateObject("Excel.Application")
    
            oWB = oXL.Workbooks.Add
            oSheet = oWB.ActiveSheet
    
            'oXL.Visible = True
            'oXL.UserControl = True
    
            oSheet.Range("A1").Value = "vbForums"
            oSheet.Range("B1").Value = "my Number"
            oSheet.Range("C1").Value = "S"
    
            oSheet.Range("A1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("E1").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
    
            oSheet.Range("A1").ColumnWidth = 10   ' Werte in Excelformat
            oSheet.Range("B1").ColumnWidth = 10
            oSheet.Range("C1").ColumnWidth = 15.5
    
            With oSheet.Range("A1", "C1")
                .Font.Bold = True
                .Font.Color = RGB(255, 0, 0)
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            End With
    
    
            'Daten in Excel schreiben ---------------------------------------------------------------------------------------------
            oSheet.Range("A2", "C9").Value = Exceldaten
    
            oSheet.SaveAs("C:\Exceldaten99.xls")
    
            oSheet = Nothing
            oWB = Nothing
            oXL.Quit()
            oXL = Nothing
    
    
        End Sub
    EDIT:
    set the Range you want

    Code:
     'Data to Range ... sample
            oSheet.Range("H25", "J32").Value = Exceldaten
    regards
    Chris


    regards
    Chris
    Last edited by ChrisE; Sep 17th, 2017 at 05:59 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  7. #7

    Thread Starter
    Lively Member StevenM's Avatar
    Join Date
    Mar 2015
    Posts
    73

    Re: Copy single dimension array to clipboard

    Wow! Thanks all for your time replying

    This works with one major problem:
    Code:
    tempString = ""
    For i as integer = 0 to 5
    tempString = tempString & txtMyDataBox.Text & ControlChars.Tab
    Next
    When I paste it into the spreadsheet it dumps it into individual separate cells. Horizontally.
    I need it to dump it vertically.

    Looking at more of the suggestions

  8. #8

    Thread Starter
    Lively Member StevenM's Avatar
    Join Date
    Mar 2015
    Posts
    73

    Re: Copy single dimension array to clipboard

    Quote Originally Posted by jmcilhinney View Post
    After a bit of research I've determined that Excel prefers Tab-delimited text and this tested successfully:
    vb.net Code:
    1. Dim arr = {"First", "Second", "Third", "Fourth", "Fifth"}
    2. Dim tabbedText = String.Join(ControlChars.Tab, arr)
    3.  
    4. Clipboard.SetText(tabbedText)
    I'm not sure what other applications would do with that but it certainly seems to work in Excel 2016.
    jmcilhinney, Yours works with the same little hook. When pasting to Excel it pastes horizontally.

    Also, there is no set range of cells in Excel as this is going in different places on different sheets.

    Trying another example shared here

  9. #9

    Thread Starter
    Lively Member StevenM's Avatar
    Join Date
    Mar 2015
    Posts
    73

    Re: Copy single dimension array to clipboard

    This does it without overwriting the Excel cell contents of the columns on either side:
    Code:
    tempString = ""
    For i as integer = 0 to 5
    tempString = tempString & txtMyDataBox.Text  & ControlChars.CrLf
    Next
    Thanks to all those who contributed. You got me thinking in a different direction. Sometimes the answers are so close we can't see them till others shines their light on it!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width