Opening excel AFTER(!!!) export to it...

[Wirloff]

Hi,
in my application i'm transferring data from MS Access into pre-made Excel templates using datasets. I'm using the following code to start the excel application

[size=2]objExcel = [/size][size=2][color=#0000ff]New[/color][/size][size=2] Excel.Application[/size]
[size=2][size=2]objExcel.Workbooks.Open(path)

objWorkSheet1 = objExcel.Worksheets(sheet)

objWorkSheet1.Select()

[/size][/size]

than I can start filling up the templates with
objExcel.cells(theRowIndex, theColIndex) = theValue

But this works very slowly. He has to jump from one to another active cell in the open excelfile. And if somebody clicks in the excel file while filling it up, it crashes.
My question is: is it possible to fill up the excel file in the background without allready having to open the excel file physically. And showing all of it to the user when done with importing. I hope that this would work faster...
Thx.

 

[Administrator]

You could probably do an export to a CSV format then execute it or open it directly using the System.Diagnostics.Process.Start method.

 

[jmcilhinney]

You can access an Excel workbook using ADO.NET, which is much quicker than opening an instance of Excel. To do this, your connection string will look something like:

"Provider=""Microsoft.Jet.OLEDB.4.0"";Data Source=""<file name>"";Extended Properties=""Excel 8.0"""

The "Extended Properties" part can contain other information, like whether there is a header row (HDR=YES or HDR=NO). A select query would look something like this:

"SELECT * FROM [<sheet name>$<first column label><first row number>:<last column label><last row number>]"

I've never used ADO.NET to update a workbook, but the principles are the same. A search through Excel help in the application or the MSDN library on the Microsoft web site should yield the information you need. That's where I found the above info when I needed it.

 

[cliffd64]

Hi,
in my application i'm transferring data from MS Access into pre-made Excel templates using datasets. I'm using the following code to start the excel application

[size=2]objExcel = [/size][size=2][color=#0000ff]New[/color][/size][size=2] Excel.Application[/size]
[size=2][size=2]objExcel.Workbooks.Open(path)
 
objWorkSheet1 = objExcel.Worksheets(sheet)
 
objWorkSheet1.Select()
 
[/size][/size]

than I can start filling up the templates with
objExcel.cells(theRowIndex, theColIndex) = theValue

But this works very slowly. He has to jump from one to another active cell in the open excelfile. And if somebody clicks in the excel file while filling it up, it crashes.
My question is: is it possible to fill up the excel file in the background without allready having to open the excel file physically. And showing all of it to the user when done with importing. I hope that this would work faster...
Thx.

If you make the excel application visible property = False, until you have finished loading the data, there is no way the user can see it.

As soon as you finish loading all your cells, make the excel app visible like this:

		' view the spread sheet
		objExcel.Visible = True