[2005] MS Excel Automation

[eSPiYa]

Is there any walkthrough about MS Excel Automation in MS Visual Studio 2005?
I already use Automation in Excel using MSVS2003 but it takes aroung 10min to fill a worksheet w/ more than 1,000 rows from a dataset.
It is very slow because I insert text to cells 1-by-1, is there a way to do it faster?

I've heard there is a new way in automating in VS2005.
I'm using MS Visual Studio 2005 Team Systems and MS Office 2003 so I think I have all the tools I need.

It is ok if it is in VB or C#, or using MS Office Tools.

Thanks,

[RobDog888]

That sounds painfully slow.

I havent heard of anything new in 05 but then I have just installed it a couple of days ago.

Have you tried Application.ScreenUpdating = False and then back to True when your done populating your sheet?


Edit: Application I mean the Excel Application object

[eSPiYa]

I hide the application (MS Excel instance) while populating it then show it after the whole process.
Is it possible to insert a set of records in Excel faster (just like Highlight, Copy then Paste method) rather than inserting data/text cell-by-cell?
Or may be databinding, we need a faster process or else our database server will suffer.

[RobDog888]

You can perform a sql insert into statement on the sheet. Use ADO.NET to connect to the sheet with a connectionstring found at http://connectionstrings.com.
This should be the fastest.

[eSPiYa]

I don't have any problem in connection/connectionstring, my problem is a faster method of inserting data to the worksheet from my dataset/recordset.
Instead of inserting data cell-by-cell.

This is my previous method: (Peudocode)

For(Row=0;Row<=RS.Recordcount;Row++) //(Recordset/Dataset)
{
For(Col=0;Col<=RS.Columns.Count;Col++)
{
Worksheet.Row(Row).Cell(Col).Value = RS.Row(Row).Cell(Col)
}
}

If you see this is very slow.

What I want is like this: //(Pseudocode again)

Worksheet.InsertData(RS,"A1") //Where InsertData([Datasource],[Location])

Iknow this code does not exist but I hope you got want I am saying

[eSPiYa]

I tried this one:

Dim SQLConnection As New Data.SqlClient.SqlConnection([ConnectionString])
        Dim myDA As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("select top 10 * from repArticleAllCoWatch", SQLConnection)
        Dim myDS As New Data.DataSet
 
        myDA.Fill(myDS)
 
        Dim Rng As Excel.Range = Globals.Sheet1.Range("a1", "e1")
        Rng.Value = myDS.Tables(0).Rows(0).ItemArray

But I insert only 1 record.
What I want is to insert a set or records.
Please help me!

[RobDog888]

As I was posting, executing a Insert Into sql statement on an Excel ADO.NET connection would be the fastest. Its not just a connectionstring thing as you are actually connecting to the workbook's sheet and able to treat it like a database table.

[eSPiYa]

Ok I got your point, but is it possible to insert multiple recordset/dataset/tables in a worksheet by that method?

[eSPiYa]

Please show me a sample code of it and the references I need to add.
I'm new in office automation, I want to understand everything.

Thanks for help.

[jmcilhinney]

Ok I got your point, but is it possible to insert multiple recordset/dataset/tables in a worksheet by that method?

When using ADO.NET each sheet in an Excel file is treated as though it's a table in a database. I've only ever used that technique to read data, so I'm not sure that you could use it to add a worksheet, which is basically creating a table. Maybe it is possible though. The only way to know for sure is to try. www.connectionstrings.com has the details of how to modify a query for Excel. Perhaps you can apply the same modification to other SQL statements, like CREATE TABLE.

[RobDog888]

Yes, I have posted that info already but just in case when you create a new workbook it will contain 3 sheets by default. You would then reference a sql statement pointing to the desired sheet. Insert Into and Selects are what seem to work best. Reference the sheet name with a "$".

INSERT INTO [Sheet1$] VALUES ('Test', Test2', Test3')

Etc.

Joining the two sources cant be done unless you do it from the other database or a dataset or such.

