-
Sep 17th, 2017, 12:18 AM
#1
Thread Starter
Lively Member
[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!
-
Sep 17th, 2017, 03:44 AM
#2
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.
Last edited by jmcilhinney; Sep 17th, 2017 at 03:48 AM.
-
Sep 17th, 2017, 04:05 AM
#3
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:
If Clipboard.ContainsData(DataFormats.CommaSeparatedValue) Then Using clipboardStream = DirectCast(Clipboard.GetData(DataFormats.CommaSeparatedValue), MemoryStream), reader As New StreamReader(clipboardStream) Dim s = reader.ReadToEnd() MessageBox.Show(s) End Using 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.
-
Sep 17th, 2017, 04:21 AM
#4
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:
Dim arr = {"First", "Second", "Third", "Fourth", "Fifth"} Dim tabbedText = String.Join(ControlChars.Tab, arr) Clipboard.SetText(tabbedText)
I'm not sure what other applications would do with that but it certainly seems to work in Excel 2016.
-
Sep 17th, 2017, 05:27 AM
#5
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.
-
Sep 17th, 2017, 05:43 AM
#6
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.
-
Sep 17th, 2017, 10:27 AM
#7
Thread Starter
Lively Member
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
-
Sep 17th, 2017, 10:53 AM
#8
Thread Starter
Lively Member
Re: Copy single dimension array to clipboard
Originally Posted by jmcilhinney
After a bit of research I've determined that Excel prefers Tab-delimited text and this tested successfully:
vb.net Code:
Dim arr = {"First", "Second", "Third", "Fourth", "Fifth"} Dim tabbedText = String.Join(ControlChars.Tab, arr) 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
-
Sep 17th, 2017, 11:38 AM
#9
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|