I found code on the web which takes data from an MS-Access table (my test
table has three rows by five fields) copies it to the Clipboard then into a
range within MS-Excel. Data is stored in a StringBuilder object and should
paste into Excel via Clipboard.SetDataObject. When it comes time to paste
the StringBuilder object is empty. If I strip all database code out and
place the code in another project it works fine. Any ideas? Thanks for your
time, Kevin
Problem code
Private Sub ConvertIt(ByVal aWorksheet As Excel.Worksheet, ByVal aRange As
Excel.Range)
Dim sbExcelData As New System.Text.StringBuilder
Dim Cmd As OleDbCommand
Dim Reader As OleDbDataReader
Dim Con As OleDbConnection
sbExcelData.Length = 2000
Try
Con = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; data
source=" & GetAppPath() & "\test.mdb")
Cmd = New OleDbCommand("SELECT * FROM [tbContacts]", Con)
Con.Open()
Try
Try
Reader = Cmd.ExecuteReader()
While Reader.Read()
'sbExcelData.Append(Reader("ID").ToString)
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Company Name"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Contact Name"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(Reader("Address"))
'sbExcelData.Append(vbTab)
'sbExcelData.Append(vbCrLf)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append(vbCrLf)
End While
Catch ex As OleDbException
MessageBox.Show("Problem with executing the query" & vbCrLf &
ex.Message & vbCrLf & "Can not continue")
End Try
Finally
Con.Close()
End Try
Catch ex As OleDbException
MessageBox.Show("DB Error")
Exit Sub
End Try
MessageBox.Show("theExcelData=" & sbExcelData.ToString)
Clipboard.SetDataObject(sbExcelData.ToString)
aRange.Select()
aWorksheet.Paste()
End Sub
Stripped out code that holds StringBuilder values
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim sbExcelData As New System.Text.StringBuilder
Dim iInsance As Integer
sbExcelData.Length = 2000
For iInsance = 1 To 10
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append("1")
sbExcelData.Append(vbTab)
sbExcelData.Append(vbCrLf)
Next
Console.WriteLine("theExcelData=" & sbExcelData.ToString)
Clipboard.SetDataObject(sbExcelData.ToString)
End Sub
Downloadable project
http://home.teleport.com/~kevininstructor/dotnet/sb.zip