[eSPiYa]

What I need to do is retrieve data from the SQL Server, then draw several tables(MS Excel table like designs not data tables) inside a worksheet.
So it is impossible to write data inside my Excel file using this method.
(please see attachment)

As you can see, there are several tables inside the sample.
The number of rows inside a table is not fix, sometimes it contains 1 row but usually it may contain hundreds to thousands of rows.

If the number of rows is that large, the extraction of reports(creating xls files) will be very slow(please read previous posts).
The application should generate 16+ .xls files contains 2-6 worksheets contains 10+ tables and each tables contains 100-1,000 rows and other worksheets will contain 5+ charts.
If you analyze, the extraction of reports will be very slow if I am going to insert data cell-by-cell from my query.

[RobDog888]

Well when you are dealing with multiple workbooks and sheets like you are there will be some performance loss. One drawback withthe sql method is that you can not position the row where your insert into statement will be going. What you are trying to do is create a visual representation of database data in Excel which is not its primary design.

You can do this allot easier with VSTO if you have it as you can define the ranges and have them databound to your sql server. All reads/writes will be taken care of automatically due to the databinding.

[eSPiYa]

Macros sometimes were blocked some PCs, I think I should create only plain workbooks(w/o macro).

I tried to record a macro while importing external data (Data>Import External Data>Import Data) then I read the code then I found this ActiveSheet.QueryTables.Add([Some parameters]).
Then I tried it w/ VS2005(C# and added reference to some MSExcel automation) it is visible there too: [worksheet object].QueryTables.Add([object connection],[Destination],[object Sql])

Here is the sample code from the macro:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=D:\cash_05.xls;DefaultDir=D:\;Driver={Microsoft Excel Driver (*.xls)};DriverId=790;FIL=excel 8.0;MaxBufferSize=2048;MaxScan" _
        ), Array( _
        "Rows=8;PageTimeout=5;ReadOnly=1;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
        )), Destination:=Range("A34"))
        .CommandText = Array( _
        "SELECT `Sheet1$`.AWB, `Sheet1$`.HEADER1, `Sheet1$`.DATE, `Sheet1$`.HEADER2, `Sheet1$`.HEADER3  FROM `D:\cash_05`.`Sheet1$` `Sheet1$`  GROUP BY `Sheet1$`.AWB, `Sheet1$`.HEADER1, `Sheet1$`.DATE, `Sheet1$`.HEADER2, `Sheet1$`.HEADER3 " _
        , " HAVING (Count(`Sheet1$`.AWB)=1)  ORDER BY `Sheet1$`.AWB")
        .Name = "Query from Cash05"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\vdolosa\Application Data\Microsoft\Queries\Query from Cash05.dqy"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=21

As of my observation, it will insert all the records w/o looping(looping through records and displaying output will decrease performance).

[RobDog888]

Correct but it is the same as the .CopyFromRecordset method of the EOM.
As you may have noticed, the select statement

Only other issues are that you will need to limit the data somehow in order to keep it populating the correct row range and not go outside of your drawn/formatted "tables".

Also, you shouldnt use the macro recording straight out of the box. It uses the Activexxxxx object(s) which are unreliable and have the same issues as the "SendKeys" method does in vb 6 and vb.net.

So either this method or manually coding a similar one like the original plan, you are going to run into limitations and will need to use the EOM eventually for a few tasks.

[eSPiYa]

Thanks for the advise, I'm desperate of this thing that is why I tried this.

.CopyFromRecordset method of the EOM what is it?

[RobDog888]

It works with the classic ADO. You basically create a ado recordset and then call the excel object model function and it populates the range with the recordset data all at once. Similar to the data import.

I have a code example on the FAQ topic - http://vbforums.com/showthread.php?t=402060

[eSPiYa]

Wow I think this would help me a lot.

Out of Topic:
Rob, I think one of your FAQs for VS2005 & VS2003 are inaccessible.
Please repair the links for the benefits of the others.