vb .net using excel automation to paste data into opened workbook

Question

Is it possible to paste data into an already opened worksheet using office automation from vb.net? (I done it using oledb but is very slow.)

        Dim Folder As String = "C:\Users\gm1\Desktop\"
        Dim Filename As String = "Raw2Eng.xlsx"
        Dim ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Folder & "\" & Filename & ";Extended Properties=""Excel 12.0;HDR=YES;"""
        Dim MyConnection As New OleDbConnection(ConnectionString)

        MyConnection.Open()
        Dim cmd1 As New OleDbCommand
        cmd1.Connection = MyConnection
        cmd1.CommandText = "DROP TABLE [Sheet2$A1:E1027]"
        cmd1.ExecuteNonQuery()

        cmd1.CommandText = "CREATE TABLE [Sheet2$A1:E1027] (HiResAngle FLOAT, CH2 FLOAT, CH4 FLOAT, CH5 FLOAT, CH6 FLOAT)"
        cmd1.ExecuteNonQuery()

        For j As Integer = 0 To TrimmedSegmentSize - 1
            sb = New StringBuilder
            sb.Append("INSERT INTO [Sheet2$A" & 1 & ":E" & j + 1 & "] (HiResAngle, CH2, CH4, CH5, CH6) values ( '")
            sb.Append(eng(i, j, 0)) : sb.Append("' , '")
            sb.Append(eng(i, j, 1)) : sb.Append("' , '")
            sb.Append(eng(i, j, 2)) : sb.Append("' , '")
            sb.Append(eng(i, j, 3)) : sb.Append("' , '")
            sb.Append(eng(i, j, 4)) : sb.Append("' )")
            cmd1.CommandText = sb.ToString
            cmd1.ExecuteNonQuery()
        Next
        MyConnection.Close()

It seems you have an surrouding loop (with the i variable). What does it do ? — Dude Pascalou, Mar 25 '13 at 09:02
Apart form the declaration (1st 4 rows) the code will repeat i times. So output will vary as I iterate through the 1st dimension of the 3D array. Thanks. — Gergo, Mar 25 '13 at 09:18

Dude Pascalou · Accepted Answer · 2013-03-25T09:49:45.593

0

OleDb is way faster than automation. Show us your code, may be there is another problem.

[EDIT] If you really want to use automation (because you want to keep the workbook opened), you may read this article : How to transfer data to an Excel workbook by using Visual Basic .NET

edited Mar 25 '13 at 09:49

answered Mar 25 '13 at 08:38

Dude Pascalou

2,989
4
29
34

Hi Dude Pascalou. Please see my oledb code. I need to overwrite old data and as delete isn't allowed I drop-create table. The code works but to insert 1024 rows takes about 30 seconds if the workbook is open. Any suggestions how to improve the code? – Gergo Mar 25 '13 at 09:02
I tried on closed workbook, the insertion reduces to 2 seconds. But I need 'live update' on opened workbook. – Gergo Mar 25 '13 at 09:39
Is there anything wrong with my code? If not, is there a more efficient way I could automate the data transfer from vb .net to excel while the target workbook is open? – Gergo Mar 25 '13 at 09:45
There is nothing wrong with your code... I've edited my message to give you an help link. – Dude Pascalou Mar 25 '13 at 09:47
I've already read this guide but the code presented here will create or open existing workbook then save changes. I guess my question is: can excel automation connect to already opened workbook? I appreciate your time, thank you for your help! – Gergo Mar 25 '13 at 09:58
...and the anwser is yes with late binding. Instead of using `CreateObject("Excel.Application")` you may use `GetObject(, "Excel.Application")`. See : http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm – Dude Pascalou Mar 25 '13 at 10:13
You are right, it works! I will add a comment later about performance in case others are interested. Many thanks for your help! (sorry, I'm not allowed to vote as I have lass than 15 reps) – Gergo Mar 25 '13 at 10:31
Transfer time is 62 ms for 1024 x 5 table. – Gergo Mar 25 '13 at 10:47

vb .net using excel automation to paste data into opened workbook

1 Answers1